Combine Top(sort(select(Datetime))) expressions together

Chinh
Participant I

hi, I’m new to the community and hopping to get some pointers from the pros.
I have a “customer” table w [customer ID] key and 3 other tables linked to that key “repair orders”, “Sale orders”, “device Buy-in” (also each of those 3 tables has its own [datetime] column.
Now I’m trying to get a formula for a column called [Customer since] in the “Customer table”. the formula is to tell Appsheet to put together all the corresponding datetimes of all the transactions (from those 3 tables) that related to a specific customer ID and sort them out and finally give out the earliest result
I’ve been trying that by combining these expressions together TOP(), SORT(), SELLECT() but none seems to work correctly so far.
Could someone please point me to the right direction?

Solved Solved
1 8 225
1 ACCEPTED SOLUTION

LIST() shouldn’t be there at all. Try without it:

MIN((Select(ServiceOrdersSL[Date],[Customer ID]=[_THISROW].[Customer ID]) +
Select(DeviceSaleSL[Picked up],[Customer ID]=[_THISROW].[Customer ID]) +
Select(DeviceBuyinSL[Buy-in Date],[Seller’s ID]=[_THISROW].[Customer ID])
))

View solution in original post

8 REPLIES 8

Hello @Chinh , welcome to the community !

How about this expression:

MIN(LIST([Related repair orders][datetime],[Related sale orders][datetime],[Related device Buy-in][datetime]))

Let me know if it works for you

Thank so much for the quick response.
I tried this MIN(LIST(Select(ServiceOrdersSL[Date],[Customer ID]=[_THISROW].[Customer ID]) +
Select(DeviceSaleSL[Picked up],[Customer ID]=[_THISROW].[Customer ID]) +
Select(DeviceBuyinSL[Buy-in Date],[Seller’s ID]=[_THISROW].[Customer ID])
))
ERROR “The inputs for function ‘MIN’ should be a list of numeric values”

This is what I’ve tried so far, I’ve tried to get a combine list of all the dates from the 3 tables first, then sort() them

SORT(LIST(Select(ServiceOrdersSL[Date],[Customer ID]=[_THISROW].[Customer ID]) +
Select(DeviceSaleSL[Picked up],[Customer ID]=[_THISROW].[Customer ID]) +
Select(DeviceBuyinSL[Buy-in Date],[Seller’s ID]=[_THISROW].[Customer ID])
))

I was able to get a result but it’s not sorted, could this be because date time type? this was the returned result:
2/1/2021 11:27:00 AM , 2/28/2021 11:27:00 AM , 1/13/2021 11:28:00 AM , 11/1/2020 11:31:00 AM , 1/4/2019 2:28:00 PM , 3/18/2020 2:27:00 PM , 5/1/2017 2:27:00 PM , 2/18/2021 2:18:37 PM , 6/16/2018 2:18:37 PM , 10/27/2020 2:18:37 PM , 11/13/2019 2:18:37 PM

Once I’m able to get that list sorted properly then my next step would be wrapping it with a TOP() expression as I previously read up on some similar threads

Did you try my expression though?
3X_9_3_937774a197a56ab5afe56f73d4ff007ab60f4c8c.gif

yes I did, I tried this I have to use select() in it to filter our the right records corresponding to a specific customer ID.
MIN(LIST(Select(ServiceOrdersSL[Date],[Customer ID]=[_THISROW].[Customer ID]) +
Select(DeviceSaleSL[Picked up],[Customer ID]=[_THISROW].[Customer ID]) +
Select(DeviceBuyinSL[Buy-in Date],[Seller’s ID]=[_THISROW].[Customer ID])
))

ERROR i got was “The inputs for function ‘MIN’ should be a list of numeric values”
I changed data type to list, number, date, date time. all got the same error

LIST() shouldn’t be there at all. Try without it:

MIN((Select(ServiceOrdersSL[Date],[Customer ID]=[_THISROW].[Customer ID]) +
Select(DeviceSaleSL[Picked up],[Customer ID]=[_THISROW].[Customer ID]) +
Select(DeviceBuyinSL[Buy-in Date],[Seller’s ID]=[_THISROW].[Customer ID])
))

Wow, amazing. it’s working now. thank you so much!
by the way, do you know why the result has a yellow triangle sign with an exclamation mark in it? what does that mean?

Chinh
Participant I

thank you all for your time and for the quick responses.

Top Labels in this Space