How to mark client who had ordered FLOWERS?

[antimixer]

I have 2 tables - Clients (100 people) and Orders. Clients sometimes order Flowers.
The tables are already linked and have the following structure:
- Clients (clientUID, name).
- Orders (orderUID, clientUID, goodsIndex, sum).
I need the whole list of my clients (100 people) in one column ("name") and in the second column I need to put "ok" - if at least once the client ordered flowers (goodsIndex = "flowers").
Clients have many other orders and flowers could also be ordered more than once.  And some customers might not order flowers at all.
How can I do this?

[WillowMobileSys · Accepted solution]

You already have a list of your Clients in the Clients table.  Simply add a Virtual Column "OrderedFlowers?" or some such name.

Based on your provided details, here is the exact expression to use.  Place it in the App Formula of the "OrderedFlowers?" column.

IF( IN("Flowers", SELECT(Orders[goodsindex], [clientUID] = [_THISROW].[clientUID])),
    "ok",
    ""
) 

 This column will be updated automatically should any future Orders be placed for Flowers.

[bonameblisto]

If your tables are connected correctly, then in the Clients table there should be a virtual column with a name similar to [Related Orders] - this is a list of client orders.

Create another virtual column named for example FlowersBuyer with the formula

count(select([Related Orders][OrderUID],[goodsIndex] = "flowers"))>0

After that you can create a slice for the Clients table with the condition [FlowersBuyer]

[WillowMobileSys · Accepted solution]

You already have a list of your Clients in the Clients table.  Simply add a Virtual Column "OrderedFlowers?" or some such name.

Based on your provided details, here is the exact expression to use.  Place it in the App Formula of the "OrderedFlowers?" column.

IF( IN("Flowers", SELECT(Orders[goodsindex], [clientUID] = [_THISROW].[clientUID])),
    "ok",
    ""
) 

 This column will be updated automatically should any future Orders be placed for Flowers.

[antimixer]

Hello, thanks for helping!

I want one more column in my Report about Flowers - column named "3months", where I need mark "ok" only if Flowers was ordered in last 3 months. I tryed different variants, but have small expierence how it can be reached.  Table "Orders" has column "OrderDate". Can You explane me this formula?

[WillowMobileSys]

Sure.  Just a slight addition to the previous  expression:

IF( IN("Flowers", SELECT(Orders[goodsindex], 
                         AND([clientUID] = [_THISROW].[clientUID],
                             [OrderDate] >= TODAY() - 90))),
    "ok",
    ""
) 

[antimixer]

Excuse me, but if necessary to show only This month orders?
There must be any like:   
month([OrderDate]) & "." & year([OrderDate]) = month(now) & "." & year(now) ?

But if I try:

IF( IN("Flowers", SELECT(Orders[goodsindex], 
              AND([clientUID] = [_THISROW].[clientUID],
              month([OrderDate]) & "." & year([OrderDate]) = month(now) & "." & year(now)))),
    "ok",
    ""
) 

expression how: month[OrderDate] - writes at "Arithmetic expression '(MONTH([pl_month]) >= (TODAY()-30))' does not have valid input types"

[WillowMobileSys]

MONTH() is function that returns just the number of the month.  You cannot compare that to a date (TODAY() - 30).  You would need to apply the MONTH() to TODAY() as well.  The below expression is valid but I am not sure it will get you what you are looking for.

 (MONTH([pl_month]) >= MONTH(TODAY()-30))

If you only want THIS Month's orders - not the last 30 days - then expression simply needs to be: 

IF( IN("Flowers", SELECT(Orders[goodsindex], 
              AND([clientUID] = [_THISROW].[clientUID],
                   month([OrderDate]) = MONTH(TODAY()) ))),
    "ok",
    ""
) 

[antimixer]

mm.. but this expression shows all orders include orders from last all years when month number = month(now).. But I need only this month orders. I tryed concatenation above 🙂

[WillowMobileSys · Accepted solution]

Ah yes you are right.  A check for year is needed.  That would look like this:

IF( IN("Flowers", SELECT(Orders[goodsindex], 
              AND([clientUID] = [_THISROW].[clientUID],
                   month([OrderDate]) = MONTH(TODAY()),
                   YEAR([OrderDate]) = YEAR(TODAY()) ))),
    "ok",
    ""
) 

[antimixer]

There is a problem here.

IF( IN("Flowers", SELECT(Orders[goodsindex], 
                         AND([clientUID] = [_THISROW].[clientUID],
                             [OrderDate] >= TODAY() - 90))),
    "ok",
    ""
) 

Orders will be shown not for 3 months, but for 90 days. That is, if today is 01/15/2022 And if we make -90, then we will not receive orders for the first half-month from 01/01/2022 to 10/15/2021. And so order has another Year too. Can you explane me how to solve it?

[bonameblisto]

@WillowMobileSys answered your main question. Use the search on 'Community Q&A' and you can find the answer to this on