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
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().
MINROW() help.appsheet.com
MAXROW() help.appsheet.com
+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
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |