Bulk cross referencing in as few clicks as possible

[Tom_Stevens]

I have 2 tables

1. Tracking Numbers (Master / Parent) Table 1 is a listing of Job numbers
2. Related Tracking Numbers (Child) Table 2 is a listing of cross referenced Job Numbers

The image above shows the Tracking number table. You will notice that 3524 is a “Master Invoice”. 3525, 26, 27, 28, 29, & 30 are all also their own Tracking numbers but are also related tracking numbers to the Master Invoice 3524.

I currently have the system set up that you can create a new Tracking Number, and then using a child table “Related Tracking Numbers” tie in any and all other other existing tracking numbers.

The Dream…

I would like to be able to be able to (like a bulk delete), be able to pick all the numbers that are related, then say OK or NEXT, pick the “Master / Parent number”, click Save and have it create a listing of all the selected children in the “Related Tracking Numbers” under the Master number.

Currently I am manually adding the master/parent number to all of the children under each child’s “Related Tracking Numbers” and then the adding the list child numbers “Related Tracking Numbers” each to the master.

Now I am no stranger to “Actions”, and I do image that this whole process would involve “Data execute an action on a set of rows”. That said I’ve never been down this part of the rabbit hole before and there doesn’t seem to be anything that I can find which demonstrates what I’d like to do.

Hive mind what say you?

[Suvrutt_Gurjar · Accepted solution]

If the understanding of your requirement is correct, you could try the INPUT() function.

New INPUT() function --- binding dynamic inputs to a data change action Announcements

SUMMARY: We now have an INPUT(, ) function that can be used to define inputs that are dynamically “bound” to values via user input or via other actions that compose them. This is a beta feature but available for anyone to try. If you run into issues please let us know. As always, we would love your feedback and suggestions. This addresses two popular feature requests: Action: UPDATE EXISTING ROW(s) using values from this row with 92 votes, the top-voted feature request here in the user com…

In the child table’s ref column , please have valid_if as ParentTable[Parent Key]

Then please have an action on the child table that sets the ref column [MASTER TRACKING NUMBER] to the master table with an expression something like

INPUT( “MASTER TRACKING NUMBER”, [MASTER TRACKING NUMBER] )

You can then bulk select the parent tracking number in the child table’ stable view and Input function will present a list of all “MASTER TRACKING NUMBER” s to select from.

Please note that I quickly tested the concept and it works. You will need extensive testing at your end as some test scenarios I might have missed.

The below video shows the parent table ID( “Order ID”) from a list of parent table ids being assigned to child table “Order Details” selected records.

[Tom_Stevens]

Suvrutt_Gurjar

First, thank you for the reply. Second you have almost solved my issue perfectly.

I have had to make only a few modifications. To clarify my situation, all of the Tracking numbers share the parent table “Tracking” & the child table “RELATED TRACKING NUMBERS” is there so I can relate multiple Tracking numbers to a single number.

So in my case I have done the following:

• I have added a “Data: add a new row to another table using values from this row” Action to the “Tracking” (Parent) table that adds data to the “RELATED TRACKING NUMBERS” (Child) table, using the input() function
  INPUT( “MASTER TRACKING NUMBER KEY ID”, [key(Id) ])
  on the “Master tracking number Key Id” row.

I can select the numbers I want to add to the “RELATED TRACKING NUMBERS” (Child) table. Click the “Link to master number”…

But it never gives the option for me to select the number I want to specify as the Master. It instead just writes the 3 new lines to the child table with the respective Tracking numbers as the “MASTER TRACKING NUMBER KEY ID” in place of being able to select a number as in your video example

Thoughts?

[SkrOYC]

Tom_Stevens:

[key(Id) ]

This seems to be the problem.
What are you trying to do with it?

[Tom_Stevens]

Dang! You’re correct I has the Master & The Tracking number formulas flipped. The input() should be on the Tracking number key id column not the “master id” column

Fixed.

Tested and the same issue persists. At no point am I able to pick a number, as is done here:

Thoughts?

[Suvrutt_Gurjar]

I think you are trying to combine two actions into one “Add a new row to another table using values from this row” and within this action, you are trying to have the dynamic INPUT () function. I think this combination will not work together.

As shown in your first post, the approach is based on all the children record being already present .
For this approach to work , please try to add the rows with a “Global” master ID value such as say “GLOBALID1” and thereafter implement the input function based action in the child table. This input action will allow you to change the master ID in bulk.

Please also remember to include below

Suvrutt_Gurjar:

In the child table’s ref column , please have valid_if as ParentTable[Parent Key]

[Tom_Stevens]

I was afraid you’d say that. I will tweak the system and see what I can com up with.

[Tom_Stevens]

Thank you very much for showing me the path Sir.

I have worked some appsheet voodoo magic and came up with a solution to my issue. The solution is as follows.

3 separate actions and a grouped action along with some google sheet side array formulas

Step 1
In my “Tracking” (Parent) table, I added a column to take an input for a master number and then hide it from user view.

Step 2
The child table “RELATED TRACKING NUMBERS", I made 2 new columns that auto calculate and allow me to either take as a reference either a user input Master & Related number, or if no user input is given it looks at the “Tracking” (Parent) table for a “master number” in the new column.

Step 3
An action to “SET MASTER TRACKING NUMBER” in the “Tracking” (Parent) table for selected rows.

Step 4
An action to create child table “RELATED TRACKING NUMBERS" entries based on the selected child Tracking numbers and autofill/link the appropriate child data

Step 5
An action to create child table “RELATED TRACKING NUMBERS" entries based on the selected child Tracking numbers and autofill/link the appropriate master data

Step 6
An action to link and execute the 3 previous actions.

So a Master number with 3 subservient numbers Will create the 6 entries in the child table “RELATED TRACKING NUMBERS" table. 3 entries for the Master number that show each subservient number. Then 3 entries one for each Child that shows the Master number.

It works really well. Thank you again for showing me the new INPUT() function.