Workflow Update Record Action - how to update value on another reference record

[EugeneB]

how do we update a record value in reference table once the current table record updated.

Case Details - Table

• Case ID Column
• Case Status Column

Work Order Details - Table

• Case ID Column (ref to Case Details -> Case ID)
• Work Order Status Column.

How do we change the status of case status column to closed when work order status set to closed.

Been figuring out the action, but not sure which one to use

[Lynn]

Hi @EugeneB
I dont understand what you are trying to do.

[EugeneB]

@Lynn

was trying to create a workflow action to change the status of another reference table.

[EugeneB]

Trying to do as this guide,

help.appsheet.com

Changing Data from a Workflow Rule or Scheduled Report

How to change data in your app on a schedule or when some event happens.

but, strange that i have set it, the record of the case details table, Case Status column not update.

[Steve · Accepted solution]

Action 1

• For a record of this table: Case Details
• Do this: Data: set the values of some columns in this row
• Set these columns:
  • Case Status: "Closed"
• Only if this condition is true: ("Closed" <> [Case Status])

Action 2

• For a record of this table: Work Order Details
• Do this: Data: execute an action on a set of rows
• Referenced Table: Case Details
• Referenced Rows: LIST([Case ID])
• Referenced Action: (action 1)
• Only if this condition is true:

  AND(
    ("Closed" = [Work Order Status]),
    ("Closed" <> [Case ID].[Case Status])
  )
  

Action 2 should be used by your workflow. Action 2 will then use action 1.

[EugeneB]

@Steve thanks so much, appreciate your details explanation. i am marking this as the solution, hopefully someone will find this useful in the future.

i missing the condition check for that, previously i placed it as default with true? but wasn’t that if it is true, it will execute it in regards anything?

Action 1 - * Only if this condition is true: ("Closed" <> [Case Status])
Action 2 - AND(
(“Closed” = [Work Order Status]),
(“Closed” <> [Case ID].[Case Status])
)

[Steve]

EugeneB:

i missing the condition check for that, previously i placed it as default with true? but wasn’t that if it is true, it will execute it in regards anything?

I can’t make sense of this.

[EugeneB]

@Steve can we pass a value from the Work Order Details column to the Case Details column?
example we have [Arrival Time] from the Work Order Details Table, to pass the value to the [Resolution Time] Case Details table?

[Steve]

Sure!

[EugeneB]

I have use LOOKUP()
LOOKUP([_THISROW].[SD Case ID], “Work Orders”, “Case ID”,“Finished Date & Time”)

in the Action 2,

it is a correct way of doing it?

[Steve]

Looks good to me! Does it not work?

[EugeneB]

@Steve it is picking the first entry instead of the latest entry, so the result wasn’t the expected one. however i followed your FAQ guide here FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() - #3 by Steve

To pick up for the latest entry, thanks. it works like charm. thanks @Steve

LOOKUP(
  MAX(My Table[_ROWNUMBER]),
  "My Table",
  "_ROWNUMBER",
  "Wanted Column"
)