CONTAINS expression

[Phil_Waite]

Hi,

I have a user imput table to choose customers based on the date of orders and the status.

4 tables

Customers
Orders
Order Details
Runnign Sheet 2 (the user input table with column for “Delivery Date” and “Order Status”

The expression is for a slice of the customer table but the Delivery date and order status are held in the child table Orders. The below expression works fine if only open orders are required.

The “Order Status” column in Running Sheet 2 (user input) is an enumlist where they can choose “open” and “eported to xero” for example. The cell being looked at would have “open, exported to xero” in it. So the first part of the expression needs to use CONTAINS rather than equals but I can’t work out how to write the expression.

AND(COUNT(SELECT(Orders[Order Id],AND([_THISROW].[Outlet No]=[Outlet No],[Order Status]=LOOKUP( [Unique ID],“Running Sheet 2”, “Unique ID”, “Order Status”))))>0,

COUNT(SELECT(Orders[Order Id],AND([_THISROW].[Outlet No]=[Outlet No],[Delivery Date]=LOOKUP( [Unique ID],“Running Sheet 2”, “Unique ID”, “Delivery Date”))))>0)

Many thanks

Phil

[Suvrutt_Gurjar]

Hi @Phil_Waite,

May I request you to try an expression something like below, if my understanding of your requirement is correct. Could not test the expression though

AND(COUNT( SELECT( Orders[Order Id],AND([_THISROW].[Outlet No]=[Outlet No],IN([Order Status], LOOKUP( [Unique ID],“Running Sheet 2”, “Unique ID”, “Order Status”)))))>0,

COUNT(SELECT(Orders[Order Id],AND([_THISROW].[Outlet No]=[Outlet No],[Delivery Date]=LOOKUP( [Unique ID],“Running Sheet 2”, “Unique ID”, “Delivery Date”))))>0)

Assumptions
A) [Order Status] in " Orders" table is text typevwith only one status possible at a time,
B) [Order Status] in “Running Sheet 2” is enumlist type with multiple simultaneous selections possible by the user.,
C) Expression needs to return all rows from "Orders’ table with all the statuses selected by user in enumlist column [Order Status] of “Running Sheets 2” table.

[Phil_Waite]

Hi Suvrutt,

Thank you.

The expression doesn’t return orders with matching “order Status” unless the matching order status in the enumlist is the first one in the list.

I have attached screen shots of the enumlist selection and result. All orders for the chosen date have an order status of “Exported to Xero”

Many thanks

Phil

![run sheet 3|690x388]

[Phil_Waite]

I have just tried replacing IN with CONTAINS and have the same result.

Phil

[Steve]

Phil_Waite:

So the first part of the expression needs to use CONTAINS rather than equals…

Nope. Use IN().

[Steve]

What are you trying to determine about [Order Status]? That it contains exactly the same entries? That it contains at least the same entries? That it contains Open? You haven’t described your problem; you’ve only asserted your expression doesn’t work.

[Suvrutt_Gurjar]

Hi @Phil_Waite,

Thank you for all the details.

Could you please try following expression

AND(COUNT( SELECT( Orders[Order Id],AND([_THISROW].[Outlet No]=[Outlet No],IN([Order Status], SPLIT(LOOKUP( [Unique ID],“Running Sheet 2”, “Unique ID”, “Order Status”),“,”)))))>0,

COUNT(SELECT(Orders[Order Id],AND([_THISROW].[Outlet No]=[Outlet No],[Delivery Date]=LOOKUP( [Unique ID],“Running Sheet 2”, “Unique ID”, “Delivery Date”))))>0)

[WillowMobileSys]

@Suvrutt_Gurjar I agree with your suggested solution with the IN() function.

[Phil_Waite]

Hi Steve,

I am looking at the user defined cell that holds an enumlist chosen by the user and want to return orders that have an order status that is in that list.

Below is an expression I use in another part of the app for a slice based on user input. (I think you told me how to do this by the way so this is for explanation purposes). It uses CONTAINS because the user defined cell has “prospect, active, barred” for example and it is comparing the text to see if the Outlet Rating is contained in that list.

CONTAINS(Filterlist[Outlet Rating],[Outlet Rating])

The expression we are looking to resolve is for a slice of a Customers table that has a child table with the relevant values in it which is why it’s getting complicated (for me anyway).

I could just slice the “orders” table but I want to run reports from it and I found it eaier to format the reports by going down the chain - customer - orders - order details - rather then up it from Orders to Customers (all the examples online go down the chain.)

Currently the user defined cell in “Running Sheet 2” is this below

Unique ID	Delivery Date	Excluder	Route	Order Status
1	19/12/2019	Cancelled		Exported To Xero , Open

The date part works fine as only ever one date will be selected by the user.

There are a number of orders on the day that I am looking at 19/12/2019 that all have “Exported to Xero” as the order status. I could change the first part of the expression to work in the same way as the second but that would only work if there was only ever one value chosen by the user. As above there are two “exported to xero” and “open”.

AND(COUNT( SELECT( Orders[Order Id],AND([_THISROW].[Outlet No]=[Outlet No],IN([Order Status], SPLIT(LOOKUP( [Unique ID],“Running Sheet 2”, “Unique ID”, “Order Status”),“,”)))))>0,

COUNT(SELECT(Orders[Order Id],AND([_THISROW].[Outlet No]=[Outlet No],[Delivery Date]=LOOKUP( [Unique ID],“Running Sheet 2”, “Unique ID”, “Delivery Date”))))>0)

The above expression only seems to return the orders with “exported to xero” if it is the first in the enumlist (which it is above) but an order with status “open” wouldn’t be returned. I also have “Open Unarchived” as a status option listed before “Exported To Xero” and if I check that then we lose an “Exported to Xero” returns.

Thanks

Phil

[Steve]

You have a very convoluted data set.

Is there a Unique ID column in either Orders or Customers?

Is Order Status in Orders an EnumList?

[Phil_Waite]

Hi Steve,

Customers has Outlet No as a unique id and order id as unique id for orders. Order status in orders is only ever one value.

Thanks

Phil

[Steve]

Phil_Waite:

Customers has Outlet No as a unique id and order id as unique id for orders.

Your expression uses [Unique ID]. What table has that column?

[Phil_Waite]

Hi Steve,

Table “Running Sheet 2” has unique ID. This table is for user input.

Unique ID	Delivery Date	Excluder	Route	Order Status
1	19/12/2019	Cancelled		Open (Unarchived) , Exported To Xero , Open

thanks

Phil

[Steve]

Okay. That being the case, this:

LOOKUP(
  [Unique ID],
  “Running Sheet 2”,
  “Unique ID”,
  “Order Status”
)

will always return the Order Status column value of the very first row of the Running Sheet 2 table. Is that your intention?

[Phil_Waite]

Hi Steve,

Yes that’s the cell i’m looking for.

phil

[Phil_Waite]

Here are the tables below

“Runnng Sheet 2” table has “Order status” and “Delivery Date” changed by the user and they can select from an enumlist for the “Order Status”.

Customers
Outlet No	Name	Short Address	Outlet Rating	Outlet Type	No Answer	Standing Delivery Instructions/Order Details	Group	User	Name & Town	Address 2	Address 3	Town	County	Post Code	Price List	Bm Spoken to	Next Call	Last Order	Sales Notes	General Notes	Please Call Next Run	Surname	Forename	Time	Contact Type	Primary Contact No	Beer Buyer Spoken To	Secondary Contact No	Contact Email	Special Attention	Real Ale?
1	Broadstraik Inn	Elrick	Special Attention	0		xyz	Patron Capital	phil@listersbrewery.com	Broadstraik Inn Elrick	0	0	Elrick	Aberdeenshire	AB32 6TL	Band 2	30/10/2019		30/10/2019			TRUE	xyz		10:20:00	xyz	01224 743217	30/10/2019			0	Yes
Orders
Order Id	Contact Status	Order Status	Price List	Name & Town	Outlet no	Order Date	Delivery Date	Complete Order Total Inc Vat	Order Taken By	Date Taken	Time Taken	Standing Delivery/Order Details	Payment Type	Cash Status	Invoice Number
c136cad6	TRUE	Cancelled	Band 2	Broadstraik Inn Elrick	1	22/12/2019	19/12/2019	88.8	phil@listersbrewery.com	22/12/2019	17:07:25	xyz		FALSE	#N/A
Running Sheet 2
Unique ID	Delivery Date	Excluder	Route	Order Status
1	19/12/2019	Cancelled		Cancelled , Exported To Xero , Open

I want to be able to slice the “Customers” table based on customers that have an order with delivery date equal to “19/12/2019” where they have an “order status” in the “orders” table that is either Cancelled, Exported to Xero or Open as chosen by user in “Order Status” in table “running sheet 2”.

In the above case the customer “Broadstaik Inn Elrick” should come up as it has an order on the 19th with an order that is cancelled.

At the moment the expression doesn’t return it unless Exported To Xero is unselected from the enumlist and “Cancelled” is the first in the list.

From what you have said about IN and looking it up it appears that it should do.

Thanks

Phil

[Steve]

Try changing this:

SPLIT(LOOKUP( [Unique ID],“Running Sheet 2”, “Unique ID”, “Order Status”),","))

to this:

SPLIT(LOOKUP( [Unique ID],“Running Sheet 2”, “Unique ID”, “Order Status”)," , "))

[Phil_Waite]

HI Steve

I get the same result.

Thanks

Phil

[Suvrutt_Gurjar]

Hi @Phil_Waite and Hi @Steve,

As per my understanding the challenge is because the expression is in a way trying to compare list with list. One list is SELECT (Orders[Order ID]… and second list is Enumlist of selections.
I will request @Steve’s or @WillowMobileSystems inputs/opinion on this.

In the meantime, I have tried to test a similar approach on a sample app but with much lesser conditions than those @Phil_Waite is having in his actual requirement.

I will retest the approach for correctness and try to post if it is good by putting it in a similar expression as that @Phil_Waite needs.

[Suvrutt_Gurjar]

Hi @Steve,

The delimeter space was indeed a good catch. I believe we all those are trying to convert a text string into list should always remember it.