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

AppSheet AppSheet Q&A
, ,
1

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

2

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

1 Like
3

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

4

Nadim:

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”

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

1 Like
5

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”

6

Any help please?

7

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:

[FAQ: FILTER(), LOOKUP(), MAXROW(), MINROW(), REF_ROWS(), and SELECT()](https://community.appsheet.com/t/faq-filter-lookup-maxrow-minrow-ref-rows-and-select/24216/15) Tips & Tricks ?

How do I get the last row of this thing only? MAXROW( “My Table”, “_ROWNUMBER”, ([_THISROW].[Thing] = [Thing]) ) Replace My Table with the name of the table whose row you want; and Thing with the name of the column containing a value that identifies the thing you want (e.g., Order ID).

3 Likes
8

Thank you so much, this is exactly what I need

1 Like