I think I have it figured out now.
Very hard to answer that as you haven't described how your tables, relationships and scripts are designed.
One trick that I have used for creating a new record only if a record does not already exist is to use set field from a layout based on a related table where the relationship matches just to the single record and for which "allow creation..." has been enabled. In that context,
Set Field [RelatedTO::ForeignKey ; Layout'sTO::PrimaryKey ]
Will create a new record if such a related record does not exist, but sets the Foreignkey field's value of any existing record to the value that it already has and thus no new record nor any change to the data in it takes place.
That's just what I need. It will allow me to use a much simpler script too.
That script worked for me. Thanks. I'm having trouble now with another issue. When I start a new class session the same attendance ID goes onto the same students as in the previous record. So Tuesday's students have Monday's attendance statuses. To fix this I think I have to have a relationship based on two criteria: sessionID and studentID. I'm struggling to find a way to match studentIDs and sessionIDs with other session IDs and student IDs as the don't occur in the same table.
I think a join table is needed. But I'm not sure where to put it. Here is a diagram of where I put it. When I run the script on the debugger it isn't saying there is a problem. But it is not making new records in att_studentID session for me.
I've decided that I'm going to follow advice from Phil from a different thread. I'm going to populate each class session using the following script.
It is nearly working. But its giving me a couple of problems.
When I set the student id field it includes the paragraph symbol or line break too. I could use the substitute function to get rid of that. But maybe I'm doing something wrong thats causing that.
The last loop of the script won't stop looping either.
Just after your first loop script step, use this step to avoid problems with having a trailing ¶:
Set Variable [$ID List ; value: List ( $ID List ; Personnel Records::pk_StudentID ) ]
Your exit loop has a problem that's keeping the loop from ever terminating. All the "values" functions, such as middlevalues append a ¶ after the list of values returned by the function and you have two variables, $i and $L. $i is incremented, $L never changes in the loop.
Instead of using middleValues to extract a single value from a list of values, use GetValue ( $ID List ; $L ) and you will not have that problem and simpler expressions as well. (I've used upper case for $L to make the difference in variable names more obvious.)
You can also use: Exit Loop IF [ $i > ValueCount ( $ID list ) ]
Substitute ( MiddleValues ( $ID list; $i ; 1 ) ; ¶ ; "" )
Exit scipt If (Substitute ( MiddleValues ( $ID list; $i ; 1 ) ; ¶ ; "" ) = "").