Add a new row to another table and update that new row with variable input?

[Jason808]

I have been having trouble figuring out how to create an action that will allow an update to a child table and allow the user to select some of the data.

I tried to create an action "Data: add a new row to another table using values from this row" and was hoping for Advanced/Inputs so that I could get a pop-up asking the user to provide the update but Advanced/Inputs is only in "Data: set the values of some columns in this row".

Alternately, I was also trying to figure out how to create the new row, then access that row and use advanced/inputs to update it through a "Grouped: execute a series of actions".

Also, tried to use "App: go to another view within this app" and "LINKTOFORM()" but was unable to use [_thisrow] to pass the selected records [Item ID]

Any ideas on things I missed or another way to possibly handle it like through references or using MAXROW()?

Thanks in Advance!

[Trevwiller · Accepted solution]

Okay so here it goes.

We will state we have two sheets "Sheet 1" and "Sheet 2". Sheet 1 is where we are creating the new row from and sheet 2 is the table the new row is going in. 

STEP 1 -

We are going to need an action that is make a "add a new row to another table using values from this row". We will fill in the required Data that is needed from this row. We also need a way to link to this row we are on. So we need a column called [Creator Row Key] or whatever you would like. This will give us the ability to reference this row properly when it is made.

Step 2 -

We need to make an action on sheet2 that is a "Set the value of some columns in this row". We choose the columns we would like to input for as well as properly setting up our advanced settings for the [_INPUT]s. 

Confirm that the names for your new inputs are unique.

STEP 3 - 

We will need another action that is "Execute an action on a set of rows".  Our reference statement will be

SELECT(Sheet2[Key],  [key]= MAXROW("Sheet2", "_ROWNUMBER", [_THISROW].[Key] = [Creator Row Key]))

The referenced action will be the action we made in step 2

STEP 4 - 

Make a grouped action set the action from step 1 as the first action and step 2 action as the second. 

Hide the actions from step one and two and customize step 4 action how you would like. Now let's use it. The row will get created and you will be prompted for input. Input data as needed and click save.

[Jason808]

Thank you for the information. I ran into the following problem and not
sure what I am doing wrong.

For Step 3, you suggest "data action on another table", i selected "Data:
execute an action on a set of rows, hopefully thats the right one.

Sheet1 = Items (ID is key)
Sheet2 = InventoryLog (ID is key, Added 'Creator Row Key' but concerned
about subsequent actions, if it will pull the latest record)

TrevStep3:
For a record of this table:Items
Do this: Data: execute an action on a set of rows
Referenced Table:InventoryLog
Referenced Rows:
SELECT(
InventoryLog[ID],
[ID] = MAXROW(
"InventoryLog",
[_THISROW].[ID] = [Creator Row Key]
)
)

Function 'MAXROW' should have at least two parameters: a table name, a
column name, and an optional filter expression

Referenced Action: TrevStep2 Update Location Amount Reference
With these inputs: Amount =0, Reference=""

Data action 'TrevStep3' uses an invalid value expression '=SELECT(
InventoryLog[ID], [ID] = MAXROW( "InventoryLog", [_THISROW].[ID] = [Creator
Row Key] ) )'. Function 'MAXROW' should have at least two parameters: a
table name, a column name, and an optional filter expression

Thank you!

[Trevwiller]

SELECT(
InventoryLog[ID], [ID] = MAXROW( "InventoryLog", "_ROWNUMBER", [_THISROW].[ID] = [Creator
Row Key] ) )

You have to reference the rownumber for this function.

[Jason808]

Thanks, I usually saw that parameter in the examples. Error is gone.

When trying to do step 4, I can only access actions from step1 and step3
when selecting the items table. If I select the inventory table it allows
me to select the action from step2. I put the action for step2 in the
InventoryLog as we are updating the second table rows and the 'do this'
requires the table that you are setting the values on.

Did I miss something else?

Thanks!

[Trevwiller]

MAXROW() pulls the latest record in a table. That is why our filter condition checks for the creator row id. So no matter how many rows you create when you click the button you will always grab the one you just made.

[Trevwiller]

Wanted to check back in and see if your problem has been solved?

[Jason808]

Thanks. Did you see my post about not being able to put all the actions into a group?

[Trevwiller]

yes so action 1 and 3 are in the group action 4. action 2 is on a seperate sheet. Action 3 calls action 2.

[Jason808]

Thank you. When I ran the action, it created the record on InventoryLog but there was no pop up. I checked and action 3 refers to action 2. i also unhid action 2 and manually clicked it and it behaved as expected. thoughts?

[Jason808]

When I had created the action for step 3, it had errored saying it needed default values set for the fields referenced in the action 2 so I had created default values, hoping that when action 3 triggered, it would prompt and overwrite the values. I tested action 3 and changed the default values and it updated with the new defaults but does not seem to be triggering the advanced input prompt in action 2.

[Trevwiller]

What is the order that the actions are ran in top to bottom in the grouped action 4. The first should be action 1, then action 3.

[Jason808]

Action 1 then
Action 3

[Trevwiller]

Photo 1 - The detail view of sheet 1 row

Photo 2 - The Input window for the record made on sheet2 visible in the detail view of sheet1 row detail view.

Photo 3 - The detail view of the record in sheet2 

[Trevwiller]

The only thing I can help further on is if you share photos of the setup, but make sure there is no PII or company sensative data. There has to be a way you are setting it up that is causing the issue. 

to confirm:

Creator ID is a column now on the inventory log table that is getting its value from the step one action that is the ID of the row it is being made from.

All required columns for the table are filled when the row is created from the action and the columns that we are updating are editable.

The input types match the columns types of the columns being updated.

The step 1, step 3, and step 4 actions are on the same table. (The table we want to generate the record from)

Our step 3 action refs the correct table

The step 2 action is on a seperate table. The table we are adding the record to.

The actions "Only if this condition is true" is = TRUE

[Jason808]

sheet1/Itemssheet2/InventoryLogaction2 form

Will review your list as well.

[Jason808]

I checked everything and it was all according to your list. I did however wonder if the input names might have conflicted, though they probably shouldn't so I renamed both of them with a 2 suffix. Now it works!! Now that the basic structure is working, I want to add some ref fields that I was having trouble with. Will let you know how it goes. Thank you so much!

[Trevwiller]

Glad to help! If that solves it would you be able to mark my comment about the steps as a solution so other users with the same issue can find it easier.

[Jason808]

thanks for updating row number, would you also update to step 3 instead of step 2 for the grouped action. not sure if you want to mention that the input names on step 2 should not match the field names. TY!

[Trevwiller]

Will do! You always want to come up with a new name for the input or the app will think it is a column.