Solved: Max date within a SELECT function

Mike_Davis · 11-10-2020 12:17 PM

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.

Steve

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:

That’s 100% correct.

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.

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…

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.

View solution in original post

Lynn

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

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

The workflow will automatically use the row that triggered it.

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

Yep, the action should automatically use that row.

Shouldn’t need any of those.

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

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

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.

Yep.

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:

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

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:

That’s 100% correct.

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.

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…

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.