Action in database MYSQL

[Msant77s]

Hello good mornig for everyone.

I contact you to ask you a question about an application that I am working on for the management of tools in a warehouse.

I'm having problems configuring my database in MYSQL, I used to use this app with Google sheet before, but due to synchronization time problems I'm wanting to migrate to a database with MYSQL.

The problem I'm having is between two tables:

Table 1: "Prestamosmultiples" whose function is to collect through a form the tools that a company worker borrows from the deposit. In this table the warehouse manager takes the order of the tools that the worker wants to withdraw through an enumlist column, where he can select multiple tools.

Then, when saving the form, a series of actions are unleashed that I use to create a unique record for each selected tool in another table, table2 "Prestamos".

Actions:



This operation in the application with Google sheet works correctly, but in the current application with MYSQL it does not work, it is only sending a new row in the table. We provide the first tool code and it does not create the following ones, I am sure that this is the case due to a problem in the counter action "Action 2" and the way I have configured the database in MYSQL, but I can't find what the error could be.
I leave the configuration of my tables in MYSQL:
Table 1: "Prestamosmultiples"

Table2: "Prestamos"

I hope you can help me, I already tried to migrate this database to an internal database in Apsheet Database but it was impossible, many functions are not compatible with my application and it was very difficult for me to migrate this database.
Thank you very much for your time.

[AleksiAlkio]

Have you checked what happens if you trigger the action after the row is created? Then you could have an idea from where it's coming from as it sounds it's not triggered at all.

[Msant77s]

The action is triggered after saving the record, it creates the record row in "Prestamosmultiples" and then the action is activated, I have configured that the action is activated after saving the form. This with google sheet works correctly and generates the rows for each tool in the "Presamos" table. But in this version with external database it doesn't work.
What do you mean by test after creating the row?

[Msant77s]

You are currently creating a row with the data from this record "Multiple Loans", but it only creates a single row, when you have to create the same number of rows according to the number of tools selected, if three tools are selected, you must create three records in the "Loans" table. This works correctly with the same configuration in google sheet, but currently using MYSQL it does not work, it only creates the first record of the first enumlist tool, the others are not created, and the counter always remains at 1

[AleksiAlkio]

What I mean.. now you are triggering the action with the Event action from the form view. What happens if you manually press the same action after the row is saved into PrestamosMultiples table.

[Msant77s]

The value of the counter column increases in the row of the "multiple loans" table. The addition of the other rows to the table did not work.

[AleksiAlkio]

Now try to trigger the action #3 manually. What happens then?

[Msant77s]

When activating action 3 manually, it tells me invalid tool code, it is because it is reading the first code from the enumlist that was already sent to the "loans" table, the only record that it sends, the first... if I delete this record in the table "loans", it lets me activate and send the following tool, if I activate the action again without deleting the previous record, the error message invalid tool code returns... the problem is that for some reason this expression:

I don't know why. When sending the data to the table loans, it assumes that the enumlist is a single value and not a set of values. In google sheet it works, I don't understand why not here, it has to be something with the configuration of the Codigo_Herramienta column in the MYSQL database.

[AleksiAlkio]

So.. let me try to summarize this problem.

#1 - User creates the record in "Multiple loans" table
#2 - It has an EnumList column where the user selects multiple tools
#3 - When the record is saved, it creates the record to "Loans" table but only for the first tool, not for others
#4 - When you trigger the action #3 manually, it gives you an error saying it's an invalid tool code if the first one already exists.

Is my assumption correct?

[Msant77s]

Of course, that's how you describe it.

[AleksiAlkio]

What happens if you change the index number as a real number, like set it as "2"?

[Msant77s]

I can't change the value of index to an integer in the expression, it tells me that it is invalid. It only works if I reference the column [ToolCode[ which is enumlist.

[AleksiAlkio]

What I mean.. INDEX([Codigo_Herramienta],2)

[Msant77s]

nothing changes, it just creates a single row in the loans table with the code of the second tool in the list

[AleksiAlkio]

If you check the EnumList column and its value in the database, what it looks like?

[Msant77s]

data is saved well

[AleksiAlkio]

This sounds really weird. Have you tried to change the column type in DB to something else than longtext?

[Msant77s]

yes, try varchar(255), text and nothing

[AleksiAlkio]

What have you selected as a separator in the EnumList column? If it's blank, try to add a comma and see if that helps.

[Msant77s]

It was blank and I tried adding a comma, also a hyphen and nothing

[AleksiAlkio]

Try with space-comma-space

[Msant77s]

It doesn't work either.
Query, do you already have a reference that this type of logic works with a mysql database?
Can it be done with the appsheet database?

[Marc_Dillon]

The data source being used should make no difference here. I've certainly set up similar functionality on a MySQL-sourced app.

To me it sound like maybe your loop isn't proceeding after the first iteration. Does the [contador] value ever end up being more than 1? What behavior condition do you have on your loop repeater action (#4)?

[Msant77s]

I don't understand what you mean by behavior condition of action four, I don't have any if valif, just the configuration:
Here are the actions:

[Marc_Dillon · Accepted solution]

Behavior condition:

On action loops, you need a behavior condition on your loop repeater in order to stop the loop when appropriate. Otherwise you get an infinite loop. Yours should be something like [contador] <= COUNT( [enumlist] )

[Msant77s]

Small detail, I had forgotten to pass my condition😅.

Now it works correctly, thank you very much 🥳.
Now I have a problem and it is that it creates an additional row, that is, if I set 3 tools it creates 4 rows and that gives me errors. How can I do so that the counter does not go by 1 every time I do this operation. When ready 3 tools the counter is set to 4, when ready 4 tools it is set to 5, then it always creates a row with empty fields and this causes me an error.

[AleksiAlkio]

Btw.. I normally use Webhook for these kind of tasks as the configuration is much simpler. Though it needs Core subscription.

[Marc_Dillon]

---

@Msant77s wrote:

Now I have a problem and it is that it creates an additional row, that is, if I set 3 tools it creates 4 rows

---

Change the '<=' in the behavior condition to just '<'

[Msant77s]

perfect, it worked correctly, thank you very much

[AleksiAlkio]

Good catch Marc. So.. the definitions were not the same when using gSheet as a data source 🙂

[Msant77s]

I was not passing the same conditions, when passing my app with this new database, I forgot to set this condition of action1. My mistake 😅
Thank you very much AleksiAlkio and sorry for the time, this happened to me.

[AleksiAlkio]

No worries 👍