relation to multiple fields

Classic Servoy
1

I’m creating a administrationsystem for a lanparty. We’re gonna compete in clans, and i made a for where you can see the clanname and password. Beneath it i want to show the matches (in sense of fights) the clan played.
In the table match there are the fields clan1_id and clan2_id. The id’s are related to the clanID in another table, called clan.

Now, the problem is:
If your match schema is like this:

Clan1 vs Clan2
Clan3 vs Clan4
Clan2 vs Clan4
and so on…

then you’ll notice you clan id my be stored in both clan1_id and clan2_id. When i make 1 relation with as entries:
id = clan1_id
id = clan2_id
and i create a new tabpanel based on this relation, no matches are found, propably because servoy thinks it should match both expressions.

How can i get this working the way i mean it?

2

no we don’t have support for OR relations
You have to do this with a find youreself.

3

thanks Johan, i figured that out myself. Now I want to do another thing in which I fail. I hope you could help me.

In order to show ranks, I want to calculate the score and point scored against a clan. Again the score can be in either clan1_score or clan2_score. How can I calculate this?

I give an example, to make sure you understand what i mean:
If the table looked like as in the attachment, the score would be like this (F = for, A = against):
Clan 1: F:25 A:17
Clan 2: F:16 A:26
Clan 3: F:10 A:4
Clan 4: F:5 A:9

table_example.PNG
table_example.PNG519×124 2.23 KB

4

Wouldn’t this work:

In your table “clans”, create a field “score_for” and “score_against”. Then create 2 relations from “clans” to “match” (one pointing from clanid to clan1id, the other from clanid to clan2id). Next, create two aggregations in matches that sum up “clan1_score” and “clan2_score”. In clans you create two calculations “score_for” and “score_against” (the results will be saved in the database). The calcs could look like

return clans_to_matches_1.sum_2 + clans_to_matches_2.sum_1

and

return clans_to_matches_1.sum_1 + clans_to_matches_2.sum_2

Does this help?

5

Thanks patrick, this does exactly the way I want it to. Plus i do understand what it does.

I don’t really have to make those fields in the database, do I? It’s just in case I’d like to view the values trough another application or trough php or so.
Am I right?

6

yes, you are right. no need to store the scores.