Solved: Trying to look up the max row for a ref - Page 2

Luke_Vancleave · 01-18-2020 09:48 AM

Hi, I am trying to make a behavior that is triggered by a workflow. I have one table of assets and another of locations and a third that creates a log of what item gets deployed where. The Table of assets has a ref column for location that I want to change to the most recent location I have entered in the deployment log. I’ve made a workflow so that when I add deployment logs, it triggers a behavior to change the location column for that asset but I’m having a hard time figuring out what expression I need to use to get the most recent location I entered in the deployment log. right now I have
Lookup([_Thisrow].[Asset ID],Asset Deployment Log, Asset ID, Site ID)
but this only selects the log that it finds first, not the most recent.
and then my other attempt was

any(select(Asset Deployment Log[Site ID], in([_RowNumber], list(max(Asset Deployment Log[_RowNumber])))))
But this ALWAYS chooses the location from the last log regardless of if that log was for that item or not
Any ideas?

Marc_Dillon

Starting from your second expression
any(select(Asset Deployment Log[Site ID] , in([_RowNumber], list(max(Asset Deployment Log[_RowNumber])))))

First off, is there any reason that condition expression can’t be changed to just
[_RowNumber] = max(Asset Deployment Log[_RowNumber])
i.e., remove the in() and list() parts?

Second, let’s use AND() and add a 2nd condition for the SELECT() expression to meet
[Asset] = [_THISROW].[id]

Third, using RowNumber probably isn’t the best way to determine the most recent. It will probably work for now though. I suggest adding a timestamp to your AssetDeployment records that you can pull a MAX() from.

View solution in original post