How Order Lookup

AppSheet AppSheet Q&A
1

Hi,
I found a solution to retrieve user names from a table and am trying to sort them individually based on their insertion in another table based on the Week column…
I still have the problem of retrieving the last row of the last Lookup…

I’ll provide the formula I wrote:

ORDERBY
(
  ORDERBY
  (
    FILTER
    (
      table_list_users,
      [section_is] = TRUE
    ),
    [users]
  ),
  IF
  (
    ISBLANK
    (
      LOOKUP([users], table_section, "column_names","week")
    ),
    DATE("1/1/1900"),
    LOOKUP([users], table_section, "column_names","week")
  ),
  FALSE
)

Is this possible? Could there be another solution?

1 Like
2

The inner ORDERBY() seems unnecessary. Whatever order the filtered list ends up in, it is re-ordered by the outer ORDERBY. I don’t think the inner ORDERBY is needed.

Performing LOOKUP()’s as part of the ordering criteria is VERY inefficient. Instead I would suggest to pull that lookup info into the rows of the table_list_users and I would add table column with an App Formula with your LOOKUP() and not a Virtual Column. You do not need to use DATE(“1/1/1900”). You can just leave it blank.

Lastly, if you need to order by multiple criteria you can do that in a single ORDERBY() expression. Please refer to this article for the syntax - ORDERBY()

Applying the above you should be able to simplify the expression and make it much more efficient.

I hope this helps!

3 Likes
3

Hey,

Hope you’re keeping well.

In AppSheet, nested ORDERBY() calls work, but retrieving the “last row” from a lookup chain often needs an explicit MAX() or TOP() rather than relying on sort order alone. Instead of sorting twice, you can filter and join in one pass, then sort by the week value using IF(ISBLANK(…), DATE(“1900-01-01”), …) as you did, and finally wrap the result with INDEX(…, COUNT(…)) to get the last entry. This avoids multiple lookups per row and is generally faster.

For example:
```
INDEX(

ORDERBY(

FILTER(table_list_users, [section_is] = TRUE),
    IF(ISBLANK(LOOKUP([users], table_section, "column_names", "week")),
       DATE("1900-01-01"),
       LOOKUP([users], table_section, "column_names", "week")
    ),
    FALSE
  ),
  COUNT(FILTER(table_list_users, [section_is] = TRUE))
)
```  
This returns the last user based on week ordering while keeping the logic in a single sort.

Thanks and regards,  
Taz
5

You’re using LOOKUP() incorrectly.

1 Like
6

Your problem is very poorly described. It seems you want to get a list of user names in the order they’re recorded in a specific table for a specific week. You haven’t clearly indicated what column of the table containing all user names contains the user name. You haven’t indicated what columns of the other table contain the user name or how to identify which rows of the table match the target week.

The approach to the problem as you’ve described it is to generate the list of user names from the target week, generate the list of all user names, then use INTERSECT() to get only those names that occur in both lists:

INTERSECT(
  SELECT(
    weekly-data[user-name],
    ...target-week...
  ),
  SELECT(
    all-users[name],
    ...eligible-users...
  )
)

The list returned by INTERSECT() will be in the order of the first of the two lists. Note that ORDERBY() is not used at all.