Should I script this or can it be calculated

[Rich]

G'morning, all:

Please refer to the attachment. What I'd like to do is have the 1st Attempt (text) field auto-calculate whether a record's (Test) Code has been recorded with an earlier date; "No" if it has (e.g., there's an existing record for Code 5004 taken on Test Date 01-20-2017 so the record with it being taken on 01-08-2018 will have its 1st Attempt field set to "No"); "Yes" if it's the first time it's taken (e.g., Code 5002...and subsequent dates with Code 5002 would be set as "No".)

I know I could script this by sorting by Test Date then Code, then comparing the existing record with a previous one...which would then set the 1st Attempt to the appropriate Yes or No, but I'm curious whether I could set this up as a calculation within the child table...just that I don't know how to go about doing that.

Thanks in advance for your help!

[siroos12]

TeacherEd,

 

There are many different approaches to address this. One would be using a self relationship between the target table of your portal and itself. Lets call it "Tests". You would need to copy its TO and call it "Tests_Self||Code". The relationship between the two TOs would be based on Code fields. So it would look like below:

 

Tests::Code = Tests_Self||Code::Code

 

Then you can count number of codes through this relationship which would look like:

Count(Tests_Self||Code::Code)

 

If this calculation result value is greater than one, it means that there is at least a record with the same Code in this table. This means that it is not a first attempt and vise versa.

 

Now you can use this as Auto enter Calculation for the field "1st Attempt" as below:

 

if(Count(Tests_Self||Code::Code)>0;"No";"Yes")

 

Also it is a good practice to use numbers instead of text for Boolean fields. So set the calculation like this:

if(Count(Tests_Self||Code::Code)>0;"0";"1")

Note: the "Code" filed must be set to a number field instead of text field.

Then use inspector>>Data tab>>Data Formatting to set the field formatting as Boolean. From there you can set the "Yes" and "No" text to show up instead of 0 and 1.

 

Hope abouve does make sense.

 

Regards,

 

 

 

 

Edited April 9, 2018 by siroos12

[Rich]

Thanks, Siroos--I did what you suggested but ran into a snag--my fault for not adding this earlier. Because these calcs are done in the child table, the count for the number of times a code is used includes all the records with that Code instead of a specific student so I'm getting a count of 285 instead of say, 2, for Code 5005. To try to filter the count, I added the StudentID, in addition to the Code in the new TO (with an = sign, like Code), but that results in no records being seen, so that wasn't it.

 

I'll keep trying with different permutations unless you, or someone else, chimes in with a suggestion; if I figure it out, first, I'll post the info here.

Edited April 9, 2018 by TeacherEd

[siroos12]

TeacherEd,

 

Are the StudentID fields the same type in both child and parent tables? Can you post a sample file with some fake data here so we Can have a deeper look into the file structure?

 

I would suggest to copy the same relationship you have right now between the parent and child table, this way you will get the same records showing into the new relationship as they are shown on your current portal. Then add the Code key into this new relationship and see how it goes.

Edited April 9, 2018 by siroos12

[Rich]

They are. It's a messy file so I'll create a clone and will tidy things up a little make it easier for you before I upload them. Thanks for the fast reply!

[Rich]

It was easier to make a quick-'n'-dirty one so I've attached it.

First Attempt.fmp12

[siroos12]

Have a look at the relationship and do some testing. does this work the way you want?

 

Changes I made: 

 

I added the "_f_ParentID" into the self relationship and also I setup the "FirstAttempt" to a number with with auto calculation. The formula is "If ( Count ( Tests_Self||Code::Code ) > 0 ; 0 ; 1 )".

Note that the checkbox of "Do not evaluate if all referenced fields are empty" is unchecked.

 

Here is the file:

First Attempt_V1.fmp12

[Rich]

Looks great but we're not quite there, yet.

I don't know how the dates I entered disappeared but they did somehow. Anyway, if a student takes the same test (code) more than once, the first time (date) the student takes it should have a First Attempt of 1/Yes; thereafter, 0/No. So, say for Record 1 (_p_ParentID) a student took test 5001 on 05-15-1999 and again on 01-20-2000, in First Attempt the 05-15-1999 date should be 1/Yes and the 01-20-2000 should be 0/No.

Edited April 9, 2018 by TeacherEd

[siroos12]

Okay, the issue is not committing the record after adding one portal record.

The reason would be hidden in the "Auto Enter Calculation" behavior. It needs a commit to evaluate the calculations which are through relationships. A work around would be using ExequteSQL calculations to count the number of existing codes instead of using FM count function through relationship.  This does not a commit to evaluate.

 

So the auto enter calculation would be like this:

 

Let ( [
    
    ~count = ExecuteSQL (

    " Select COUNT (Code)
    FROM Tests
    WHERE Code =? AND \"_f_ParentID\"=?"
    ;"" 
    ;¶
    ;Code
    ;Parent::_p_ParentID
    )
];
If ( ~count > 1 ; 0 ; 1 )
)

 

Note that the new calculation is looking for records count of more than 1 instead of 0 because the current portal record itself is also counted as one. With this method, the "Code" field should be very first field that a user inputs data. You can restrict users access to other fields when this filed is empty.

 

Have a look at attached:

 

First Attempt_V2.fmp12

 

 

Edited April 9, 2018 by siroos12

[Rich]

Ah! Brilliant! Thank you!!

I'd like to send you a coffee card as a thanks for all your help--can I send it via your website link in your signature line?  (Hopefully, I can snag a Timmys for you.)

[siroos12]

2 hours ago, TeacherEd said:

Ah! Brilliant! Thank you!!

I'd like to send you a coffee card as a thanks for all your help--can I send it via your website link in your signature line?  (Hopefully, I can snag a Timmys for you.)

Thanks TeacherEd, That is so kind of you. Really no need to do that, we are all here to help the community. 

 

I received tons of help from this community and I would help others too as much as I can.

 

Good luck.