Many-to-many: prepopulate when adding new related ...

DMA2022 · 08-05-2022 03:00 PM

Each table in my app has a many-to-many relationship to the other tables. Ideally, when a user is in a detail view, they can choose to "Add New" from a Related Records field, and the record from the previous screen will prepopulate in the related table's form dropdown for that field.

Here's what the structure is currently:

Multi-select in related fields. In each table, users have the option to select multiple, prepopulated records from the other tables. ACCOMPLISHED: I chose the EnumList/Ref type fields to minimize bloat from what would be a massive join table.
Display multiple related records. Each table has a related records field for each other table, displaying multiple related records from the other tables. ACCOMPLISHED: I use a Select statement to filter to the related records.
Add New from the Related Records field. Upon clicking "Add New" in a Related Records field on the detail screen, a form enters for that related table. But it does not prepopulate the field from the prior screen in the related field.

As an example, there are three tables: Contacts, Tasks, Appointments. Every Contact can be related to multiple Tasks and Appointments, every Task can be related to multiple Contacts and Appointments, and every Appointment can be related to multiple Contacts and Tasks.

The app immediately built the related fields as "Ref" types, based on the underlying worksheet. But that only allows a single selection in the related field. In the Tasks form, I can only choose one related Contact or Appointment.

Changing the field types easily lets me multi-select related items and display multiple related items in fields.

Unfortunately, using the EnumList/Ref solution, I cannot pass through the related record when I choose to "Add New" from the Related Record field. So if I'm in Task Detail, and I see three related Contacts, and I want to add another Contact, clicking "Add New" opens the Contact form, but the Task field is empty. I would like that field to prepopulate with the prior screen's Task ID.

I've read multiple posts regarding this but do not yet see a solution. I may be misunderstanding the solutions, though:

1) This solution appears to prepopulate the destination field with at least a dropdown of records that have relationships (Joseph_Seddik). So in the new Contacts form, there would be a list of all Tasks that have any related Contacts. This is different from an empty dropdown but not better, since virtually all Tasks have related Contacts.

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Add-a-new-row-to-a-ref-table/m-p/389674#M152923

2) This was the same question, but no solution was reached; just more discussion around whether to use a join table vs enumlist/ref field types: https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Enumlist-Base-Type-Ref-gt-Add-New-Value-gt-form...

This is the initial thread that got me to rebuild the app away from Join Tables and using EnumList/Ref: https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Multiple-Selection-Reference-Field/m-p/379869

This is a great example of EnumList/Ref that also includes related/"dereferenced" fields, which I don't use (or don't know how to, if they solve the ''Add New" problem): https://www.googlecloudcommunity.com/gc/Tips-Tricks/Use-Enum-EnumList-Base-Type-REF-to-De-Reference-...

3) This was the same question, and the discussion was around trying to use deep link actions or dereferenced fields, but no solution was reached: https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Contextual-fill-for-Alternate-quot-Add-quot-for...

4) This was the same question (or similar), and the discussion included a suggestion to use a Select statement but no clarification on how one would do that in connection with "Add New": https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Prepolutating-a-Virtual-Column/m-p/350576#M1217...

5) This may be related, so storing here. It suggests changing from the initial value of a field to an app formula: https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Initial-value-in-form/m-p/333541

I don't want to add to what is a long list of unsolved posts, but I am hopeful that if someone has actually succeeded in prepopulating a related field via "Add New" using the EnumList/Ref approach to visualizing relationships, they will post it in response here.

MultiTech

@DMA2022 wrote:
Unfortunately, using the EnumList/Ref solution, I cannot pass through the related record when I choose to "Add New" from the Related Record field. So if I'm in Task Detail, and I see three related Contacts, and I want to add another Contact, clicking "Add New" opens the Contact form, but the Task field is empty. I would like that field to prepopulate with the prior screen's Task ID.

To provide a little background on the mechanics of things:

The reason the parent ID isn't passed along when you hit that system add button, is due to the fact that it's NOT a system generated inline view.
- This is one of the known limitations of the system, which hopefully at some point will be ironed out.
But as it stands, the only way to get the inline add button to pass the parent's ID into the form... is to use the system generated [Related Whatever] reverse reference.
- And you can't modify the formula IN ANY WAY
- @Steve knows of some great posts about this topic

-----------------------------------------------------
How to pass the Parent ID to the child
-----------------------------------------------------

The only way you'll be able to pass the parent's ID to the child, is by creating your OWN deeplink action using LINKTOFORM() to pass the parent's ID into the child form when opening it

LinkToForm("FORM_NAME_HERE"
	"COLUMN_TO_SET", [VALUE_TO_SET_IT_TO]
)

But this means you can't use the system generated ADD button, so you'll need to hide that completely - which means.... you can't have the nice interface of pressing the little add button under the inline view.

Sux... I know. Price we pay for all the other power we're given access to with the platform. 😉

MOverturf

Is it true that large font sizes facilitate learning?

MultiTech

@MOverturf wrote:
Is it true that large font sizes facilitate learning?

????? Ummm.... maybe? 🤔

It certainly help make things easier to read, and can add emphasis to important aspects people should really pay attention to.

MultiTech

I've been struggling with this problem (many to many relationships in AppSheet) since the beginning.

AppSheet doesn't natively support it, so you're stuck to resorting to work-arounds using brute force - which won't scale in any way.

--------------------------------------------------------------------------
THE SOLUTION
--------------------------------------------------------------------------

I think the real answer is a combination of both methods

Use join tables to actually accomplish the connections, but utilize an enumlist + action looping for easier data entry
Use an Enumlist on the parent, allowing for rapid selection of previous items
On the save of the parent form, kick off an action-loop to create all the records selected
Since the join records will be held in a system generated reverse reference, the add button will work as expected
On the save of the child form, run an update to push that value (the one you just saved) into the parent's enumlist

You'll end up with tons of join records. But those join records will create that list you're looking for (the one you're brute forcing with SELECT() right now) on the parent level, using the efficiencies of the system.

DMA2022

UPDATE: I searched and found MV's thread and sample app for using actions to integrate a join table back end and EnumList/Ref front end:

https://www.googlecloudcommunity.com/gc/Tips-Tricks/Looping-with-Actions-Sample-App-BUILD-Video/m-p/...

Realizing now how helpful it would be if users could add our own tags to solutions (in a separate field from the official ones)!

MV's sample app: https://www.appsheet.com/templates/How-to-loop-through-a-list-and-create-child-records-for-each-item...

(This is the video further illustrating how to use EnumList/Ref and Select to create related records and view them without a join table: https://www.googlecloudcommunity.com/gc/Tips-Tricks/Many-to-Many-Relationships/m-p/425284#M6371)

I just saw this, thanks - do you have this in action somewhere it can be viewed? This would be a significant rebuild but worth it if it will work for the nicer UX of just clicking Add.

The only issue is the size of that join table, which might shut the whole thing down rather quickly.

MultiTech

@DMA2022 wrote:
The only issue is the size of that join table, which might shut the whole thing down rather quickly

How big are we talking about?

DMA2022

In one of the m2m relationships, there are about 15,000 discrete records in one of the main tables, and each record would have at least 10+ related records in another table, for one user, initially. There are two other tables that also have related records, and 3-4 users.

MultiTech

@DMA2022 wrote:
In one of the m2m relationships, there are about 15,000 discrete records in one of the main tables, and each record would have at least 10+ related records in another table, for one user, initially. There are two other tables that also have related records, and 3-4 users

Perhaps if you describe your scenario, we here in the community might be able to find a better way

DMA2022

Yes, just watched down your looping video and thinking through how best to apply it to my structure. I can't read another thread, lol; hoping to land on something definitive for all of my apps at this point.

MultiTech

I'm currently working on a set of updated sample apps for my portfolio

One-to-Many References
BRUTE FORCE Many-to-Many Refs
JOIN TABLE Many-to-Many Refs

I'm finding a lot of holes that I never really explained with the brute force method (that's using SELECT() to get your lists); many more holes than I thought actually.

#NotAsStraightForwardAsIThought

DMA2022

Yes, I'm hopeful to get to a many-to-many solution that allows easy adding and displaying of related records, both in the record creation (form) and record viewing (detail) UIs.

Brute Force

Brute Force has worked the best for me - adding related records using EnumList/Ref field type with Suggested Values (vs validation, which doesn't allow new adds in Form view), then viewing related records using List type field and SELECT statements. But not being able to natively add new items to that list using the "Add" option is a pain.

Trying to remove that Add option in favor of an action is a design question and possibility.

I haven't hit any other holes so far but am curious what else I'm not yet running into that you're seeing as you review?

Join Table

I'm reluctant to try this because of scaleability, but using the loop to add related records in bulk, then changing the EnumList/Ref to refer to the join table would at least allow the "Add" feature to work.

Ref Field Type

This has two flow limitations that there may be workarounds for. First, users can't add more than one related record in form view. This is confusing and kills flow.

Users can add multiple related records AFTER completing the initial record using a List type field with a Ref table. So if I can design a flow that lets them create an initial record, then pass them to new inline views to "Add Related ___" then that might actually work. And I believe I found somewhere a way to hack a form to display an inline table (UPATE: haha - it's your video: https://www.youtube.com/watch?v=7NzOY1jeTqo and there's also this action that can direct to a dashboard view: https://www.googlecloudcommunity.com/gc/Tips-Tricks/Tabs-in-Detail-Views/m-p/281110)

This may be the way to go, even as I type it, since there's no join table and you can use native REF_ROWS to display.

Thoughts?

UPDATE: I've tested out this third option and am going to move ahead with this as the many-to-many solution. It means creating almost all custom UIs, but the ease of using the native reference table functional seems worth it. Will see how users respond and follow up.

Markus_Malessa

I have been using the brute force action looping method for quiet some time, but it is really bothersome because it slows down the process. In some instances between the grouped actions that creates entries in a child table from the parent record creation it may end up in a total of 36 or more new records across up to 4 tables. Ultimately that may result in a 30 second delay for the browser where I get the 'Page Unresponsive' popup. I really wish this functionality could be improved within AppSheet.

MultiTech

Hey @Markus_Malessa

Creating such a small number of records shouldn't be causing a problem like you're experiencing.

Perhaps it's just a matter of implementing some additional structures in your app to help reduce the computational load?

If you take a look at the Brute-force many-to-many sample app I have, there's some elements inside the setup to help reduce the strain in this manner. Specifically:

On each side of the many-to-many, I've got columns that hold "what's changed"
- What's been ADDED
- What's been REMOVED
By having these two separate fields like this, whenever a change is made for the ref-connections - the system can know exactly which records it needs to update.
The system can even know which operation it needs to execute, because there's the separate lists of what's been added vs. removed

Here's a video going over the sample app, and how all the nuts-and-bolts work together

Markus_Malessa

Thanks for the response. It appears that the particular sample you are mentioning is not utilizing a join table. In my specific case I am using a join table. I suppose maybe that is where I am lacking efficiency. But I only get the 'hang time' and freeze on one specific process. I enter one Annual Report -> kicks of a sequence of actions that adds references in join table (typically between 1-5 entries) -> then enters 12 entries in Water Use Table -> then kicks off another sequence entering (1-5 entries) in another join table.

My looping action consists as follows and is fired when clicking the form save button:

MultiTech

I've got you covered for Join Tables too. (^_^)

Join tables can a bit more convoluted; the "remove items from the opposite side of the reference" becomes a bit more involved - temporary variables, slices, and such to smooth out the interface.

Markus_Malessa

I would certainly agree that working with join tables is a bit more convoluted. That is why I think if Appsheet would introduce a forEach() function, the looping process itself could be significantly improved, that is of course in the absence of just improving many-to-many relationship handling directly. If we could get Google to improve both of these processes that would be excellent of course.

I have found that using SELECT statements against the join table in the conditional section to determine if the loop should fire as well as in the add row to the join statement for the relationship key value as I pointed out in my screenshots, actually gives me the benefit of eliminating the temp value setting that you have incorporated in your sample app. Of course what I don't know is whether my process or your process is more efficient or has any significant execution time savings associated with it. My general problem is for every newly introduced temp value column I have to make a change to the MySQL database, which was not originally designed with the AppSheet anomalies in mind. I had to integrate these anomalies after the fact since the original database and web application was designed with Google App Maker, which exhibited much better relationship handling on the many-to-many side. I have made due with what is available within Appsheet however, but can't help and think at times how much faster this kind of process would execute if the programming was a bit more straight forward.

DMA2022

NOT AN OPTION

Thanks, @MultiTech - this is where I stopped a while back before trying to noodle this again.

Thought/Question - since there is a system view for the "Add" option, perhaps there's a way to modify the related action by screen. For instance, if I copy the system Add and create one for the Tasks option, maybe I can pass the deep link in the Add action?

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Hide-the-View-button-in-Inline-REF-ROW/m-p/2820...

It appears that it can be edited but not displayed similarly if it uses a Deep Link: https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Change-add-button-system-generated-actions/m-p/...

MultiTech

If you look at the configuration of that sys-gen ADD action - it's just one of native options inside the "Do this:" dropdown - add a row to this table

The problem with how things are setup, is that all the 'reference magic' is applied to that action - only in the right context (not talking CONTEXT() here).

For the longest time, we've been trying to get them to separate out that behavior in some way. Yet to happen.

We've also been begging for years for the ability to drop an action into the same line as the (View Add) line under the inline view. That would at least give me the ability to create my own deeplink action and put it there - so no one would know the difference.

But no... you have to use a deeplink, because that's the only way to pass the parent's ID into the child form.

JPAlpano

Im trying to remember how I used to do this M2M setup way back in MS Access era.

If my memory serves me right, i had to create another table that joins the two tables together. That table only has the primary key of the 1st table and the prinary key of the second table. And both fields are combined as a composite primary key.

I wonder how that process can be adopted to Appsheet.

DMA2022

Yes, the join table option is #2 on my options list here. In AppSheet, as
with Access, you manually create a join table. Then you create actions
(automations) to push selected related records to the join table. There are
links for MV's videos on this in the thread.

I worry that join tables will slow things down quickly given the size of my
related records lists. So I redesigned today using native ref and ref list
fields only and removing the multi-select related fields from my forms. I
will try to teach to adding related records AFTER creating each underlying
record and see how the users respond. Will follow up.

MultiTech

Microsoft Access actually supports many to many relationships themselves, so a join table wouldn't be necessary in that situation. (It'd be nice if AppSheet would support it too!)

JPAlpano

It does? Not the way I remember it from before. That was way back 2005-ish. I didn't touch MS Access that much starting 2008. So it could be a later version enhancement.

DMA2022

Same! My last regular Access build was 2007, then a big project in 2010, but we still used join tables for that (which maybe could have been avoided!)

MultiTech

@JPAlpano wrote:
It does? Not the way I remember it from before. That was way back 2005-ish. I didn't touch MS Access that much starting 2008.

I haven't touched that stuff for quite some time myself; and to be honest, back then all the "many-to-many" stuff made my head hurt. 😝

Markus_Malessa

My main issue with the looping action (not really true looping) is that it consists of three separate actions. I think if AppSheet were to introduce a true looping action that might improve things.

Many-to-many: prepopulate when adding new related records via EnumList/Ref structure vs join tables