Problem with move to MySQL - workflow email

[Ethan_Hildebran]

@Aleksi @Phil I’m having trouble with a workflow now that I’m moving my app to a MySQL database. I have an ENUMLIST column that I am using in a workflow to send an email. If ENUMLIST options ‘1’, ‘2’, and/or ‘4’ appear in the list then send an email. If ONLY ENUMLIST option ‘3’ appears in the list then do not send an email. The workflow condition I’m using is (simplified)…

AND([TYPE]=“A”,
OR(IN(“item 1”, [ENUMLIST]), IN(“item 2”, [ENUMLIST]), IN(“item 4”, [ENUMLIST])))

The above condition works with my app on a GoogleSheet Db with any combination of items 1, 2, and/or 4 in the ENUMLIST.

However, with my app on a MySQL Db, I can send my workflow email only if a single item (1, 2, or 4) appears, not if multiple items appear. Help!

[WillowMobileSys]

I know the AppSheet guys are busy and since there hasn’t been a reponse, maybe I can help.

What is the datatype of the EnumList column in the database?

Have you confirmed that the multiple choice are getting correctly saved in the database as a comma separated list?

AppSheet doesn’t care which datasource you are using so the logic should not behave any differently.

Since it is the datasource that has changed then there must be something happening with the saving and retrieving of the data and this is most likely due to a usage of the wrong data type in the database.

[Ethan_Hildebran]

@WillowMobileSystems thanks for the reply! The MySQL data type for this column is ‘varchar(150)’. Selections do appear to hit the Db as comma separated values just as I’m used to seeing in GoogleSheets. My reading suggests that MySQL doesn’t have an ENUMLIST data type (?) and instead sees comma separated lists as a long string, but I’m new to MySQL and still learning.

I could try to change the MySQL data type for this column, but to what? I could also try to change the AppSheet column type, but then I would lose the ENUMLIST functionality that I need. Any suggestions? Thanks again.

[WillowMobileSys]

You are right, there is not a specific type in MySQL of EnumList. The type varchar(150) should suffice.

In AppSheet, after a save, when you review the EnumList column, is it getting back the proper comma separated list?

If that looks good then I’m baffled. If the data is being saved and retrieved in the same way AND you have made no changes to the Workflow trigger criteria then it should work as before.

Oh, one thing that just occurred to me is to check to see if any errors are reported when you try to send an email with multiple options selected. You view the Log by expanding the Workflow rule and clicking on the Log button. Find the reported for this Workflow rule. If its highlighted in red then there was an error. Click the binoculars to drill into it and see what error message was reported. If no errors it’ll be highlighted in green.

If all looks good so far then let’s continue on.

Can you post an example of the EnumList column from AppSheet?
And can you post your Workflow criteria thats used to trigger it?

[Ethan_Hildebran]

Hi @WillowMobileSystems thanks for your reply, apologies for my delayed response. After saving, Enumlist appears to show an accurate comma separated list. However, when I allow changes to the table (deployed app will be add only) the Enumlist column doesn’t pull the selections into the field when editing. It seems to me that the issue is due to MySQL not supporting comma separated lists.

Hopefully, the AppSheet crew are a little less busy now. I wonder if @Aleksi, @Phil, @Steve, @nico have any suggestions?

[WillowMobileSys]

I don’t believe that there is anything special you need to do in MySQL for an EnumList. A comma separated list is just text. I could be wrong but I do have an app connected to a MySQL db and I’ve not had the issues you are describing.

I wonder, did you have any formulas in the Google sheet that could have been introduced to the column? If you did, AppSheet might be trying to apply the formula still and you would want to remove it. See the image below for the Spreadsheet Formula property location.

Otherwise, I hope one of the other guys will have an answer for you soon!

[WillowMobileSys]

@Ethan_Hildebrand

So, I just played with this, I actually did not have an EnumList column in my app so I added one. I called it Item Choices and defined it as varchar (150). I was able to add a record, select multiple items from the list, save it, Edit it, changed it from 3 to 7 items. I also tried closing the app and re-opening it after both Saves. I am not seeing an issue at all.

Item Choices in App after 2nd Save

Item Choices in MySQL db after 2nd SAVE - the … means more items

Dropdown EnumList showing chosen items

[Ethan_Hildebran]

interesting @WillowMobileSystems… this is not happening in my app. When I reopen a submission to edit/update there aren’t any selections showing. What delimiter are you using (",", or ", ")?

What is your data type for this column in MySQL?

[WillowMobileSys]

I didn’t assign a list separator so it just uses the default which is comma. Also note that my Base type is “Text”. What is yours?

Datatype is defined as VARCHAR(150)

Column definition - note the Base Type

SQL column definition

[Ethan_Hildebran]

The only difference I see is that I entered a list of enumlist values, rather than use a vaild if statement to produce the list. I’m using VARCHAR(150) just like you.

[Ethan_Hildebran]

I’m under a severe time limit, so I’ve actually reconfigured my app… rather than use a single ENUMLIST column with four options, I’m now going to use four separate columns. I’ve set the new columns as ‘COLOR’ with a single option ‘Green’. This sets up simple checklist functionality in my app. If any of the four options apply, my user will tap to check off the option. This will allow me to run workflow logic on each column separately. This solution is not as clean as my original design using an ENUMLIST column, but it has proven out as a viable option. I hope to pursue this inquiry further and move back to an ENUMLIST column when possible.

[WillowMobileSys]

For what its worth, I switched the column to have hard coded values and see no difference in behavior.

I’m wondering if some behind-the-scenes column attribute is lingering from when you switched from the sheet to the db. Maybe simply removing the column, regenerate and then adding it back in, regenerate could have corrected it??

At this point, It might be worthwhile to send to it directly to support@appsheet.com and refer them to this post.

Values in SQL Database

[Ethan_Hildebran]

@WillowMobileSystems I took your advice… I sent an email to support@appsheet.com. I, then, removed/readded/regenerated the column/table. Good news is that I got an almost immediate response from @Joshua_Aldrich to my email (thanks, Josh!). And, more good news, your advice to rebuild the column worked and I am now getting the workflow functionality. Happy, yet frustrated, yet relieved to be moving forward. Thanks for the help. I’ll update this thread if I learn anything about root cause.