HI, I'm using the deliveries and delivery det...

HI, I’m using the deliveries and delivery details model and got most of my app to work quiet well. I am loaning unique items from stock and need would like to look up a customer to see what stock they have. Also to look up a stock item and see where it is. I have sorted the first of these problems with this formula in customers table:

=SELECT(Stock[Stock ID],[Location]=[_THISROW])

This works ok but relies on the location being correct from this formula in stock table:

=INDEX(SORT(SELECT(Delivery Details[CustomerRef],[Stock ID] = [_THISROW]),TRUE),1)

Before this formula is sorted it lists the deliveries of a stock item in the order they are entered. The last entry in the list is where the stock item was delivered last i.e. where it is now which is what I need know. To reference the last entry in a list I need to sort it in reverse order and INDEX value 1. However when I sort the list it puts it into alphabetical order, not entry (or ideally date delivered) order, so it doesn’t work.

Am I going about this the wrong way? Any help would be most welcome.

Andy

0 10 417
10 REPLIES 10

If your SELECT() is returning KEY column values, you can use the ORDERBY() function rather than SORT() to sort the rows.

To get the last entry in the spreadsheet, you can sort by [ROWNUMBER] in reverse order, then the first item will be the last entry.

See also the Rows with Highest Values example in the attached. TOP() help.appsheet.com

+Steve Coile Thanks Steve, i’ll have a look at your recommendations.

+Steve Coile Hi Steve, I looked into your suggestions, sadly the column value I need to return isn’t a key value so I am struggling to use the ORDERBY() function. Whilst I could manually reorder the back end spreadsheet to get the app to work, this isn’t really practical for the apps daily usage. I shall get my thinking hat on again. I can’t help thinking I’m overlooking a function of Appsheet that I’m unfamiliar with. Thanks for your ideas though, Andy

Also look into MINROW() and MAXROW().

+Steve Coile No Joy Steve, going round in circles! I can get the correct key value as returned value, but not the Stock ID from the Delivery Detail table. All the columns are there to help the filter: Delivery Date, Stock ID, Customer ID. But most are referenced from other table, the Key Column is Delivery Details ID. Its a simple library style app, except there are no library apps to study. Customers table should show what books (Stock ID) they have, and Stock Table should show where the books (Stock ID) are. I just can’t get my head round it. Sorry to be a pain, Andy

There’s really nothing else I can offer without a better understanding of the columns of the involved tables. Could you post a screenshot of the Show view (in Data > Tables) of each table’s column list?

+Steve Coile thanks Steve, will get on it tomorrow when I get a chance.

Hi Steve, managed to get a reference to the last delivery in the stock table which I used to define the Customer. All sorted at last I hope. Thanks for all your help, Andy

Top Labels in this Space