Any(Select()) - Return value from the bottom of the table first

[MatthewEngel]

Hi AppSheet Community 

I'm trying to solve an issue where i need the latest value of a table where the conditon is met. 

In Excel i'd use a Xlookup function and search it from the latest data entry(ie the latest row) up the rows returning the most recent entry that meets the criteria. 

How do we do this in appsheet? 
I currently have : 

any(Select(ShippingRates[Cost per Ton],[_THISROW].[Ship Routing]=[Ship Routing]))

This is used as an initial value in a column to get the latest shipping rate from the shipping rates table. 

The issue is the random select. I need it to be the largest row number where the criteria is met. 

Any pointers would be super helpful! 

PS. It would be amazing if appsheet were able to update the lookup function to allow the user to select how the function searches through the data. IE top to bottom or bottom to top 🙂 

[MatthewEngel]

UPDATE: 

I've Managed to get it to work by creating a slice with the following filter: 

[_ROWNUMBER] =MAX(SELECT(Shippingrates[_ROWNUMBER],([Ship Routing] = [_THISROW].[Ship Routing])))

Then where I am looking for the latest shipping rate I use Any Select again and search in the new slice that I made. 

However I'm now realizing that I need to not only get the latest shipping rate, but also the most competitive( I.e., where the ship routing is equal to multiple options and I then select the smallest value. 

Any idea how to do this?

[MatthewEngel]

UPDATE 2: 

Is there a way to create a slice that check the latest shipping rate entry against the previous rates and then the slice only filters out the lowest price? 

PS. the rates are all linked to a Ship Routing - IE. Madrid to New York

[dbaum]

Questions like this should be posted in the AppSheet Q&A board, where they're more likely to be seen by someone who can provide an answer and won't confuse anyone subscribed to this Tips & Tricks board for the type of content it's intended for. I suggested to moderators that they move the post.

Regarding your questions, if you haven't already, review the following functions, which might be relevant to your needs:

MINROW and MAXROW

LOOKUP

[MatthewEngel]

Hi Dbaum, 

I'm not sure how it ended up here, Will post it again in the Q&A Section. Sorry about that. 
I'm still learning how to navigate the community forums 🙂 

Re your suggestion. I realized I need to not only check the latest entry , but I also need to check if the new entry is smaller than the previous one and only return which ever is the smallest of the two numbers 🙂