Suggested Values Unique

AppSheet AppSheet Q&A
1

Hello,
I’d like to assign the ability to select from a list to a text column, and everything works fine, as long as I set “Suggested values.” The problem is that I want to set up a list of users from the same column in the table, sorted by the value of the week the row is associated with, but without duplicates. I’ve tried Orderby and Unique, Orderby and Select, but I can’t. Can someone please provide me with an example?

2

I’m not clear on what you need. Maybe an example?

1 Like
3

Table A has a Users column that’s positioned as needed… I’d like to retrieve the names in the column, sorting them by the most recent week and avoiding duplicate names…
Can you give me a clear example?

(I’ll wait outside of this post, I’m having some trouble interacting with this site)

4

What does “sorting them by the most recent week” mean? You seem to be incorrectly assuming I have an idea what your data looks like.

3 Likes
5

I’m sending you a screenshot of the result I’d like…
The section on the left shows how the data is entered into the table. The result, just the user names, must be sorted and consolidated as shown in the right column…

Image
Image1754×1240 215 KB

1 Like
6

It’s a two-part process: you’ll need a virtual column to get the most-recent-date row for each individual user in most-recent-date order, then you can get the user names from that list. The expression to get the rows is heavy, so you’ll only want to compute it when needed: in form views. I’ll call this virtual column Users by Week. Its App formula might be something like this (replace table with the appropriate table name):

IFS(
  ("Form" = CONTEXT("ViewTypw")),
    ORDERBY(
      FILTER(
        "table",
        ISBANK(
          FILTER(
            "table",
            AND(
              ([_THISROW-1].[Utenti] = [Utenti]),
              ([_THISROW-1].[Settimana] < [Settimana])
            )
          )
        )
      ),
      [Settimana],
        TRUE
    )
)

Your Suggested values expression could then get the individual user names with:

[Users by Week][Utenti]
1 Like
7

First, thanks for your help. I tested it (aside from a few lexical errors that I’d suggest the community edit), and it works…

What if the list of Names were retrieved from a list of users in another table called User_Master_Data? Can they still be retrieved and sorted by where they were in the last week to which they were assigned?

PS. With this new site, is it also possible to vote for the suggestion?

8

Yes.

9

How can I filter with names from the other table list?

10

You would use your knowledge of the structure of the other table to adapt the expression I gave earlier to incorporate the names of the other table.

11

The only thing I wanted to understand is this: do I have to insert the other table in the Filter?

PS. With this new site, is it also possible to vote for the suggestion?

12

I don’t know because I don’t know how the other table is structured. I’m done here.

13

I’ll try…

I still suggest editing the example given due to its incorrect spelling…

If you tell me how I can rate the suggestion, I’ll do so…

Thanks…

14

Once the suggestion in the spelling errors is updated, I’ll close this post…

However, it’s still a correct code if you need to search for and sort data in the same table… IF THE USER NAMES ARE IN ANOTHER TABLE, THIS EXAMPLE WILL NOT WORK, EVEN IF YOU TRY TO MODIFY IT TO ACCORD WITH THE STRUCTURE OF THE OTHER TABLE, WHICH ONLY NEEDS TO SEARCH FOR THE COLUMN WITH THE USER NAMES, BUT SORTING THEM SEQUENTIALLY ACCORDING TO THE OTHER TABLE, WHICH HAS USERS ENTERED BY WEEK…

I’ve already found another solution, which has just one difficulty, which will lead me to open another post because it’s a different topic…

Thanks for your attention…