Hi everyone,
I’m new to appsheet and I was wondering if you could help me please. I have two tabs in my app, the one is where you can record how a session has been, and the other tab is to evaluate those sessions. In the first session I have an EnumList like the one shown in the picture. People can choose multiple answers from the EnumList. For the other tab, I was wondering if there is anyway to automatically have what the most common answer was from the previous tab (so for example if they selected ‘drugs - information’ 5 times and ‘smoking - guidance’ 3 times, I’d like to show ‘drugs-information’ was the most selected topic that month.
Many thanks!!
Hello, in my opinion, you have to create a place where you going to do the counting,
virtual columns for each item from enumList with the formula:
COUNT(
Select( TableName[ColumnName ] , CONTAINS( [ColumnName ] , “drugs - information” ) )
)
Yes, you can do that but I think you’ll want to do a little work to make it happen.
I would recommend creating a “Rig Topics” table to list all of your EnumList choices. In this table you would have the:
- Topic ID
- Category
- Topic
- Label
- any other details you wish to track
…and then, in the app, add a Virtual Column named “Selected Count” that tracks the number of times a topic has been selected. You want this as a Virtual Column so that with any new entries or updates made the count is automatically adjusted on each Sync. Make sure to also check the Label property - selecting the Label column.
In the app, where you have the current EnumList column, you would edit the column and change the Base Type from Text to Ref. Set the “Referenced table name” to your new table “Rig Topics” and then set the Valid_If expression to - Rig Topic[Topic ID] - this will show all of the table items in the column dropdown list just as before.
(NOTE: separating your topics by category will give you more flexibility later - How many “Illegal Drug” choices were made? - You simply need to total by the category.)
Once you have the table established column updated as described above, you can then create expressions to ORDERBY() the list by count and then also choose the top X items using the TOP() function. See below for an example expression.
Example Table might be:
| Topic ID |
Category |
Topic |
Label |
| abcd1234 |
Illegal Drugs |
Raised by YP |
Illegal Drugs - Raised by YP |
| bcde2345 |
Illegal Drugs |
Information |
Illegal Drugs - Information |
| cdef3456 |
Illegal Drugs |
Guidance |
Illegal Drugs - Guidance |
| defg4567 |
Legal Highs |
Raised by YP |
Legal Highs - Raised by YP |
| efgh5678 |
Legal Highs |
Information |
Legal Highs - Information |
| fghi6789 |
Legal Highs |
Guidance |
Legal Highs - Guidance |
| ghij7890 |
Smoking |
… |
… |
Example expression to pick the top 5 used topics:
TOP(ORDERBY(Rig Topics[Topic ID], [Selected Count], TRUE), 5)
ORDERBY() Function Details
TOP() Function Details
1 Like
Hi,
Thanks a lot for this. Just some questions:
- Why do I need the TopicID? and do I have to seperate the category, topic and label? I want to know how frequently the ‘label’ was selected, not the rest..
- when you mention to create a virtual column, what formula should I add there? It doesn’t let me continue without adding a formula..
Thanks a lot for your help!
Hi,
Thank you for your reply. I could try that, but the ‘drugs-information’ was just an example, I’m guessing I need to put something else there so it works everytime?
Hi @angelevripidou !
TopicID is just a row key. Every table must have a key column. Best practice is to usea dedicated column for the row key. That is what TopicID is.
No, you don’t NEED to separate the Label into Category and Topic. But experience has taught me that there will eventually be a need to use that data in its separated form one way or another. My normal approach is to keep the data separated and then provide additional combined versions as needed - such as for the display Label.
Sorry, I meant to provide an example Virtual Column formula as well. It would need to be something like:
COUNT(SELECT(Monthly Evaluation Questions[key column], IN([Label],[What were this groups most discussed Rig topics?])))
Thank you for your help!
I get this error when trying to create the virtual column
The text “key column” was just a place holder - which I should have explained.
You want to replace this text with the name of the column that is marked as the key column in that table - Monthly Evaluation Questions. This was deduced from the screenshot above showing the list of EnumList values.
1 Like
