How to lookup the oldest/newest date from multiple records? or the highest value?
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.
Screenshot URL was bad.. Didn't survive the migraton.
For Example when in SQL your do:
Select [Id], MAX(CreatedOn) From Table where UserID = 123 group by UserID
Result will be:
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.