Compare multiple fields via relationship possible?

[SandieK]

Current database config: Adding test result scores in database.

Because of quesitonable data input errors, boss wants to make sure scores are correct - he wants to input scores again in another layout and then have FM7 compare the fields in the two layouts. I have 125 fields plus 125 more compare fields (i.e. testscore and testscoreC). Right now, it's working ok but I think there is a better way since I need to create compare fields for 12 more tests.

Current solution:

Layout #1 has the regular fields (testscore)- tester inputs scores in this layout. Layout #2 has compare fields (testscoreC)- boss inputs scores from same test in this layout. Button on layout #2 is associated with script that compares field in layout #1 with it's equal field in layout #2. If they do not equal, the score in layout #2 turns red to make it stand out and we then know an error was made inputing data in that field.

The script for this compare was tedious and very long - 5 lines of script for each field (125 fields) plus a 2nd script comparing fields in layout#2 with fields in layout#1. Is there an easier way to do a multiple compare fields via a relationship instead of creating a very long script to do the comparing and if so, how would I do it?

Thanks

Sandie

[Walter B]

Depends. I need a little more information. Are testscore and testscoreC in the the same table? If not, do you have the different tables related?

Either way, I think I'd start by using a calculation field that looks to see if there is a value in each appropriate field. No value would indication an incomplete process and abort the calculation. Then a simple if statement to compare. Like: if testscore = testscoreC, (trueText), (falseText)

[comment]

I would suggest you break out the scores into a separate table, where each score is a record. Everything becomes easier that way, including your current problem (a single calc field flagging testscore <> testscoreC).

[SandieK]

Walter B: Are testscore and testscoreC in the the same table? If not, do you have the different tables related?

Testscore and testscoreC are separate tables. There is an = relationship between each field in testscore and testscoreC.

To comment: Each score is a record. I'm not sure what you had in mind: (a single calc field flagging testscore <> testscoreC). Which table would this single calc field be in? Did you mean that each field (they are number fields) have a calc value added that would flag testscore <> testscoreC?

[comment]

Earlier you said "I have 125 fields plus 125 more compare fields". My suggestion was to have 125 records in a related table.

Even repeating fields, with all their faults, would be better than having 2 x 125 fields in a single record, with another 125 trying to pair them up for comparison.

But now you say that each score is a record, so I am confused.

[SandieK]

I understand what you are saying, comment. I'll try to make this clearer.

I need to keep all 125 fields together as one record as they indicate one complete test taken on a given test date and belonging to one patient. The same patient could be given the same test on a different date.

The compare table has the same 125 fields designated with a "C" at the end of each field to indicate a compare field...i.e.; table#1::testscore = table#2::testscoreC. Right now, the relationship is created between table#1::TestID = table#2::TestIDC.

My question was, is it possible to have the relationship compare all 125 fields without having to create another huge script (5 lines x 125)?

Thanks for answering.

Sandie

[Søren Dyhr]

I need to keep all 125 fields together as one record as they indicate one complete test taken on a given test date and belonging to one patient. The same patient could be given the same test on a different date.

To the best of my knowledge, doesn't invoices that are the bred and butter for many developers mix itemlines from one date with the next invoice's itemline??

You might have even two portals on one layout one to fill and one from the previous record of the same person...

--sd

[comment]

You need a table for Tests (as you have now). This will hold the date of the test and the ID of the Patient taking the test.

Then there is another table, let's call it TestItems, that has the following fields:

TestItemID - auto-generated serial

TestID (holds the parent TestID)

Label/Question/whatever you use to identify a test component

Score

ScoreVerify

cError = Score <> ScoreVerify

The relationship from Tests to TestItems is:

Tests::TestID = TestItems::TestID

So all 125 test items are still "kept together" by being related to the same parent record in Tests, and at the same time a single calculation field flags any discrepancies in data entry.

[comment]

Alternatively, you could use the method shown in the attached file. This is simpler to implement, but if you expect to perform any serious statistics at the end, you will eventually have to export the repeating fields into separate records anyway.

In any case, the way you have it now is IMHO a dead end.

TestVerify125.fp7.zip

[SandieK]

Comment:

Your attachment was exactly what I was looking for. I didn't want to do repeating fields but looking at your model would definitely solve the need for lengthy scripts.

Thanks for your help.

Sandie

[comment]

Good. BTW, the Labels field should have been a global - so that you only have to fill it once.