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! Go to 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])
))
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
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?
thank you all for your time and for the quick responses.
User | Count |
---|---|
37 | |
31 | |
29 | |
22 | |
18 |