How to lookup the oldest/newest date from multiple records or the highest value

[Dmitriy Zarossilenko]

.

[Dmitriy Zarossilenko]

For Example when in SQL your do:

Select [id], MAX(CreatedOn) From Table where UserID = 123 group by UserID

 

Result will be:

 

UserId CreatedOn

123 26/02/2018 17:55:11.2422222

 

How to achieve this with scribe lookup block

Because it will lookup only the first record that fits the criteria.

[Manoj Chaurasia]

In a perfect world, the Query, Lookup and Fetch blocks would have the ability to specify the order of returned results. However, that option doesn't exist yet. So you need to get creative.

I needed to do the same thing with the results returned from a Fetch Block (ie: select the record with the newest modified date). I accomplished this using Scribe Labs Variables. Just before your Fetch block, create a Variable with a Null or Empty String. Then inside your Fetch block, get the value of the Variable. Then use the If/Else block to compare a data field in the Fetch block with the value of the Variable. If the data field is older or newer than the value of the Variable, then add other logic to do something (ie: update another Variable, or perform some other actions). Also, in each loop of the Fetch block, be sure to Update the value of your initial Variable that is being used for comparison. Then below the Fetch block, add a Lookup block to retrieve the final value of the Variable.

I have attached a screen shot of a Map that uses this logic. In this example, I needed to select a person's alias name. The table that contained alias names could have multiple records for the same person. I needed to select the most recently modified record and use that as the person's alias name.

[Dmitriy Zarossilenko]

Thank you.. I will try

[Donald Johnson]

Screenshot URL was bad.. Didn't survive the migraton.