Working with nested join records

[RedVox]

I have two tables: scenes and props and because props can appear in multiple scenes, I have a join table called scenes_props.  When showing the details of a scene record, scene_prop records are shown as nested records.  In the nested prop record view the Add button shows a list of props and offers the ability to create a new prop, opening a new prop form. 

Two challenges:

Prepopulating fields - There are two fields in the new prop form (episode, scene number) that really should be prepopulated with the episode and scene number from the scene details record you were viewing before you started adding a new prop.  I know i can create an action to link to the form with the values, but I'd prefer to keep the existing add button and update it's functionality or apply a formula to the initial values of these fields.  But the context of the scene is lost when the the form is opened, and I don't see any way to update the behaviour of the default add button.  

Radio Buttons vs Checkboxes - It seems that a byproduct of having the scene_prop links appear as nested records in the scenes_detail view is that adding new ones can only be done one at a time.  After pressing the add button, the existing props appear as as list with radio buttons.  I'd prefer they appeared with checkboxes so that multiple props could be associated at one time.  Workarounds for this seem to be rather elaborate.  Is there an easy solution?

Thanks!

[Marc_Dillon · Accepted solution]

---

@RedVox wrote:

Prepopulating fields - There are two fields in the new prop form (episode, scene number) that really should be prepopulated with the episode and scene number from the scene details record

---

This sounds like it goes against proper data normalization. If props can be used in different scenes, why would you be recording scene details in the prop record?

RE: adding multiple records at once:

I have often done this by presenting an EnumList column on the parent record ('scene' in your case), which upon form save converts the selected values into separate records ('scene_prop' records in your case). That can be done several ways; an action loop is maybe the most prevalent, but I typically go with an API call these days so that it is less syncs on the user's end.

[RedVox]

Yes, you're completely right about the data normalization.  I am correcting the structure. 

I added an appscript function and bot to make the updates.  But it's a nasty workaround.  Really, when presenting any list to the user, we should be able to configure whether it presents with checkboxes or radio buttons.  It would save me two fields, one table, an appscript and it would save the user from needing to sync each time.

[Marc_Dillon]

---

@RedVox wrote:

when presenting any list to the user, we should be able to configure whether it presents with checkboxes or radio buttons.

---

That's Enum vs EnumList.

[RedVox]

Yes, but only because AppSheet is built with this restriction. We are
really talking about what should be a virtual table, much like a virtual
column with a ref_row formula. All this work of managing a join table etc
could be taken care of by appsheet. From a developer perspective, it would
be a matter of selecting whether a virtual column list should be presented
to the user as checkboxes or radio buttons. Appsheet would be constructing
a virtual join table when the checkbox option is selected. It'd be a very
slick extension of the way AppSheet currently automagically creates virtual
columns for related tables. And it would be so much better for the user.