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
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.
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
I have just tried replacing IN with CONTAINS and have the same result.
Phil
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)
@Suvrutt_Gurjar I agree with your suggested solution with the IN() function.
Nope. Use IN().
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.
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
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?
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
Your expression uses [Unique ID]
. What table has that column?
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
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?
Hi Steve,
Yes thatโs the cell iโm looking for.
phil
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โ)," , "))
HI Steve
I get the same result.
Thanks
Phil
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.
Hi Suvrutt,
Thank you.
Phil
Hi @Phil_Waite,
I request you to explore the following approach. Even though I tested it in a limited way, I believe there could be much simpler approach by creating a slice on the Orderstable itself and linking it to Customers table. This Orders table slice could be further explored.
The approach - ( A bulky one )
A) Create a VC in Customer Table called say [OrderStatus] with expression something similar as [Related Orders By Customer Name].[Order Status] Here [Related Orders By Customer Name] is a reverse reference column in the Customer Table that reverse references Orders table. [Order Status] is the column name fromOrders table that has order status.
B) Create second VC in the Customer table called say [FilterStatus] with an expression such as LIST (Running Sheet 2[Order Status]) If there is just one row in the Running Sheet 2 table, the expression should work
C) Create 3rd VC in the Customer table called say [All Outlets] with an expression as [Related Orders By Customer Name].[Outlet No] Here [Outlet No] is the outlets column name in Orders table
D) Create a slice on the customer table with an expression similar to below
OR(AND( IN(INDEX([FilterStatus],1),[Order Status]), IN([Outlet No], [All Outlets]),[Delivery Date]= (Running Sheet 2[Delivery Date])),
AND( IN(INDEX([FilterStatus],2),[Order Status]), IN([Outlet No], [All Outlets]),[Delivery Date]= (Running Sheet 2[Delivery Date])),
AND( IN(INDEX([FilterStatus],3),[Order Status]), IN([Outlet No], [All Outlets]),[Delivery Date]= (Running Sheet 2[Delivery Date])),
AND( IN(INDEX([FilterStatus],4),[Order Status]), IN([Outlet No], [All Outlets]),[Delivery Date]= (Running Sheet 2[Delivery Date])))
Request you to note
Edit: The Running Sheet 2 was incorrectly named Running Sheets 2 in expression in D)above. I have coorected it.
Hi Suvrutt,
Thank you I will give that ago. I have tried making the expression simpler to see what is going on and found that the below expression doesnโt give what is desired.
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]=LOOKUP( [Unique ID],โRunning Sheet 2โ, โUnique IDโ, โDelivery Dateโ))))>0)
Rather then return cusomers with an order on 05/12/2019 with order status โopenโ on that date. It returns a customer that had an order on 05/12/2019 even though it had order status cancelled because there is another order for the same customer that is a different date but that one is โopenโ. It is not connecting the date and order status to the same order.
Below are two orders for the same outlet.
When 05/12/2019 is selected in the user defined cell the expresion returns โThe Gardeners Armsโ as a customer even though the order on that date was cancelled as there is another order that is โopenโ for that customer (that order is on the 19/12/2019 but it is coincidence that we have been using that date in previous posts)
How do I specify that the status and date have to be for the same order?
To clarify at the moment the expression returns Customers that have at least one order on the chosen date regardless of itโs order status as long as there is another order for that customer that is โopenโ on any date.
We are looking for customers with an โopenโ order on the chosen date.
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 | ||||||||||||||||
c12ba8d4 | TRUE | Exported To Xero | Open | Band 3 | Gardenerโs Arms Sompting | 34609 | 16/12/2019 | 19/12/2019 | 278.4 | hilary@listersbrewery.com | 16/12/2019 | 09:50:34 | Deliver Anytime | FALSE | 3175 | ||||||||||||||||
ca8daf8a | TRUE | Cancelled | Closed | Band 3 | Gardenerโs Arms Sompting | 34609 | 02/12/2019 | 05/12/2019 | 0 | hilary@listersbrewery.com | 02/12/2019 | 10:06:00 | Deliver Anytime | FALSE | #N/A |
Thanks
Phil
Try:
ISNOTBLANK(
FILTER(
"Orders",
AND(
([Outlet No] = [_THISROW].[Outlet No]),
(
[Delivery Date]
= LOOKUP(
[Unique ID],
"Running Sheet 2",
"Unique ID",
"Delivery Date"
)
),
IN(
[Order Status],
LOOKUP(
[Unique ID],
"Running Sheet 2",
"Unique ID",
"Order Status"
)
)
)
)
)
If Running Sheet 2 has and will only ever have exactly one (1) row, you can shorten the above to:
ISNOTBLANK(
FILTER(
"Orders",
AND(
([Outlet No] = [_THISROW].[Outlet No]),
([Delivery Date] = ANY(Running Sheet 2[Delivery Date])),
IN(
[Order Status],
ANY(Running Sheet 2[Order Status])
)
)
)
)
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.
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
User | Count |
---|---|
42 | |
30 | |
25 | |
23 | |
13 |