Customers with open orders expression

Phil_Waite · 12-21-2019 05:50 AM

/Hi,
I have 4 tables

Customer
Order
Order Details
Running Sheet 2

I have a slice to only show customers with open orders in the app. I also want to only show customers who have an order with a delivery date that matches the delivery date in another table that is chosen by the user. (Running Sheet 2)

Below is the incorrect expression and google sheet with the chosen date of 19/12/2019. How do I get the expression to compare delivery date on the outlets order with the delivery date in the “running sheet 2” table referenced by unique id “1”?

AND(COUNT(SELECT(Orders[Order Id],AND([_THISROW].[Outlet No]=[Outlet No],[Order Status]=“Open”)))>0,

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

“Running Sheet 2” table

Unique ID	Delivery Date	Excluder	Route	Order Status
1	19/12/2019	Cancelled		Open

Alternatively I have an expression that looks up the contents of the running sheet table values and matches rows to" order status" open and “delivery date” 19/12/2019 but it works when the slice is of the Orders table.

How can i adjust it so that it is a slice of the customers table and returns customers with orders where the delivery data and order status match what the user has selected.

Expression below

AND(CONTAINS(Running Sheet[Delivery Date],[Delivery Date]),CONTAINS(Running Sheet 2[Order Status],[Order Status]))

The reason for doing this is so that a report can be produced from the slice too referencing child and grandchild tables.

Thanks

Phil

WillowMobileSys

Have you considered inserting a LOOKUP() function to return the Delivery Date from the other sheet? It returns a single value instead of a list. That portion of the expression would look like this:

LOOKUP([Unique ID], “Running Sheet 2”, “Unique ID”, “Delivery Date”)

Then in your SELECT() replace “Running Sheet 2[Delivery Date]” with the LOOKUP() above:

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

You can use the LOOKUP() in the same manner for your second expression to pull Order records for reporting.

Phil_Waite

Hi John,

Thank you. That’s works but I just realised that I have more than one status type such as “exported to xero” so the “order status” column in running sheet 2 is an enumlist.

This means that the formula must use CONTAINS as the cell being campared to is as follows.

Open , Exported To Xero , Open (Unarchived)

Cheers

phil

Phil_Waite

Hi Just to clarify here is is the expression below and it is the first part that needs to use CONTAINS

Thanks

Phil

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)

WillowMobileSys

As I have been recently informed, CONTAINS() is to be used when checking for text within text. It will work probably in your case since a list is just a comma separated string.

A better option is to use IN() which is designed for lists.

If you wanted to see if the Status was in some sub-list, You could do something like:

IN(
   LOOKUP([Unique ID], “Running Sheet 2”, “Unique ID”, “Order Status”),
   LIST("Open" , "Exported To Xero" , "Open (Unarchived)")
)

Or , if the sublist can be retrieved with a SELECT(), (assuming a type column in the status list):

IN(
   LOOKUP([Unique ID], “Running Sheet 2”, “Unique ID”, “Order Status”),
   SELECT(table([Order Status]), [Type] = "OpenType")
)

Anyway, have fun!!

Phil_Waite

Thanks John

There is a user input so they can choose the orders and customer by status. The below expression shows customers with orders that have a status that matches any in the list in the expression rather than what is in the cell in the running sheet 2 table which changes according to user selection. That’s why I was looking at CONTAINS as it will not be an exact match but an enumlist.

Cheers

AND(IN(
LOOKUP([Unique ID], “Running Sheet 2”, “Unique ID”, “Order Status”),
LIST(“Open” , “Exported To Xero” , “Open (Unarchived)” , “Cancelled” , “Archived”)
),

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

I think I have gotten offtrack with your original goal.

This question above is resolved. Correct?

Your second question was the below.

I believe the delivery date portion is resolved. Correct? If so, then its just the Status part that needs help. I haven’t really been focusing on the reason behind the expression and I think we might need to visit that now

If I understand correctly, the purpose of “Running Sheet 2” is to allow users to select Delivery Date and a List of status’ - could be more than 1 that they choose in an EnumList. You are trying to create an expression - used in a report or a view - that selects the set of Orders based on these user selections. Do I have that right?

Where I am a little fuzzy (assuming I am not completely off the rails) is the need for this part of the expression at all (correct or not at the moment):

IN(
LOOKUP([Unique ID], “Running Sheet 2”, “Unique ID”, “Order Status”),
LIST(“Open” , “Exported To Xero” , “Open (Unarchived)” , “Cancelled” , “Archived”)

If your status’ in “Running Sheet 2” where chosen from the EnumList, there is no reason to check again if they are part of that list which is what this part of the expression seems to be doing. What is the reason for performing this check?

Phil_Waite

Hi Jon,

I don’t thonk I have explained it very well. There is anothr thread below whwre we are almost there but can’t get the CONATINS bit to work.

Thank you

Phil

WillowMobileSys

Understood. The reason why I asking about why its needed is because of the complexity. It seems you are comparing a List against a List - list of user selected status’ against a list of possible status’. If I understand it correctly, neither IN() nor CONTAINS() will work directly. You’ll need a more complex expression.

IN() simply checks a single value is included in a LIST()

CONTAINS() works by checking if text is CONTAINED inside of another text string. But, It looks for contiguous text. So, if your “Running Sheet 2” status columns has in its list the #1 value and then the #4 value -e.g. “1,4” - it will never return true because the text string of “1,4” doesn’t exist inside of the possible values text string like “1,2,3,4,5,6”

If all you are trying to do is re-confirm that chosen values in “Running Sheet 2” status’ are indeed possible values in the EnumList, I contend that part is not needed and can be safely removed. Unless you have some other need for doing so.

WillowMobileSys

I apologize. I looked back and see that I did get off track. You were simply trying to resolve this part of the expression shown above. I gave IN() expressions meant as examples for you to modify to meet your needs. I think that led us down a bad path. I should have given you an example to use in your expression directly.

@Suvrutt_Gurjar 's suggestion in the other thread of an IN() clause is the way to go.

Sorry for the confusion and Good Luck!!

Happy Holidays!