Sort a list by re-occurring ref views

[ivannewman]

I'm making an app where you can create an account for a person, and then record a visit with the persons account and date of visit.

I have it set up so that when someone visits, I can go to a form view, select a date, and then select the account that visits, and then add it to the records (creating a new account when they've never visited before).

I have 2 tables set up: "Accounts" that stores all the info like Name, Number, Address, etc. ; And a "Records" table that references that name on the "Accounts" table and then creates a new row with the reference and the date. Hence, recording the visit.

MY QUESTION: I want to be able to have a deck view called "Accounts" that shows all accounts created AND organize it by the number of visits by each account. I'd like to be able to show the number of visits by each account name as well. I have the deck view set up but I can't for the life of me figure out how to organize the view by the number of visits.

I'm very new to Appsheet and I have little to 0 coding experience. If there's any code I need to put in please tell me where to put it. Thanks

[dbaum · Accepted solution]

---

@ivannewman wrote:

I want this sorted alphabetically:

---

Wherever the list of values is defined (e.g., maybe in the column's Valid if property) include a SORT or ORDERBY function to yield the order you want.

---

@ivannewman wrote:

I want this sorted by number of visits. So the account with the most visits appears on top:

---

Control row sort order - AppSheet Help 

[dbaum]

Maybe Control grouping - AppSheet Help helps.

If not, I suggest elaborate by clarifying which AppSheet view type you mean by "list view" and maybe illustrate with your current view and even a mock-up of what you're seeking to create.

[ivannewman]

I mean a "Deck" view

[ivannewman]

This is the form where you enter someone's name to find their account to record a visit under their account name

I want this sorted alphabetically:

This is the "Accounts" Deck view

I want this sorted by number of visits. So the account with the most visits appears on top:

Thanks

[dbaum · Accepted solution]

---

@ivannewman wrote:

I want this sorted alphabetically:

---

Wherever the list of values is defined (e.g., maybe in the column's Valid if property) include a SORT or ORDERBY function to yield the order you want.

---

@ivannewman wrote:

I want this sorted by number of visits. So the account with the most visits appears on top:

---

Control row sort order - AppSheet Help 

[ivannewman]

Hi, I followed the steps and seemed to get the sorting working, however. Completing my form no longer adds data to the table and each name has a warning sign by it:

Any ideas? Thanks

[dbaum]

---

@ivannewman wrote:

I followed the steps and seemed to get the sorting working

---

Likely this is a Ref type column and you created a Valid if expression that returns a list of values that are not from the key column of the referenced table. Change the expression to return values from the key column (you may need to use ORDERBY instead of SORT), and the table's label column values will still display for the user.

[ivannewman]

Also, I have the deck view set to sort by the ref column and then by name if the ref column value is the same. However, it doesn't do that. It puts the highest visits on top but doesn't organize the entries with the same amount of visits at all:

[dbaum]

---

@ivannewman wrote:

doesn't organize the entries with the same amount of visits at all

---

If my original reply's suggestion to use the view's Group by property doesn't meet your need, then I don't understand what you're trying to accomplish.

[ivannewman]

The group by property groups all the accounts by number of visits. I just want all the accounts sorted from most to least visits, not split into groups

[Steve]

---

@ivannewman wrote:

I followed the steps and seemed to get the sorting working, however. Completing my form no longer adds data to the table and each name has a warning sign by it:

---

Use ORDERBY() rather than SORT().

[ivannewman]

Thank you, I was able to figure out how to use ORDERBY()

[Steve]

---

@ivannewman wrote:

I have the deck view set to sort by the ref column and then by name if the ref column value is the same. However, it doesn't do that. It puts the highest visits on top but doesn't organize the entries with the same amount of visits at all:

---

It looks correct to me. In what way is it wrong?

[ivannewman]

These entries have the same number of visits, if organized by name ascending, shouldn't it be in the order of "Test 1, Test 7, Test 8"?

[ivannewman]

Also, after adding some visits under "Test 5" I see that it's no longer sorting it by visits:

Is there something I'm missing here? Is there some sort of function or code I need to write that counts the amount of times each referenced name appears in the table and displays that amount? If so, please elaborate. Thank you

[dbaum · Accepted solution]

The value of each row's [Related peopleRecords] column is a list of its related records' IDs--not a count of those records. In a table view, that list is summarized as a count for display. Try instead indeed creating an additional column that is truly the count:

COUNT([Related peopleRecords])

[ivannewman]

Thank you! The sorting is working fine now! I guess I need to learn some more about virtual columns