Solved: Re: Row filter condition in Slice to get the last ...

Nadim1 · 10-22-2021 06:19 AM

Hi,

I’m new here and, and just finished my first App with one unsolved issue, and need your help please.

I have 3 tables in my App and each has many columns, but I’ll mention the required columns only
1st, Salesmen, (Name, Email)
2nd, Clients, (Client ID, Salesman, Status)
3rd, Actions, (Client ID)

What I need is a Slice to show the last Action of each of my clients only that don’t have Status “Not Interested” or “Ordered”

Ex: Each client may has more than one action, sorted by date, so I need to show the last action only, for those clients that assigned to me and don’t have Status “Not Interested” or “Ordered”

I used this expression and it worked but I need the last row only if all condition are true
1st, (Check my Clients) LOOKUP(USEREMAIL(), “Partners”, “Email”, “Name”)=LOOKUP([Client ID], “Clients”, “Client ID”, “Salesman”)
2nd, (Same 1st, but only if their Status are not “Not Interested” or “Ordered”)
3rd, (Show me the Last Row of the clients that meets the above conditions)

Thanks in advanced

Steve

Try this:

AND(
  NOT(IN([Status], LIST("Not Interested", "Ordered"))),
  (USEREMAIL() = [Client ID].[Salesman].[Email]),
  (
    [_ROWNUMBER]
    = MAX(
      SELECT(
        Actions[_ROWNUMBER],
        AND(
          NOT(IN([Status], LIST("Not Interested", "Ordered"))),
          ([_THISROW].[Client ID] = [Client ID])
        )
      )
    )
  )
)

See also:

View solution in original post

1minManager

You’ll Need to use MAX() to find either the highest [_RowNumber] or [DateTime]

Nadim1

Thank you Simon, but my problem is that I’m not able to combine all the expressions together, I tried several ways but still didn’t get the correct one

1minManager

Do you need to see this in the Salesman record or the Client record?

Nadim1

In my Action table, I have added many actions for each client, so I need a slice to show me only the last action of this client just incase it match the following conditions
1- If this is my client LOOKUP(USEREMAIL(), “Partners”, “Email”, “Name”)=LOOKUP([Client ID], “Clients”, “Client ID”, “Salesman”)
2-If the Status of this client is not “Not Interested” or not “Ordered”

Nadim1

Any help please?

Steve

Try this:

AND(
  NOT(IN([Status], LIST("Not Interested", "Ordered"))),
  (USEREMAIL() = [Client ID].[Salesman].[Email]),
  (
    [_ROWNUMBER]
    = MAX(
      SELECT(
        Actions[_ROWNUMBER],
        AND(
          NOT(IN([Status], LIST("Not Interested", "Ordered"))),
          ([_THISROW].[Client ID] = [Client ID])
        )
      )
    )
  )
)

Row filter condition in Slice to get the last Action Row of each Client