ORDERBY already selected records then alphabetically.

[Mals]

Is it creasy to think of sorting records in a enumlist list selector by having at the top the records previously selected then alphabetically for the others? 

Let's says I have a table with Address record. From there I have a form to add a list of related Person using a enumlist of Ref Person. This list contains 300 people. There is a column Address_list on the Person table which is updated by a bot after the person is added or removed. A Person may be linked to more than one Address

On day one I add Zacky and Mary.

On day two, I open the selector which should show Mary and Zacky at the top previously selected then all the other people sorted alphabetically. 

ORDERBY(Person[ID Person],CONTAINS([Address_list],???),[name], FALSE)

I have though of using CONTAINS but I struggle to complete the formula or my strategy is just wrong. 

[WillowMobileSys · Accepted solution]

Yes, you can do this.  AppSheet supports List operators of "+" and "-" so that you can do things like

List One + List Two   ...or...   List Three - List Four

Create two list expressions - one that gathers and orders the previously "selected" items;  and a second that gathers and orders those NOT previously selected.  Then simply "add" them together.  They will be displayed in the order you have added them.

Combine Lists 

Subtract values from list 

I hope this helps!

[Mals]

Thanks @WillowMobileSys for your input. It is good to have this new strategy in mind. 

Finally, this formula works for me 

ORDERBY(Person[ID Person],CONTAINS([Address_list],[_THISROW].[ID Address]),[name], FALSE)

I guess I though it was not working because of the Bot not having completed its job. 

[Mals]

It looks like I have inconsistent result with this approach. 

@WillowMobileSys I played with your concept and I believe I have a solution. 

I simply or complexly did...

ORDERBY(SELECT(Personne[ID Personne],CONTAINS([ID Adresse list],[_THISROW].[ID Adresse])),[Nom complet],FALSE)+ORDERBY((Personne[ID Personne]-SELECT(Personne[ID Personne],CONTAINS([ID Adresse list],[_THISROW].[ID Adresse]))),[Nom complet])

[WillowMobileSys]

---

@Mals wrote:

ORDERBY(SELECT(Personne[ID Personne],CONTAINS([ID Adresse list],[_THISROW].[ID Adresse])),[Nom complet],FALSE)+ORDERBY((Personne[ID Personne]-SELECT(Personne[ID Personne],CONTAINS([ID Adresse list],[_THISROW].[ID Adresse]))),[Nom complet])

---

The portion after the "+" is less efficient as that portion alone must build two lists to drive a third that is then tacked onto the resulting list.  For a "Person" table it is probably ok as that table likely does not grow significantly as compared to, say, a "Transactions" table.

For future reference you can simplify this expression into:   

ORDERBY(SELECT(Personne[ID Personne],CONTAINS([ID Adresse list],[_THISROW].[ID Adresse])),[Nom complet],FALSE)
+
ORDERBY(SELECT(Personne[ID Personne], NOT(CONTAINS([ID Adresse list],[_THISROW].[ID Adresse]))), [Nom complet])

Additionally, I just realized, if your [ID Adresse List] is indeed defined as a list - either VC List or EnumList - then you can further improve efficiency by using the IN() function as shown below.  CONTAINS() function is meant for finding substrings in text, is a lot less efficient and may not work effectively on some lists. 

ORDERBY(SELECT(Personne[ID Personne],IN([_THISROW].[ID Adresse], [ID Adresse list])), [Nom complet],FALSE)
+
ORDERBY(SELECT(Personne[ID Personne], NOT(IN([_THISROW].[ID Adresse], [ID Adresse list]))), [Nom complet])


I hope this helps!

[shimodabt]

Wow @WillowMobileSys ! Thanks for the code review! I am learning. 

One related thing that puzzled me...

Let's say I add a Person. Even after all the required updates have been done in the spreadsheet, the selector frequently does not update and requires a full sync (like Chrome page refresh) to show the updated list. Any idea? 

[WillowMobileSys]

---

@shimodabt wrote:

Even after all the required updates have been done in the spreadsheet

---

Are there sheet-side formulas running that make updates to the data that impacts how the dropdown gets organized?

[shimodabt]

No. The only thing is a script in a second Google sheet running an importrange() every 5 min.