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

(Andy Hambleton) #1

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.


(Steven Coile) #2

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

(Andy Hambleton) #3

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

(Andy Hambleton) #4

+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

(Steven Coile) #5

Also look into MINROW() and MAXROW().

(Steven Coile) #6

MINROW() help.appsheet.com

(Steven Coile) #7

MAXROW() help.appsheet.com

(Andy Hambleton) #8

+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

(Steven Coile) #9

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?

(Andy Hambleton) #10

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

(Andy Hambleton) #11

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