Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Order status based on date combinations

Hello,

 

My question is related to showing the Status in a large database of different orders based on: same order number and Received / Shipped combinations?

 

I have included a sample below. Basically there can be dozens of same order numbers, with different quantities, but different Received / Shipped date combinations (Received and Shipped, Partially shipped, etc.)

 

The thing that throws me off is the "same order#", how could I group them, while still having different data on a row by row basis? What would be the best choice here?

 

Thank you!

 

Order#QuantityReceived dateShipped dateStatus
12345678 1229/01/202430/01/2024Received and Shipped
12345678 229/01/202430/01/2024Received and Shipped
12345678 3429/01/202430/01/2024Received and Shipped
12345678 2129/01/202430/01/2024Received and Shipped
12345678 1129/01/202430/01/2024Received and Shipped
12345678 629/01/202430/01/2024Received and Shipped
87654321 429/01/2024 Only Received
87654321 3729/01/2024 Only Received
87654321 2329/01/2024 Only Received
87654321 229/01/2024 Only Received
54321678 45  Ordered
54321678 32  Ordered
54321678 11  Ordered
54321678 7  Ordered
67854321 5629/01/2024 Partially received
67854321 3  Partially received
67854321 4329/01/2024 Partially received
67854321 2129/01/2024 Partially received
67854321 9  Partially received

 

  • lbendlin's avatar
    lbendlin
    2 years ago

    Note that this is a calculated column, tagging each order line.

     

    Status = 
var o = [Order#]
var a = filter(all('Table'),[Order#]=o)
RETURN switch(TRUE(),
countrows(a)=sumx(a,if(ISBLANK([Received date]),0,1)) && countrows(a)=sumx(a,if(ISBLANK([Shipped date]),0,1)),"Received and Shipped",
countrows(a)=sumx(a,if(ISBLANK([Received date]),0,1)),"Only Received",
sumx(a,if(ISBLANK([Received date]),0,1))>0,"Partially Received",
"Ordered")

     

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    Note that this is a calculated column, tagging each order line.

     

    Status = 
var o = [Order#]
var a = filter(all('Table'),[Order#]=o)
RETURN switch(TRUE(),
countrows(a)=sumx(a,if(ISBLANK([Received date]),0,1)) && countrows(a)=sumx(a,if(ISBLANK([Shipped date]),0,1)),"Received and Shipped",
countrows(a)=sumx(a,if(ISBLANK([Received date]),0,1)),"Only Received",
sumx(a,if(ISBLANK([Received date]),0,1))>0,"Partially Received",
"Ordered")