create record in different table

[plunkettphoto]

The best way to pose the question is to fully describe what I want to do.  I have an application that documents board meetings for an association.  The base table for the layout is "Meetings".  There is a related table named "Attendance" and another related table named "Trustees".  On the layout is a portal to the Attendance table with Trustees Names and a checkbox to indicate if they are present or not.  What I want to do is when they create a record in Meetings for a new meeting, I want to populate the Attendance table with records for each Trustee for the current meeting.  Then all they must do is, for each trustee, indicate if they are present or not.

So how do I read each record in the Trustee table and create a record for the new meeting in the Attendance table?

thanks,

 

[eos]

You can add a TO of Trustees to your Meetings table via a Cartesian relationship ('x') and use List ( Trustees_cartesian::id ) to get … well, a list of all trustee's ids; or use ExecuteSQL() to the same effect, but without an additional TO.

 

Either way, when you have that list, you capture the unique meeting ID, go to an Attendance layout and process the trusteesID list in a loop to create new records.

 

A possible script:

# STORE A LIST OF TRUSTEE IDS …
Set Variable [ trusteesIDs ; ExecuteSQL ( " SELECT id FROM Trustees " ; "" ; "" ) ]
# OR List ( Trustees_cartesianTO::id ) ]
# … AND THE CURRENT MEETING ID 
Set Variable [ meetingID ; Meetings::id ]
Go to Layout [ Attendance ]
Loop
  # KEEP A COUNTER TO CONTROL THE LOOP …
  Exit Loop If ( Let $i = $i + 1 ; $i > ValueCount ( $trusteesIDs ) ) ]
  New Record/Request
  Set Field [ Attendance::meeting_id ; $meeting_id ]
  # … AND USE IT TO GRAB THE VALUES ONE BY ONE
  Set Field [ Attendance::trustee_id ; GetValue ( $trusteesIDs ; $i ) ]
End Loop
Go to Layout [ original ]

Be aware that you could do the same without a loop, by importing the ids from the Trustees table into the foreign key field of the Attendance table, then batch-replace the meeting id – but for the typical attendance count of a board meeting, that probably doesn't really make a difference. 

 

Alternatively, you could use a portal with a Cartesian TO as a checklist of the Trustees, where clicking an entry actually adds (or removes) a join table record, and you only add records for actual attendances … on the other hand, the method above gives you a history of not only who attended, but also who could have attended, i.e. was a (active?) trustee at that time.

Speaking of history: over time, some trustees will retire / die / be affected by other circumstance, in short: no longer be a trustee; how do you plan to accommodate for that?

[Matthew F]

What if someone makes a mistake and checks the wrong Trustee?  I think you also need to delete the related record when a box is unchecked. 

[eos]

What if someone makes a mistake and checks the wrong Trustee?  I think you also need to delete the related record when a box is unchecked. 

 

where clicking an entry actually adds (or removes) a join table record

[MikeKD]

Using a tick box to add or remove a join table record sounds to be a really useful potentially - is removing a record pretty straightforward? Are there many precautions needed?

[eos]

Are there many precautions needed?

 

I'd say that depends on your business logic … also, if you just want to record the attendance, but don't need to record any meta-data, or create reports etc., you could just store a list of IDs with the meeting itself. 

 

See attached file.

MeetingsAttendances_eos.fmp12.zip