Trying to look up the max row for a ref

[Luke_Vancleave]

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 · Accepted solution]

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.

[Luke_Vancleave]

Thank you very much for your assistance!
Here’s what I have now

any(select(Asset Deployment Log[Site ID] ,And([Date/Time] = max(Asset Deployment Log[Date/Time]),[Asset ID]=[_ThisRow].[Asset ID])))

I think that’s working, does it look like it makes sense?
So this will give me the column Site ID from my deployment log that is the last entered AND is also the asset I’m doing the behavior on.

[Aleksi]

Just curious… do you have virtual column like related logs in your assets table?

[Luke_Vancleave]

Yes it automatically made that and I’ve kept it. So far I have kept all the virtual columns it’s made as I find them quite useful

[Marc_Dillon]

On review, I don’t think the solution above will work for all situations. Aleksi is hinting at a better solution where you use the list you already have to remove one of the conditions.

ANY( SELECT( [Related Asset Deployment][Site ID] , [Date/Time] = MAX( [Related Asset Deployment][Date/Time] ) ) )

[Luke_Vancleave]

I see what you mean, so this way I’m having it look through the list of deployment logs related to that asset and to pick the most recent one instead of looking through the entire deployment log until it finds the most recent that also matches that asset?
Just curious, what are we saving by doing this? Is this a sync time saver or is there something else we save by doing this?

[Aleksi]

@Luke_Vancleave In generally… think about a case where you have two tables and both have 1000 records. The virtual related list contains only few records and when reading values from that list like [Related records][Column], it saves the time when trying to find out the result.

[Luke_Vancleave]

Seems to work well, thanks!

[Aleksi]

You’re welcome