Automation problems, row created> create row in table2, empty value passed on

[Reftek85]

I will try to keep it simple,backend is a MYSQL database,
example BD
table1: clients
column: id INT(11) (autoincrement) primarykey
table2: file
column: id INT(11) (autoincrement) primarykey
column: client_id INT(11)

I am trying to do this:

trigger: row created
table: clients

create row
table: file1
client_id: clients.id
Row ID *: {{ trigger.id }}

I tried a lot of things but I only get three result, the row gets created with an empty field at destination, error invalid inputs, or [object Object] red popup error

I tried to pass the value a lot of way after looking online but I dont understand much of what I find
I tried things like this
{{ steps.1.rows.0.id }}
{{ trigger.row.id }}

also, I would like those new row in the other table to have the budibase relationship working in case I want to use it that way.
I created a relationship in Budibase clients(ID)> one to many file > file1(client_id)
will I need to create an update column manually in the same automation with another builtin like ._id or will the relationship be created automaticcaly while the previous automation does its thing?

i was thinking this logic: update row on table file1 WHERE client_id = {{ trigger.id }}
the budibase relationship column in file1 is name "clients"
so update value of column clients with "{{ steps.1.rows.0._id }}

I tried this as well but values are still empty in the created rows....

Please help I am lost here and can't find any real complete information on how automation works.
Thank you to anyone that can help!
Reftek85

[Reftek85]

after further investigation it might be related to data format.
I stumbled on this thread which is mostly the exact same issue I face :
#10935

I saw id value displayed like this in the input tab of a failing test details I did earlier:
{{ trigger.row.id }} did try to input this value:
"id":"%5B2%5D"
this clearly won't fit an Integer field.

He somehow managed to convert it to integer and removed the foreign characters and only keep the real value, but I am not able to recreate his success in my own automation rule.

I tried to adapt his value to my needs like this
{{ toInt (remove (remove (stringify trigger.row.id) '%5B') '%5D') }}

the output is still a failure
{
"success": false,
"response": "Error: Unknown column 'NaN' in 'field list'"
}

This seems like an overly complicated workaround for a strange issue. How come the .id value which is an Integer type in the database becomes another type in the first place and is now with other random chars?

[mikesealey]

Hi @Reftek85

I've been looking into this for you. It seems like the ID you're getting back from your source is being Percent Encoded or URI encoded (Uniform Resource Identifier). The %5B and %5D pieces of your value are opening and closing square brackets, which implies that the value is an array.

You can use the built in JavaScript function decodeURI() to unpick this and turn it back into something a little more useful, and then access the first value of the array by indexing it {{ steps.1.rows.0._id }}[0].

This only treats the symptom though, and not the root cause. Why your identifier is being URI encoded might be worth looking into within your MYSQL database.

I hope this helps get you unstuck.

[Reftek85]

Hi,
Thank you for your help!
I'm not sure how it would be related to the Mysql Database, I can use the ID column of the clients table to work with url parameters in other pages and bindings. I use a table with clients as datasource, on row click action I open a sidebar, and on a button i have this action: navigate to: /clients-form/{{ Edit row form block.clients.id }}

the page that open is /client-form/1
there is no sign of %5B %5D in the passed parameter. I think that the automation threat my mysql id column like the budibase _id column.

I tried JSscript as step one to decode and use that value to the next step but the client.id still has no value when automation creates the row.

{ "row": { "tableId": "datasource_plus_92701026510648de812754a2d3bebf78__file1", "client_id": "", "create_time": "", "update_time": "", "clients": "" }, "response": { "id": 30, "create_time": "2024-05-08T16:22:04.000Z", "update_time": "2024-05-08T16:22:04.000Z", "_id": "%5B30%5D", "tableId": "datasource_plus_92701026510648de812754a2d3bebf78__file1", "_rev": "rev" }, "id": "%5B30%5D", "revision": "rev", "success": true

It seems like the id column seems to be unusable. I created a new task but if i try a test valu like {{ trigger.row.firstname }} to a text colum the value is passed, if I use my ID column which is my primarykey, there is no valu passed to the other step.
I'll try to rename my index column to idkey later to test further

[mikesealey]

Try swapping from using _id to id - they're both the same value, it's just that one is being bookended by URI encoded square brackets for some reason.

[Reftek85]

Hi Mikesealey
I already tried to swap _id to id to no avail.

so you confirm what I was thinking, that budibase builtin value _id is equal to id, so Budibase automation will ignore my table column named "id" in the mysql database.

I will then try to rename my id column to something else and try automation again.
I'll update this post once its done.
Thank you.