Lookup last delivery date in orders but ignore cancelled ones

[Phil_Waite]

Hi,

I have an expression that looks up the last delivery date from a table of orders. Please see below.

Orders is the other table we are searching.

(MAX ( SELECT(Orders[Delivery Date], [Outlet No] = [_THISROW].[Outlet No]))

How do I get it to ignore orders where the column [Order Status] in table Orders = “Cancelled”

Many thanks

Phil

[LeventK]

@Phil_Waite
Try this:

MAX(
	SELECT(
		Orders[Delivery Date],
		AND(
			[Outlet No]=[_THISROW].[Outlet No],
			NOT([Order Status]="Cancelled")
		)
	)
)

[Phil_Waite]

Hi Levent,

Thank you

Phil

[Phil_Waite]

Hi Levent,

this is actually part of a longer expression where I Concatenate three colums into one to save space vertically in the desktop version of the app.

Here it is

IF(MAX ( SELECT(Orders[Delivery Date],AND( [Outlet No] = [_THISROW].[Outlet No],NOT([Order Status]=“Cancelled”))))>[Last Order],

TEXT((MAX ( SELECT(Orders[Delivery Date], AND( [Outlet No] = [_THISROW].[Outlet No],NOT([Order Status]=“Cancelled”)))),“mm/dd/yyyy”)),[Last Order])

&" “&[Price List]&” "&IF(MAX ( SELECT(Beer Buyer Answer[Beer Buyer Spoken To Date], [Outlet No] = [_THISROW].[Outlet No]))>[Beer Buyer Spoken To],TEXT((MAX ( SELECT(Beer Buyer Answer[Beer Buyer Spoken To Date], [Outlet No] = [_THISROW].[Outlet No])),“mm/dd/yyyy”)),[Beer Buyer Spoken To])

The previous version with out the cancelled status in worked produced the correct result for the expression but after inserting the cancelled status I get “Invalid Date”

I have turned the date into text as otherwise it reverts back to the US format (i’m in the UK).

Any help appreciated.

Cheers

Phil

[Phil_Waite]

Hi sorry,

Just realised that I had the column set as date rather then text!

The below expression is returning the US format still. (even after changing mm/dd to dd/mm)

TEXT((MAX ( SELECT(Orders[Delivery Date], AND( [Outlet No] = [_THISROW].[Outlet No],NOT([Order Status]=“Cancelled”)))),“dd/mm/yyyy”)),[Last Order])

I’m assumingI have parentheses in the wrong place?

Thanks

Phil

[LeventK]

@Phil_Waite
I don’t understand why you need to use TEXT expression to format the date actually. If you have set your table locale correctly, AppSheet always produces the same date format. Therefore provided you compare the column value with TEXT(…, “mm/dd/yyyy”) expression’s value, it will possibly return FALSE. I believe this might work better:

IF(
	MAX(
		SELECT(
			Orders[Delivery Date],
			AND(
				[Outlet No]=[_THISROW].[Outlet No],
				NOT([Order Status]="Cancelled")
			)
		)
	)>[Last Order],
	MAX(
		SELECT(
			Orders[Delivery Date],
			AND(
				[Outlet No]=[_THISROW].[Outlet No],
				NOT([Order Status]="Cancelled")
			)
		)
	),
	[Last Order]
)&" "&[Price List]&" "&
IF(
	MAX(
		SELECT(
			Beer Buyer Answer[Beer Buyer Spoken To Date],
			[Outlet No]=[_THISROW].[Outlet No]
		)
	)>[Beer Buyer Spoken To],
	MAX(
		SELECT(
			Beer Buyer Answer[Beer Buyer Spoken To Date],
			[Outlet No] = [_THISROW].[Outlet No]
		)
	),
	[Beer Buyer Spoken To]
)

[Phil_Waite]

Hi Levent,

I have attached a screen shot with the locale set as UK and the last delivery showing as 12/10/19 and last beer buyer spoken to as 12/04/19 which is the wrong way round. All other dates on the app are correct. As you can see next call is 18/12/2019.

thanks

Phil

[LeventK]

@Phil_Waite
How many users you have? I’m asking this because, when entering dates into a form, AppSheet always takes into account the locale setting of the mobile device that the user have. That might be the reason for your dates appearing in different formats.

Secondly, can you please ensure that the dates columns in your gSheet are also formatted the same way rather than set to AUTO.

[Phil_Waite]

Hi,

I have 3 users and everything that goes through comes out with the correct format when they put the orders in. I have attached a screen shot of the google sheet.

I assumed it reverted back to us format when converted to text.

thanks

Phil