12 Replies Latest reply on Nov 10, 2012 9:16 PM by paulatkins

Relationship problem?

Hi, I am building a scoring system for a print judging process.

I have several tables, one is the Entrants table where each entrant is described, given a "PortfolioNumber" and their up to 8 entries' scores are gathered in separate fields.

One of the other tables is the Entries table, where each entry has an ID number, a score that is averaged from 5 judges scores and a "PortfolioNumber" field (plus other details). The "PortfolioNumber" is what I have related between the Entrants and the Entries tables.

What I want to do is when a score is entered into the Entry score field (AverageScore), averaged from the 5 judges, that score is fed into the Entrants print score fields entitled "Print1Score" "Print2Score" etc.

Why do these calcualtions not work?

PrintScore1 = If ( (( Entries::PortfolioNumber = PortfolioNumber ) & ( Entries::PrintNumber = 1 )) ; Entries::AverageScore )

PrintScore2 = If ( (( Entries::PortfolioNumber = PortfolioNumber ) & ( Entries::PrintNumber = 2 )) ; Entries::AverageScore )

...etc

It returns the same score for every print (Print1), ignoring other print numbers.

Thank you

• 1. Re: Relationship problem?

Hello, Paul.

It's a little hard to tell from your description. Is the relationship between Entrants and Entries a one-to-many relationship? If so, then your issue is coming from the fact that a calculation will address the first related record. So, for example, this would be what happens:

Entrant               Entries

1                         PrintNumber: 1

PrintNumber: 2

Whenever the Entrant record looks into the Entries table via a related field, it's always going to see the PrintNumber = 1 record.

You can fix this a couple of different ways:

1) If you really want to keep an array of PrintScore records, you'd need a number of relationships equal to the number of PrintScore records, each one with a calculated key field to isolate down just the one Entry record you want.

2) The other way to do it (some would say THE way to do it) would be to store your PrintScore record either in a separate table or in the Entries table (hard to tell with the limited information). It's a data modeling issue, but it looks like your PrintScore data element belongs in a table other than the one where it's currently living. (Whenever I see something like Field1, Field2, Field3 and they're essentially duplicates of each other, it tends to indicate a problem with the data model.)

HTH

Mike

Edit: You can verify that the system is seeing the first record by putting a portal on your Entrant layout pointing to Entries. The first row will tell you what record a calculation field will see.

1 of 1 people found this helpful
• 2. Re: Relationship problem?

paulatkins wrote:

What I want to do is when a score is entered into the Entry score field (AverageScore), averaged from the 5 judges, that score is fed into the Entrants print score fields entitled "Print1Score" "Print2Score" etc.

I don't see why you need those "print score fields", when the data is already in the Entries table, and can be displayed directly through a portal (or portals).

paulatkins wrote:

Why do these calcualtions not work?

PrintScore1 = If ( (( Entries::PortfolioNumber = PortfolioNumber ) & ( Entries::PrintNumber = 1 )) ; Entries::AverageScore )

The main reason is that it you are referencing a related field - and such reference always looks at data in the first related record (in the order of the relationship).

You are also using & instead of and, but that's not critical here because both your tests will always return true: the PortfolioNumber of the related record is obviously equal to the local PortfolioNumber (otherwise it would not be a related record), and the PrintNumber of the first related record is always 1, I guess.

You should be using the GetNthRecord() function to get the value from the second related record and up - but as I mentioned earlier, it's not really necessary.

1 of 1 people found this helpful
• 3. Re: Relationship problem?

Thank you Michael and Mike,

Michael, I need to average the 8 print scores and add it to an overall score each portfolio is given, I figued I nee to move those judges averages made in the entries table over to the entrants table to do that.

Michael, how will I know what "Nth" record it is if each entrant can have 1 to 8 entries?

Does this go to Mike's point of me needing to relate the up to 8 Entries table records to the PrintScore1 to PintScore8 etc fields in the Entrants table?

Sorry I've lacked detail, here is some more information that will hopefull explain my logic (or lack of):

• 4. Re: Relationship problem?

Whoops the graphics missed posting:

• 5. Re: Relationship problem?

I think you are making this more complicated than it needs to be, because your basic structure is incorrect. You should have a table for every "many", instead of numbered fields - something like:

• 6. Re: Relationship problem?

Hi Michael, my FM training has been disjointed, I see the logic in the tables for 'many' but I am having trouble construcing a layout that will allow me to apply to an entry 5 scores from different jusdges, plus show the average of those 5 and then do a second overall score for the portfolio and average those.

I will mess with layouts it is the only way I'll make the leap and improve my FMskills.

Thank you,

paul

• 7. Re: Relationship problem?

paulatkins wrote:

I am having trouble construcing a layout that will allow me to apply to an entry 5 scores from different jusdges

Use a layout of Entries, with a portal to Scores.

paulatkins wrote:

then do a second overall score for the portfolio and average those.

Do you want to average the averages or the original individual scores?

• 8. Re: Relationship problem?

The portal to scores, in the Entries Layout, do I make 5 score fields, one for each judge and link it to 5 separate score records?

I need to average:

1. the 5 scores form the judges to make an overall print score,
2. then a portfolio score, where the pieces are judged as a whole against their artist statement by the 5 judges and this too is averaged to get an overall portfolio score.
3. Then finally an average of the two averages, the overall print score and overall portfolio score giving them a whole score.

Easy done in a spread sheet, but I'd like to make a tool in FMP that is clean and idiot proof.

• 9. Re: Relationship problem?

paulatkins wrote:

The portal to scores, in the Entries Layout, do I make 5 score fields, one for each judge and link it to 5 separate score records?

If there are 5 scores, then there will be 5 related records in the Scores table - thus 5 rows in the portal.

I didn't understand point 2 above.

• 10. Re: Relationship problem?

Point2, the portfoio score would be an extra score given by each judge for the overall concept of the entrant's work that needs to be averaged amongst the 5 judges. Essentially it is just another "print" or "entry".

So related to that 'Scores' portal, I'd make a "New Score" button, which fires a new record in the score field, then ask the judge to enter their judgeID and the score into the two fields on display, as the number of judgements grow a new line is displayed?

Thank you for your tenacity with this Michael, I am hving some ahha moments.

Paul

• 11. Re: Relationship problem?

IIUC, you need two tables for scores (although you could use another occurrence of the existing scores table for the second purpose as well). That way you would  add to the already existing:

Entrants -< Entries -< PrintScores >- Judges

this (in blue):

Judges 2 -< PortfolioScores >- Entrants -< Entries -< PrintScores >- Judges

paulatkins wrote:

So related to that 'Scores' portal, I'd make a "New Score" button, which fires a new record in the score field, then ask the judge to enter their judgeID and the score into the two fields on display, as the number of judgements grow a new line is displayed?

You could do that, or you could enter a new score directly into the last portal row (assuming the relationship is set to allow creation of records on the scores side). That's a question of user interface, not relationships.

• 12. Re: Relationship problem?

Excellent thank you, I will work on that.