Tricks for referencing multiple MySQL records in a...

RedVox · 03-16-2023 12:16 PM

[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!

Marc_Dillon

I use MySQL with Appsheet practically every day. This post seems to be a mix of terrible information combined with complete nonsense.

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.

Creating a record on another table with enumlist selection is a great way to accomplish this. You can also do it the way you first described. You can run a "flagging" action across the selected rows to set a lookup-able value into some column. Then run a second action on a single record that actually triggers the bot which gathers all of the flagged records.

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!

Appsheet stores lists of values as plain text, separated by a delimiter. You can use a large enough varchar, or text, as your MySQL column type. I can't imagine what difficulty you were facing here.

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.

This paragraph seems to make zero sense at all. Why can't you store your new table in MySQL? By "Appsheet database" are you referring to the new database feature? "don't expect to find your MySQL table available as the referenced table"...huh? All tables loaded into the app should be available. "Just make the base type for the list numbers in the database" I don't even know what that sentence means.

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]), ","

Appsheet database has no concept of MySQL? What do you mean by this? What sort of concept do you expect it to have? How does this lead to using suggested values? members[id] should indeed work perfectly fine for suggested values or valid_if. A list of lists? That is certainly a common issue when trying to refer to an enumlist across an entire table, but not sure how that fits in here. Single ref values in Appsheet certainly are NOT lists.

You seem to be having some sort of issue here. I suggest maybe you should post your issue as a question, with all of the details that you've left out here, because I'm sure there's a solution that you're not seeing.

RedVox

@Marc_Dillon wrote:
You can run a "flagging" action across the selected rows to set a lookup-able value into some column. Then run a second action on a single record that actually triggers the bot which gathers all of the flagged records.

All attempts I made to do this failed because I could not get a second action to happen that didn't happen once per each flagged record. How would the second action happen (without requiring a second action by the user) that would only happen once and not once per flagged record?

@Marc_Dillon wrote:
Appsheet stores lists of values as plain text, separated by a delimiter. You can use a large enough varchar, or text, as your MySQL column type. I can't imagine what difficulty you were facing here.

Yes, I totally expected that AppSheet would not care whether it was a varchar or text field, but I ran into errors every time I tried that. Maybe it's the version of MySQL I'm using (5.7) that created the issue??

@Marc_Dillon wrote:
By "Appsheet database" are you referring to the new database feature?

Yes. And and when you are defining columns within the AppSheet database, only other tables within that database are available as possible referencing tables.

@Marc_Dillon wrote:
Appsheet database has no concept of MySQL? What do you mean by this?

As above, when you are defining columns within the AppSheet database, it has no awareness of other databases associated with the project.

@Marc_Dillon wrote:
members[id] should indeed work perfectly fine for suggested values

I agree, it should. However, it does not. Again, if it's working for you with MySQL, that's interesting. Perhaps my problem is related to the version version of MySQL that I'm using. (It's limited by my ISP, btw. I could change it but that's a lot of work and I've found a workaround.)

@Marc_Dillon wrote:
I suggest maybe you should post your issue as a question, with all of the details that you've left out here, because I'm sure there's a solution that you're not seeing.

I posted many questions with related subsets of this issue on my journey towards this solution. When I asked How can a batch action lead to single file send? the only response I got suggested a solution that did not use flagging. In fact, looking back, I think I got similar good advice earlier from @dbaum in a different post. I also got some great help from @dbaum with suggested values for an enumlist when I asked about that. Overall, I spent an embarrassing amount of time on what I thought was a very simple problem.

I created this post mostly so that I would not ever have to go through this absurd journey again. I'm glad to hear that some other MySQL users might not have some of these problems. As time permits, I will do some testing with newer MySQL hosts and post my findings.

I look forward to reading your expansion on how a single action can directly follow an action that is taken on set of flagged records (without the user pressing second button, of course). As referenced above, others have steered me away from this approach, so I'm very intrigued.

Marc_Dillon

within the AppSheet database

Wait... so you don't mean in the app editor? I thought all of these were things you were setting up in the app? Are you trying to set these configs up in the appsheet-db itself(is that even a thing, I don't really use it)? Of course appsheet-db itself wouldn't have any idea about any tables from other sources, it has no connection to them.

You didn't answer why you can't store the new table in MySQL. If you already have a MySQL database, why are you using appsheet-db at all? I think that would be a downgrade, especially as it's still in beta/preview.

How would the second action happen (without requiring a second action by the user) that would only happen once and not once per flagged record?

The user would have to click a second action button on a single record. I typically present this as an inline action on the same view that the multi-selection happened, and only present the action on a single one of the records so as not to confuse the user.

RedVox

@Marc_Dillon wrote:
If you already have a MySQL database, why are you using appsheet-db at all?

Well, I think I made it clear that I was having unexpected results with MySQL so I switched away from it to figure out what the problem was. Coincidentally, since you seemed so thoroughly surprised with members[id] not working as suggested values, I tried it again. Different DB host (though still MySQL 5.7), fresh app, new tables and, yet, same result. When I try to set a field (varchar, 300chars) in AppSheet to be an enumlist with a base type of ref, it doesn't let me use something like members[id] for suggested values as shown here:

So yeah, in two apps with two different MySQL databases, the thing that apparently works very easily for you is still an absolute non-starter for me. Wonder what the difference is?

RedVox

Okay, @Marc_Dillon , I did another test with a MySQL v10 db and the results were identical. So, I don't have a clue what you are doing that lets you use the simple table[id] for suggest values in a mysql-sourced, enumlist with ref base column. ( Unless, of course, you are just marking the field as long-text in Appsheet, and just letting the user select one of the displayed index values. But that would be apples and oranges. )

RedVox

@Marc_Dillon wrote:
The user would have to click a second action button on a single record.

That's hilarious!

RedVox

Pithy Summary:

If you're trying to make a single action happen based on flagged (selected) records, such as create a summary record, send an email with info from these selected records, you can't do so without requiring additional steps from the user.
If you're using MySQL and you are trying to configure a field that you will configure as an enumlist of refs, your suggested values for the list cannot be a simple list of ref rows. Instead, use this formula: SPLIT(ORDERBY(table[id], [label]), ",")

RedVox

@Marc_Dillon wrote:
If you already have a MySQL database, why are you using appsheet-db at all?

Personally, I prefer to keep tables that are only intended to aid appsheet functionality (eg store the list of rows the user just selected, or make this bot fire now) away from my DB. They are doing appsheet-specific things so, yeah, I prefer to store them in appsheet.

Marc_Dillon

What is the column type of members[id] ?

RedVox

In AppSheet, it’s an enumlist with a ref base. In MySQL, it’s a varchar or text.

Marc_Dillon

The [id] column in members table is an EnumList-Ref? And you're trying to use members[id] in the suggested values of another EnumList-Ref column on another table?

RedVox

Sorry, the column type for members[id] is number and it’s the key for the table.

Marc_Dillon

Very interesting....

I just created a brand new blank test app here. Apparently a Number type column doesn't mix with Suggested Values on an EnumList column. I get the same error "type of suggested values does not match column type". Switching any of the 3 things seems to fix the situation:

Change from EnumList to Enum
change from Number to Text
or change from Suggested Values to Valid_If.

Actually I think this might be an issue that I've seen in several other threads that I never quite figured out to my satisfaction. Very interesting indeed.

To be clear, none of this has anything to do with MySQL, it is entirely data source independent, just as the vast majority of things in the app are.

This might be a bug. I'll submit it to support here in a bit actually.

Several other column types don't work as well, in place of the Number. Though some of that may be due to the fact that Appsheet doesn't allow key values to be certain types (like LongText, Percent, etc.).

Some screenshots of the situation:

RedVox

Whew! We are getting somewhere! I can confirm that changing members[id] to text fixes the problem (though, it would create many more problems for me, so it's a nice indicator but not a practical fix.) Similarly, changing the field to enum may cure the problem, but it doesn't meet the business need of enabling multiple selections, so more interesting than practical. I wasn't able to replicate your suggestion about moving the list from suggested values to valid if.

It would be great to see this fixed, but in the meantime, I think the only solution that actually matches the design intention is to use the approach originally suggested by @dbaum of using a SPLIT() function in the suggested values column to produce a list of paired ref values and labels.

Marc_Dillon

I wasn't able to replicate your suggestion about moving the list from suggested values to valid if.

You got the same error when removing members[id[ from Suggested Values, and putting it in Valid_if?

@RedVox wrote:

SPLIT(ORDERBY(table[id], [label]), ",")

I highly suggest using SPLIT( TEXT( ... ) , " , " ) instead. Notice the space-comma-space delimiter instead of the single comma. Yes, I know what the help article says, but I've seen instances where that breaks.

Tricks for referencing multiple MySQL records in an action