Tricks for referencing multiple MySQL records in an action

[RedVox]

[EDIT: You might want to skip this lengthy thread and go straight to the pithy summary below.]

This probably doesn't concern too many users, but if you use MySQL, you might want to know this....  I wanted to make a form where a user selects a number of table rows and then the details of those rows are sent in a CSV file.  Sounds simple enough, but here are the problems I encountered - most of which relate to MySQL. 

Forget trying to use a batch action.  (This one isn't specific to MySQL.) Even though it seems like selecting records directly on the list of records would be the most natural starting point, forget about it.  It won't work because any actions you take are going to occur once per record and there's no option to simply take that list of selected rows and refer to the list in a single action.  Multiple selections of items in the base table can only mean multiple actions.  Instead, you are going to to need a new table with an emumlist of refs to select the records you want to act on.

You can't store the new table in MySQL.  As far as I can tell, there's no json, text, varchar or other type of field in MySQL that AppSheet will accept for storing a list of ref values.  There be dragons!

You won't find your MySQL tables listed in AppSheet Database.  Since you can't store this new table in MySQL, the AppSheet database seems like a natural home for your "CSV Requests" table.  But when you go to create an enumlist field of refs, don't expect to find your MySQL tables available as the referenced table.  Don't let that stop you. Just make the base type for the list numbers in the database.  

There's a trick to making your MySQL index fields work as suggested values.  Since your AppSheet database has no concept of your MySQL database, you're going to need to provide suggested values.  While normally,   something like "members[id]" would provide the needed list for your suggested ref values, that won't work for you.  You'll need to provide a list of lists.  I believe this is because each ref value in AppSheet is itself actually a  list, whereas in MySQL, the id field is just a value. The following formula worked for me : SPLIT(ORDERBY(members[id], [name]), ",")

Cheers!