Updating A Status on a different Table

[Tom_Cat]

Hi, So I have a couple of tables:
[Table A] is my main entry table where I manage all of the data, one of the editable columns in Table A is “status”, status is an enum that can be “On time” or “Past Due” or “Closed”.
[Table B] is a reference table and is used to populate a calendar view, note Table B is not accessible and it’s entries are only automatically made via Workflow actions. There are 3 entries in [Table B] for each entry in [table A]
I am trying to create a new Workflow Action that when you go to edit a Table A part. (for example you go edit line 5 of Table A which has Serial No. 1234, the Workflow will find the 3 entries in Table B and change their status (that was copied originally) to the new status.
So far I have the following (which does not work):

• WorkFlow: Update Child Events:
• When TableA gets updated:
• Do Action: UpdateCall

Action UpdateCall on TableA:
For a Record on TableA
Do this: Data execute an action on a set of rows from reference TableB
Referenced Rows = LIST([Serial No.]) //(I want to find all the rows in TableB that have the same Serial No as TableA)
Refrence Action = UpdateStatusTableB

Action: UpdateStatusTableB:
For a Record in TableB
Do this: Set the values of some columns in this row:
Set these columns: [Status] = text(SELECT(TableA[Status], ([Serial No.] = [_THISROW].[Serial No.])))

This doesnt work.

[Aleksi]

Hi, you need to create a list of key values with the Referenced Rows. Your formula LIST([Serial No.]) probably won’t do that. You should write something like…
SELECT(TableName[KeyColumnName],[Serial No.]=[_THISROW].[Serial No.])

[Tom_Cat]

Hi Aleksi, thanks for the quick reply. I dont understand your SELECT statement. Does this statement go in the Action for Table A or for Table B or in the Workflow?.
What is [KeyColumnName], isnt it [Serial No.] ??

[Tom_Cat]

Hi Aleksi,
Also another question, I did SELECT(TableA[Serial No.], [Serial No.] =[_THISROW].[Serial No.])
I didnt get any errors, but it doesnt seem to be updating.
I have a question because maybe its a semantics thing. When you click on “edit” on an entry, that is considered an update, correct?
So if I go to entry Serial No. 1234 on TableA and edit it’s status to Finished, and then save and push sync, that counts as an “update” that should trigger this whole set of events?

[Bahbus]

What is your key column for Table B?

[Tom_Cat]

TableA Columns:
TransactionID = UNIQUEID() (cannot be entered by user)
Serial No. = Gets entered by user
Status = Gets entered by user.
TableB is automatically filled whenever a new entry in TableA is created.
TableB creates 3 entries per single entry in TableA: Input date (through actions) reminder and due date. these 3 events are then displayed in a calendar.
TableB columns:
TransactionID = UNIQUEID() (cannot be entered by user)
EventType = (Enum, can be input, reminder or due date)
Serial No. = Gets copied automatically by actions when a data row is added to table A
Status = Gets copied automatically by actions when a data row is added to table A.

I want to make it so that when I edit entry serial no. 1234 in Table A (the only editable table) it will look up its 3 corresponding rows in table B and update their status to match the same status that was updated in tableA

[Bahbus]

Ok, so TrasactionID is probably the ones marked as the Key columns for those tables.

Tom_Cat:

For a Record on TableA
Do this: Data execute an action on a set of rows from reference TableB
Referenced Rows = LIST([Serial No.]) //(I want to find all the rows in TableB that have the same Serial No as TableA)
Refrence Action = UpdateStatusTableB

Action: UpdateStatusTableB:

Referenced Rows needs to be a list of Keys to TableB as Aleksi said.

Aleksi:

SELECT(TableName[KeyColumnName],[Serial No.]=[_THISROW].[Serial No.])

So, SELECT(TableB[TransactionID], [Serial No.]=[_THISROW].[Serial No.]) will get you the Keys from TableB that you want to update.