Max date within a SELECT function

[Mike_Davis]

So I have a table, Productions, with 3 columns: datetime, ID, production. When this table has a new insert, it kicks off a workflow that kicks off an Action. I want the Action to gather the one new ID field that was part of the row that was inserted into the Productions table. I’ve attempted multiple variations of a SELECT function using MAX and MAXROW on the datetime column but no luck. Any suggestions? I’m putting this expression in the “Referenced Rows” field of the Action. Thanks very much.

[Lynn]

Hi @Mike_Davis If you haven’t already, check these out.

help.appsheet.com

SELECT()

Gather column values from row in table or slice

help.appsheet.com

MAX()

Highest item in list

help.appsheet.com

MAXROW()

Row with greatest value in column

[Mike_Davis]

Thanks @Lynn. Those are the ones that got me to where I currently am, but no luck so far. As I think about it more, wasn’t sure if there might be a different way to instruct the workflow/action to use the record currently having the insert performed? Maybe there’s a more accurate way than finding the max record (since another user might perform an insert at the same time).

[Steve]

Mike_Davis:

When this table has a new insert, it kicks off a workflow …

The workflow will automatically use the row that triggered it.

Mike_Davis:

… that kicks off an Action.

That action will also automatically use the row that triggered the workflow.

Mike_Davis:

I want the Action to gather the one new ID field that was part of the row that was inserted into the Productions table.

Yep, the action should automatically use that row.

Mike_Davis:

I’ve attempted multiple variations of a SELECT function using MAX and MAXROW on the datetime column but no luck.

Shouldn’t need any of those.

Mike_Davis:

I’m putting this expression in the “Referenced Rows” field of the Action.

Ooooh! Please post a screenshot of your action’s configuration screen.

[Mike_Davis]

Thanks @Steve for the time to respond. So I’ll expand a bit more on the situation. There are actually 2 Actions called in sequence after the Workflow. The first Action that the Workflow kicks off is called “Get Drives To Update”. (I do this because it looks like the Action can only take effect on the table that the Workflow is spawned from, but I want to update records on a different table, Inventory.) In the “Get Drives to Update” Action I use a Reference Row of “LIST([IT CLP])”. Since the Workflow and follow-on Action uses the row that triggered the workflow, I’m assuming this Action results in a List of a single item and defines the single record in the Inventory table to be updated.

The “Get Drives to Update” Action then calls the “Update Drive Status” Action. In the Set These Columns property I have: “Productions[Production]”. (Screenshot attached). Again, I’m assuming there’s only a single item in the dataset so there should only be a single value for Production?

What it does is it updates the correct record in the 2nd table, Inventory, but it concatenates all of the Productions[Production] values into the field versus just the value from the record that triggered the workflow.

So I’ve been trying to get it to use only the newly inserted record using functions such as MAX or MAXROW, but that doesn’t seem to jive (and isn’t a clean methodology either). Thanks for the help.

[Steve]

Mike_Davis:

In the “Get Drives to Update” Action I use a Reference Row of “LIST([IT CLP])”. Since the Workflow and follow-on Action uses the row that triggered the workflow, I’m assuming this Action results in a List of a single item and defines the single record in the Inventory table to be updated.

Yes, LIST([IT CLP]) will produce a list containing one item: the value of the triggering row’s IT CLP column.

Mike_Davis:

In the Set These Columns property I have: “Productions[Production]”. (Screenshot attached). Again, I’m assuming there’s only a single item in the dataset so there should only be a single value for Production?

Productions[Production] is a table-column reference, which produces a list of all values in the named column of the named table. I’m not sure how you got to your assumption that there’d be only one value.

Mike_Davis:

What it does is it updates the correct record in the 2nd table, Inventory, but it concatenates all of the Productions[Production] values into the field

Yep.

Mike_Davis:

So I’ve been trying to get it to use only the newly inserted record using functions such as MAX or MAXROW, but that doesn’t seem to jive

Typically, the most recently added row will have the highest row number, so you can identify that row with:

MAXROW("Productions", "_ROWNUMBER")

That will give you the Ref value for that row. If you instead only want a value from a column in that row, you can use:

SELECT(
  Productions[wanted-column],
  ([_ROWNUMBER] = MAX(Productions[_ROWNUMBER])
)

(substitute wanted-column appropriately)

See also:

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

How do I get the last row of this thing only? MAXROW( "My Table", "_ROWNUMBER", ([_THISROW].[Thing] = [Thing]) ) Replace My Table with the name of the table whose row you want; and Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID).

FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT() Tips & Tricks 💡

How do I get a column value from the last row of this thing only? LOOKUP( MAX( SELECT( My Table[_ROWNUMBER], ([_THISROW].[Thing] = [Thing]) ) ), "My Table", "_ROWNUMBER", "Wanted Column" ) Replace My Table with the name of the table from which you want the column value; Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID); and Wanted Column with the name of the column whose value you want. See also: MAX()

[Mike_Davis]

Thanks @Steve. So instead of Productions[Production], is there nomenclature for returning only the value of the Production column that’s part of the recently inserted record…since it evidently is passing that record around between workflows and actions? Just don’t want to have to go back to the table to get a max record that may have a newer max record by the time this action executes.

[Steve · Accepted solution]

Mike_Davis:

it evidently is passing that record around between workflows and actions?

Not quite. The workflow gets the row automatically–that’s just how workflow works. The workflow then invokes a configured action on that row, so the action gets the row specifically because the workflow gives the action the row. From there, other actions invoked by that initial action are only aware of the row they’re invoked against–and that’s entirely up to you to determine.

What it appears you’re trying to do is to respond to a change in Productions table by updating some corresponding entry in the Inventory table. You noted:

Mike_Davis:

The first Action that the Workflow kicks off is called “Get Drives To Update”. (I do this because it looks like the Action can only take effect on the table that the Workflow is spawned from, but I want to update records on a different table, Inventory.)

That’s 100% correct.

Mike_Davis:

In the “Get Drives to Update” Action I use a Reference Row of “LIST([IT CLP])”. Since the Workflow and follow-on Action uses the row that triggered the workflow, I’m assuming this Action results in a List of a single item and defines the single record in the Inventory table to be updated.

Also 100% correct, assuming the value of the IT CLP column of the row from the Productions table is a Ref to a row in the Inventory table.

Mike_Davis:

The “Get Drives to Update” Action then calls the “Update Drive Status” Action. In the Set These Columns property I have: “Productions[Production]”.

This is entirely where the breakdown is. Any given action is only aware of the row it was invoked for, but knows nothing about the row it was invoked from. The Update Drive Status action is invoked from a row in Productions for a row in Inventory (the row identified by [IT CLP]), so Update Drive Status is only directly aware of the row in Inventory. You understood this, and were rightly trying to determine what row in Productions triggered the workflow. You simply chose a wrong approach in not understanding what Productions[Production] meant.

This takes us back to this…

Mike_Davis:

Just don’t want to have to go back to the table to get a max record that may have a newer max record by the time this action executes.

Unfortunately, you’re gong to have to do just that–there’s no other option. In my experience, you likely won’t have to worry about a race condition. It seems that workflow is transactional, so nothing outside the workflow should be updating your data while the workflow is running.

If that’s not good enough for you (and I can understand why it wouldn’t be), the alternative is to avoid workflow and attempt to perform all the actions from within the app itself with a Form Saved action, and/or by chaining the Inventory update actions to any existing actions that update Productions. The goal would be to organize the actions so they all run within the app directly–where there’s no contention with other users, and no chance for a race condition–rather than in a workflow.