Creating or deleting records in related table by checkboxes

[Bullion]

I'm not even sure if I'm going about this the best way or not - hence my coming here to get a sanity check and suggestions.

I have the following tables:

t_Events
t_BookedItems

and t_Items

The t_Events and t_BookedItems are related.

t_Items is a table full of items that can be booked for events.

I have a Value List: L_Items based on this table.  (This is so that at any time, items can be added and the system will still work)

 

I have currently set up an t_Events Layout, which has a portal to t_BookedItems in it that lists all the related records.

I know I could just format the fields in the portal to have drop down lists etc and add items and records that way - but I want this process to be super easy and speedy to use.

So I set up a checkbox global field that is based on the L_Items value list - with a script attached that, every time you click a checkbox, it goes to the t_BookedItems layout, loops through the global field checkbox, checks for an existing record, if it exists it skips that one. If it doesn't already exist, it creates one.

This works really well and is instant.... however....

I'm getting stumped on when checkboxes are UNchecked. As in, were previously checked and thus has a record - but then gets unchecked, so the record should be deleted.

I could probably bumbled around for another week trialling more loops and variables to get that to work - but it strikes me that maybe there's a better way?

 

Please chime in if you think there is an easier way - even if it isn't using checkboxes, but perhaps buttons, or little number fields (where you type the number of items), or anything else.   

Also chime in if you think its an ok way to do it, and you have an idea about how to solve the UN-checked box issue! THANKS

[comment]

If you want to use a checkbox to select (and deselect) the items booked for an event, then what do you need the BookedItems table for? You could simply make the checkbox field a regular (i.e. not global) field and be done.

Optionally, if you want to list the booked items in a portal, define a relationship to the Items table using the checkbox field as the match field.

See also the demo here for another approach:
https://fmforums.com/topic/48666-matching-values-in-separate-repeating-fields/?do=findComment&comment=227334&_rid=72594

And also:
https://fmforums.com/topic/88558-padding-a-checkbox-field/?do=findComment&comment=459114&_rid=72594

 

 

Edited May 28, 2022 by comment

[Bullion]

I had a quick look at that post, and was a bit lost, so I'll need to spend more time decoding whether it works for my situation or not.

I have a BookedItems table because it seemed to me that I would have to have way too many fields in the Events table to cope with the permutations needed.

As an example: There's probably about 50 different "Items" that can be booked, there's various quantities on hand of each item, sometimes only one is needed in each event, sometimes 20. Each event might have anywhere between 1 and 15 different kinds of items, with different quantities for each kind.

So to have that held in the Events table... I would have to have let's say 15 Item fields, and 15 quantities fields.  Then if more items get added to the systems and events start using more than the 15 for example - the Events table would have to have more items/quantities fields added to it.

Unless I'm not thinking of an better way to do this?

[comment]

If a booked item has attributes (such as quantity) then you definitely need the BookedItems table. But then your idea of using a checkbox field for selecting the items becomes very awkward: you would need to select the item in a checkbox, then move to the corresponding portal row to add the quantity, then back to the checkbox if you wanted to delete the item's booking. 

Maybe you should consider a portal showing ALL items, similar to the 2nd demo above, but also showing the quantities for items that have been booked. It could be even made to create a related record when you enter a value not the Quantity field, and delete it when you clear it.

 

 

[Bullion]

Yeah I see what you mean.  I was just kind of putting the quantity part of it in my mental "next" pile without considering how it will affect the UX later.

The second example you posted was simple and cool. But I might need to re-think the smoothest way to achieve adding these items and quantities.