1) Get rid of the "Select" option in your Go to Portal Row script steps.
2) You need to go to a related field inside the portal before you go to a portal row. Otherwise, FileMaker doesn't know what portal you're telling it to go to.
In the future, please identify the tables for the relevant tables in your questions. That way, we don't have to figure out what "Classes", "Trainees", and "Attendance" are before we can puzzle through your script.
Much of the detail is hidden, so hard to tell, e.g. Finds, Relations, and calc ListTrainees
Save $attendedlist to a Global Field, so you can check it
If you don't have Debugger ( FileMaker Advanced ), put a pause in the Loop, so you can watch it work
> The second run gives correct results
I tried debugging.... event that behaves differently .. First Debug is not working as desired and the second works perfectly
In the first Debug, you should be able to see where it "breaks"
If not, display ( or Watch ) all your variables, or put a Pause after each step ( including the Finds )
And as suggested, make sure you "Go to Field" first, in the Portal
> I tried debugging.... event that behaves differently .. First Debug is not working as desired and the second works perfectly
I think there is an issue with Set Field .... it is not setting the right variable in the field ( on the first run )
It is setting the trainee's first Name instead of trainee ID .
On second run
Set Field is settting the Trainee ID correctly
Interestingly for each loop displaying the Variable using 'Show Custom Dialog Box' .... corrrect values are shown on each run of the script
Can't spot the error .... I am attaching the file ! !
I even tried the go to related record .... it was also giving some error
Thanks everyone .... i fixed it i think ... I had some errors in my lookup fields .... Trainee ID was wrongly set as lookup field..
Now the script is working
I am attaching the corrected file also ...
Still dont know why it always worked on second run !
I found the break that lead me to the solution ....
Well, you have another problem:
fk_trainee in Attendance is text, but the pk in Trainee is type number …
If you consolidate that, there is much easier way to script this:
Set Variable [ $traineesToDo; Value:Let (
attendeeList = Substitute ( List ( Attendance::fk_Trainee_ID ) ; ¶ ; "','" ) ;
ExecuteSQL ( "
WHERE fk_Cohort = ?" &
Case ( not IsEmpty ( attendeeList ) ; " AND pk_Trainee_ID NOT IN ('" & attendeeList & "')" ) ; "" ; "" ; Classes::fk_Cohort
If [ IsEmpty ( $traineesToDo ) ]
Exit Script [ ]
Set Variable [ $toDoCount; Value:ValueCount ( $traineesToDo ) ]
Go to Object [ Object Name: "portal" ]
Exit Loop If [Let($i=$i+1;$i >$toDoCount)]
Go to Portal Row [ Last ]
Set Field [ Attendance::fk_Trainee_ID; GetValue ( $traineesToDo ; $i) ]
This will also prevent you from creating duplicate attendance records.
OK thanks .. I fixed the data type .. Will your script work with my data model or do I need to change it ? I mean is there a design flaw in the ERD shown below ?
Will your script work with my data model or do I need to change it ?
I created this script in your sample file; just be aware of the usual gotchas you might trip over when using ExecuteSQL().
As for design flaws; the cohortID is a direct attribute of a trainee; what are you going to do if the same person attends another class that belongs to a different cohort (or their cohort affiliation changes for other reasons)?
You could also simply create a relationship between
Class::fk_cohort and Trainee__byCohort::fk_cohort
then List ( Trainee__byCohort::pk_ID ) is your list of potential new attendees; if this calc
Let ( [
already = List ( Attendees::fk_trainee_ID ) ;
potentially = List ( Trainee__byCohort::pk_ID )
both = FilterValues ( already ; potentially )
ValueCount ( both ) = ValueCount ( already )
returns True, all candidates are already in. (You could also use that to Hide the button.)
If it is False, there is at least one missing from Attendance, and you can use
# [ obtain one list of alreadys and one of potentiallys ]
Set Variable [ $count ; ValueCount ( $potentiallys ) ]
Exit Loop if [ Let ( $i = $i + 1 ; $i > $count ) ]
Set Variable [ $candidate ; GetValue ( potentiallys ; $i ) ]
If [ IsEmpty ( FilterValues ( $candidate ; $alreadys ) ) ]
Go to Portal Row [ last ]
Set Field [ … ]
to prevent duplicates.
Cohorts are in different parts of country so one trainee cannot attend classes in another place. Also cohort leaders are assigned for each trainee who take classes and recieve assignments . Thanks erolst .. You are very helpful as usual