"Add Many" records in a one-many relationship

[MINCH_Household]

A need an App that has similar functionality as a customer ordering 10 items from a menu in a restaurant. The list of foods isn’t edited. But the waiter creates a new “order” and adds 10 refereces to foods from the “Menu” table. This order would be stored in a linking table which adds 10 records having the same order reference but having 10 different food references.

I want to be able to open a screen, tick 10 items, and then have them all added with [_THIS] order number. I do NOT want to have to “Add” items individually because my actual application has lists of maybe 100 items.

My example app makes one-many links between “Serial Numbers” and “Collections” using a table of references called a “Links” table.

The App is functional here

AppSheet

AppSheet

Create mobile apps for your business in just minutes. No code, no delay, no cost.

The “Links” table only has two columns, a ref to “Serial Numbers” and a ref to “Collections”.

When viewing a collection, the related “Serial Numbers” are correctly listed at the bottom with the actions “View(4)” and (New) are there as normal.

What I want is an additional button “Add Many” which (for _THIS “Collection”) adds as many “Serial Numbers” as the user selects at once. Obviously, this will need to add a new row to the “Links” table for every “Serial Number” that the user wants to add into this collection.

The problem is that built-in “Add” functionality adds a single record to the “Links” table and opens it for editing. Adding 50 serial numbers into a collection is not practical like this. I want to have a form that allows the user to select any number of “Serial Numbers” from an Enum_list and then add this many records into the “Links Table”

Can anyone suggest ways of doing this?

[LeventK]

@MINCH_Household_Wate
You can achieve this with triggering AppSheet API thru a webhook workflow rule and it’s easy to accomplish actually regarding what data you want to record to the other table.

[MINCH_Household]

OK. This sounds promising. Having looked at my structure (or my app) can you suggest what I might make the webhook do? (I’ve never used them so dumb it down a little). Thanks for engaging Levent!

[MINCH_Household]

AppSheet

AppSheet

Create mobile apps for your business in just minutes. No code, no delay, no cost.

[LeventK]

@MINCH_Household_Wate
Provided you can share the app with levent@able3ventures.com with co-author status and the gSheet with edit access, I can take a look at it. And also please briefly explain what do you want to accomplish.

[MINCH_Household]

What I want to achieve is …

MINCH_Household_Wate:

I want to be able to open a screen, tick 10 items, and then have them all added with [_THIS] order number. I do NOT want to have to “Add” items individually because my actual application has lists of maybe 100 items.

…

MINCH_Household_Wate:

What I want is an additional button “Add Many” which (for _THIS “Collection”) adds as many “Serial Numbers” as the user selects at once . Obviously, this will need to add a new row to the “Links” table for every “Serial Number” that the user wants to add into this collection.

[LeventK]

MINCH_Household_Wate:

tick 10 items, and then have them all added with [_THIS] order number

What do you mean with [_THIS] order? Can you elaborate?

[MINCH_Household]

LeventK:

What do you mean with [_THIS] order? Can you elaborate?

Good question.
You’ll see that at the moment when you click on “Collection A” for example, it has a list section at the bottom called “Serial Numbers in THIS collection”. There are a few related serial numbers. There is an Add button. But I want an Add Many button where I can just select in one operation say 10 serial numbers I want to add. Then they all get added to “Collection A”

[MINCH_Household]

One thing I tried was to have one column in “Collections” to be actually a list of “References”. I could get it to contain uniqueids, but they did not seem to be interpreted by the software in a way which enabled viewing of reverse references. Looking at records of “Serial numbers” did not for instance, list the “Collections” in which they occurred. Appsheet does complain that “lists of references can only be virtual columns”. I don’t know why because here is a good demonstration of why it would be good to have them as actual columns.

The App in it’s current form allows the user to see both forward and reverse references (e.g. See all the “Serial Numbers” associated with a “Collection”, or indeed see all “Collections” associated with a “Serial Number”

Unfortunately that structure it doesn’t provide the necessary “Add Many” functionality.
Hope you can help. I’m sure this is not problem unique to me.

[Wingo_Wingo]

Following. I want to know the solution too. It is quite common in restaurant order taking scenarios too.

[Riki_Armstrong]

I managed to something similar with an ENUMLIST with a base type of REF and a VALID_IF that refers to the look up table. The user can select multiple items which are stored as a list.

[LeventK]

@MINCH_Household_Wate
Here it’s for your information…You can test with pressing the red hexagon plus button in the deck view. It will automatically append the selected Collection Name and you can multiple select Serial Numbers from the Enumlist. When saved, you will see that a separate row will be created in your Related table in that Collection’s Detail View for each one of the serial numbers that you had selected.

[Riki_Armstrong]

This is really cool. Anyway you can add a ‘look under the hood’ option for this or add it to tips and tricks?

[LeventK]

@Riki_Armstrong
The app does not belong to me, @MINCH_Household_Wate shared it with me with co-author status. But I will try to explain how I have done it under this post for members’ reference.

[Jeremy_F]

Kindly tag me when you post it.

[LeventK]

@Riki_Armstrong
@Jeremy_F
@Wingo_Wingo
I will post the solution tonight. Prior to that post, please bear in mind that the solution will require an AppSheet Pro Plan and AppSheet API integration with a webhook workflow. Regards.

[LeventK]

@Riki_Armstrong, @Jeremy_F, @Wingo_Wingo
I will try to explain how I have done it. I will explain it in the basics of the app that’s forwarded to me by @MINCH_Household_Wate so you can adopt the same aspects to your apps I believe

---

LOOK UNDER THE HOOD

---

[Basics]
The app had 3 tables:

• Collections
• Links
• Serial.Nos

Links table had 3 columns:

[uniqueid]
[Collection.Ref] - Ref to Collections table
[Serial.Ref] - Ref to Serial.Nos table

@MINCH_Household_Wate was requesting to be able to multiple select serial numbers for the related table of each Collection record (parent)

[Design Intent & Approach]
To be able to create such a functionality, I have firstly created a Helpler Table identical with the Links table with slight changes. I have named the columns and the column parameters like this:

{
	Column Name: ID
	Type: Text
	Key: TRUE
	Initial Value = UNIQUEID()
	
	Column Name: COLLECTION
	Type: Ref
	SourceTable: Collections
	isPartOf: FALSE
	InputMode: Dropdown
	
	Column Name: SERIAL
	Type: Enumlist
	AllowOtherValues: TRUE
	AutoCompleteOtherValues: TRUE
	BaseType: Ref
	ReferenceTableName: Serial.Nos
	InputMode: Dropdown
	Valid_if: =SELECT(Serial.Nos[KeyColumn],NOT(IN([KeyColumn],SELECT(Links[Serial.Ref],[Collection.Ref]=[_THISROW].[COLLECTION]))))
}

I have added a Ref position, Form Type view for this helper table. Than I have added an Action to the Collections table (red hexagonal plus button) with below properties:

{
	Actiona name: Add Multiple
	For a record of this table: Collections
	Do this: App: go to another view within this app
	Target: LINKTOFORM("HelperTableFormViewName","ID",UNIQUEID(),"COLLECTION",[CollectionsTableKey])
}

After all, I needed to create the mechanism that will loop thru the selected enumlist items in my Helper Table and create a reference record for each one in the Links table which is the base ref table for both Collections and Serial.Nos tables. For this mechanism, I would be needing the AppSheet API to perform the operation. Hence, I have activated the API from Integrations pane and created a webhook workflow with below properties:

{
	Target data: Helper Tablename
	Update event: ADDS_ONLY
	Condition: TRUE
	Reaction: Webhook
	Url: https://api.appsheet.com/api/v2/apps/{appId}/tables/Links/Action
	HTTP Verb: Post
	HTTP Content Type: JSON
	Body: {
					 "Action": "Add",
					 "Properties": {
							"Locale": "en-US",
							"Location": "47.623098, -122.330184",
							"Timezone": "Pacific Standard Time"
					 },
					"Rows": [
						<<Start: SELECT(Serial.Nos[KeyColumn],IN([KeyColumn],[_THISROW].[SERIAL]))>>
						{
							"uniqueid": "<<UNIQUEID()>>",
							"Collection.Ref": "<<[COLLECTION]>>",
							"Serial.Ref": "<<[KeyColumn]>>"
						},
						<<End>>
					]
				}
	HTTP Headers: applicationAccessKey: "Your API Access Key Here"
}

[MINCH_Household]

@LeventK
This is so AWESOME. Major breakthrough for me. I’ve needed this functionality on a few APPS and it looks like it’s helping a few people.

I can see that you’ve really had to do some quite significant development here to achieve this! I am very grateful for the pointers.

One thing does occur to me. The intuitive way that a user like myself may attempt to get this kind of functionality is to try to implement a column that is a List of type Ref. Would there be any way that Appsheet could provide this kind of functionality where a typical user might predictably look for it. I guess this depends on how many people are actually trying to work around solutions that actually provide key (IMHO) functionality that has multiple (IMHO) applications.

But again @LeventK Awesome demo.
Please feel free to make that bit of software accessible to everyone. Or can I do that? Or does it need tidying up a little bit to suit the Table / Order / Menu scenario.

[LeventK]

MINCH_Household_Wate:

But again @LeventK Awesome demo.
Please feel free to make that bit of software accessible to everyone. Or can I do that? Or does it need tidying up a little bit to suit the Table / Order / Menu scenario.

Thanks @MINCH_Household_Wate
You can mark the app as a sample app in your own portfolio page.

[Sarah_Keown]

@LeventK out of interest, is there any solution to this problem without having access to AppSheet Pro Plan/API intergration etc.

I have been looking into getting an action to set the column value from a user selected dropdown, using multi select. I was informed not long ago that there were people looking into this as a new feature but when I saw your solution I was interested on any helpful input you may have?

[LeventK]

Hello @Sarah_Keown,
Other than AppSheet API integration, the only solution is Google Apps Scripting with use of onChange(e) trigger which I have also created a post under Tips&Tricks here. The cons to this workaround/alternative is you need to sync the app to see those added records with the script. The pros to AppSheet API is, you see the added records instantly.

How to Integrate Google Apps Script & Trigger with an AppSheet App Tips & Tricks 💡

@LeventK - looks like I struck a passionate cord! Really appreciate all your (and the other Appsheet team members) efforts to share and help us re-imagine how to get “stuff” done. The wonderful thing is there are endless possibilities if you have the desire to learn, the imagination, and the endurance. The hard part is, how to segment the Appsheet audience so people get what they need, at the level they are, and can learn more when ready. @Peter wondering out loud - is the community b…

[Henry_Scott]

@MINCH_Household_Wate can you post the Link from your Portfolio of this solution that @LeventK assisted you with

[MINCH_Household]

@LeventK
Does this solution still work if the app is offline and doesn’t have internet access? Or does the use of a webhook mean that the functionality doesn’t apply unless internet is available.

I created this app to ease the communication of the challenge. My actual app is for tracking where parts of a water filter went around Ethiopia. The trouble is that many of the workshop or warehouse destinations will not have internet access and the app will need to function offline.

[LeventK]

@MINCH_Household_Wate
The app could work offline for sure but it will not be able to create those Links records from the enumlist till the app finds an active internet connection.

[MINCH_Household]

I would also like to just point out some hidden AWESOMENESS to the app tweaks that @LeventK has achieved here …

You can only ADD things into a collection that are not already IN. This is perfect for my application although I’ve yet to discover how/why that is so.

[LeventK]

MINCH_Household_Wate:

I would also like to just point out some hidden AWESOMENESS to the app tweaks that @LeventK has achieved here …

You can only ADD things into a collection that are not already IN. This is perfect for my application although I’ve yet to discover how/why that is so.

@MINCH_Household_Wate
The trick lies under the Valid_if expression of the [SERIAL] enumlist column in the Helper table.