Boolean field in a table that depends on the status of boolean field in one or more related records in a join-table.

[andersmnystrom]

In a hobby solution I work on, one part is a reoccurring medicine-event where one or more medications should be checked as "Done" in conjunction with the medicine being taken. Finally, you should check the medicine event "Done" as a whole. I would like that this last part was done automatically when you have checked/taken all medications in that event.

 

The basic structure is an Event-table and an Object (medicine) table with a join table in between. A row in the event table is a medication event and one or more medications are added to the event via the join table. A field in the joint table is called “Done”, it is a boolean field where 0 = not done and 1 = done. There is a similar field in the event table that shows if the medicine event as a whole is complete or not.

 

The layout for the event shows the medications to be taken in a portal and you check that you have taken the medication by going through the medications in the portal and pressing a button for each one that changes the boolean field in the joint table to a 1. I would like the boolean field in the event table to automatically change to a 1 when you have checked all the medications for the event but can't figure out how to do this. Alternatively, that a hidden button becomes visible on the layout when you have checked all the medications for the event. Then you can check the event by pressing that button.

 

I hope this makes sense, ask me otherwise.

[Wim Decorte]

See reply on community.filemaker.com: I would shy away from using calculated fields for the "done" result.

https://community.filemaker.com/en/s/question/0D50H00007AIAafSAH/boolean-field-in-a-table-that-depends-on-the-status-of-boolean-field-in-one-or-more-related-records-in-a-jointable

[comment]

Use a calculation that compares the count of related join records with the sum of the Done field.

 

6 minutes ago, Wim Decorte said:

I would shy away from using calculated fields for the "done" result.

I would shy away from unnecessary optimizations. Unless OP has a very large number of records (which seems highly unlikely based on the given description), an unstored calculation will serve them perfectly well.  A scripted procedure, OTOH, would not be trivial for a novice to implement reliably.

 

Edited September 14, 2019 by comment

[andersmnystrom]

1 hour ago, Wim Decorte said:

See reply on community.filemaker.com: I would shy away from using calculated fields for the "done" result.

https://community.filemaker.com/en/s/question/0D50H00007AIAafSAH/boolean-field-in-a-table-that-depends-on-the-status-of-boolean-field-in-one-or-more-related-records-in-a-jointable

Yes I read it know. Thanks!!