budibase Field Value updated from Relationship Picker onChange

Discussed in https://github.com/Budibase/budibase/discussions/6176

^{Originally posted by mmalessa80 June 2, 2022} In a form I have one relationship picker for the one side on a one-many and another picker for the many side on a many-many. I added a set value action on another field under the onChange event in an attempt to get the ID field, however the actual value that is entered into the field contains additional characters besides just the ID.

The underlined parts in the respective fields that get filled in are the IDs in this case, but as can be seen by the screen shot there are additional characters.

Jun 06 '22 08:06 melohagan

That looks like full unicode encoding.

%5B and %5D are the encoded values for [ and ]
' is the unicode value for an apostrophe (a.k.a single quotes)

So for example %5B'4339458d'%5D is just ['4339458d'] encoded. The value ['4339458d'] is how we represent IDs for rows pulled from SQL tables. That full array value should never make its way to the SQL table though, as it's an internal Budibase concept only.

I worry that what has happened here is that the internal Budibase row ID (these array values) have been used in an external query or automation and have actually been saved into the external SQL table. In doing so, it looks like they've gone through full unicode encoding.

I don't think this is anything we do because we never use full unicode encoding. At most all we do is URL encode those values, but that would not encode an apostrophe into unicode.

Jun 06 '22 08:06 aptkingston

Thank you for the explanation. So, the ReporterInfo field is in fact a relationship and the input is a relationship picker. If I wanted values in the columns Reporter_Name, Reporter_Email, and Reporter_Phone from columns in this entry form from that relationship table, how would I need to go about doing this? Since the relationship picker only actually stores the ID rather than having full access to the entire row I thought I would pull the ID from the relationship picker, then run a query against the relationship table, then use the result from that query to set the value in the field. However, when I 'feed' the relationship field value into the query it gives me the default result because it does not find a key with the field value since it is encoded.

Jun 06 '22 15:06 mmalessa80

This issue has been automatically marked as stale because it has not had recent activity.

Aug 10 '22 04:08 stale[bot]

Re-opening as there was another related discussion here: https://github.com/Budibase/budibase/discussions/7687

In this case the user was having issues with the default value. You should be able to provide numbers and Budibase should do the encoding if needed.

Sep 13 '22 15:09 melohagan

I too am looking for a solution to this. I am trying to save the row ID for the previous action ( {{ Action 2.Saved row._id }} )

However whenever I output it to my database it comes out not as the row number but instead as something like this, %5B2158%5D. I have figured out that this is the URI encoding for the string [2158]

Is there an easy way to just output the integer 2158, I've tried to use handlebars URI decoding but I am new at chaining handlebars commands so I have not had any luck. Any help is appreciated.

Oct 03 '22 14:10 hydroxy

@hydroxy the _id property coming back from that response is the Budibase internal ID for referencing your row - you shouldn't need to ever use this manually, and you definitely don't want to insert this into your SQL table. Can you reference the ID from the saved row response based on whatever your actual ID column name is?

Oct 03 '22 14:10 aptkingston

I am looking to create a row in the process table it's ID will be processID, then create another row in processFertilize table its ID will be processFertilizeID. The process table will be super-table and processFertilize will be sub-table.

What would be the best way to do that? I am using a mySQL database to store the information also.

Oct 03 '22 15:10 hydroxy

Related encoding issue here, but with execute query: https://github.com/Budibase/budibase/discussions/8087

Oct 03 '22 16:10 melohagan

Excellent, that resolved it for me. I was trying with the javascript but couldn't quite get the exact syntax right. Much appreciated :)

Oct 03 '22 16:10 hydroxy

The solution here is just to use whatever your ID columns are. If you want the processID to save in the processFertilize table, then use Action 1.Saved row.processID, not our internal _id column. There should never be a need to save the Budibase _id column anywhere, so there should never be a need to need to decode it. It's only for our internal reference.

Unless I'm missing something here, there is no work to be done. Maybe we just need to update the docs to make it more clear to not use our _id column. We could potentially just not generate the _id bindings for rows in SQL databases, which would hopefully discourage people from using it.

Oct 13 '22 15:10 aptkingston

Thanks for the reply and the fix to overcome this issue, but it is related to more fields (for example relationship fields):

port - Option picker (based on Data Provider from SQL) quay - Option picker (based on Data Provider from SQL) ship - Relationship picker (based on Form and corresponding field)

So I most probably would use another Data Provider for ship anyways, relationship fields are not ideal so to say 😅