Primary key not included in changesArray

[nickaus]

Hi,
I have a front end table which is data from two Retool DB tables (contacts and organisations), with the organisation name being grabbed by a foreign key on the contacts table. Previously I was just loading data from a single table (contacts) and could use the Bulk update via primary key to handle multiple updates to the data. The .changesArray object always contained the pk and any changed key:values

Now however, when I make any change to data in the UI, I find that the primary key of the contacts table is not in the .changesArray object so any updates to the table fail.

Screenshots of the select statement and changesArray object after a change. Anyone have ideas on this? Do I need to use SQL mode instead of GUI mode?

Thanks in advance!
Nick

[Oscar_Ortega]

Hi, does your table have primary key field set?

[nickaus]

Hi Oscar,
Thanks for replying so quick! Yes it does. I'm temporarily showing the PK and FK for now...

Nick

[Oscar_Ortega]

Thats strange, cannot imagine whats happening

[nickaus]

Agreed!
But... I refreshed the App, task switched for a bit and now it seems to be magically working

Thanks Oscar!

Nick

[Oscar_Ortega]

Yeah, it happens to me too, something stop working as expected and refreshing the page solves the problem

[nickaus]

Cool good to know its not just me!

While I have your very helpful attention, do you know if there's a standard way to use Bulk update via primary key when I update a field from another table? In this instance, say I change the Companies value, the .changesArray has organisation_name and the id, but what I actually want to update is the fk_contacts_organisations field.

Any thoughts?

Regards,
Nick

[Oscar_Ortega]

Hi, i dont know if there is a standard way to achieve that
but what i do is
i set the fk column to be editable, so when i made changes the chagesetArray will contain the fk value and not the label

[Oscar_Ortega]

in your case you can do the following

1. use your FK column and set the mapped value to be the fk value and display their respective label (make it editable)

2. like above but if you want to show the fk id in a separate column, create a new column to display the ids (but not editable)

[nickaus]

Hi Oscar,

Thanks for the guidance. I'm part way there following your advice above but I think I'm still missing something. I have made fk_contacts_organisations editable, and the mapped value is {{contacts.data[i].organisation_name}}. This looks good in the UI.

However, when I change the organisation, the organisation_name is in the .changesArray object. I can understand why, but not sure how to sort of reverse map it so that the FK integer value is in the object instead.

Not sure if its relevant, but I'm creating the dropdown list using a basic Query JSON with SQL resource which is:

select  DISTINCT org_data.org_name_short as organisation_name from {{organisations.data}} as org_data

[Oscar_Ortega]

Hello, if your Query returns id and organization name you can set mapped value like this

then the changesetArray should contain the value (id) and not the label

[nickaus]

Thanks again Oscar,

I have added the org name (as well as ID) to the query, but I don't see the same Mapped options as in your screenshot. I can only set a Mapped value.

The datasource of the table is loadContactsDB and highlighted are the available options on the field org_id which is pk of the related organisation. I must be missing something?

Regards,
Nick

[Oscar_Ortega]

I don't know if im understanding

as i can assume you have two databases

1. contacts
2. organisations

lets say contacts have the following columns:
id (pk), org_id (fk), contact_name

lets say organisations have the following columns:
id (pk), org_name

so you populate your table with
contact_id, contact_name, org_id, org_name

e.g.
id: 1
name: John Doe
org_id: 11
org_name: Company1

if you want to update the foreign key of contacts (org_id) you can do the following:

1. Hidde org_name column since is not needed for this case
2. Make org_id column to be "tag" type
3. Make it editable
4. In add-ons you wil see "option list", click it and populate it as the image i sent above

if correctly done when you make changes in org_id column, changesetArray should contain the new selected foreign key

[Oscar_Ortega]

in this table "Proceso" column is populated by a foreign_key
i have operations_db and process_db

when i make changes to "Proceso" column i update my operations_db fk

[nickaus]

Thank you so much for bearing with me @Oscar_Ortega !
I got it to work (finally )

This is super helpful and I will need to use this approach on a number of Apps and functions in the near future. We're just in the process of onboarding.

Again - so grateful. Thank you!

Nick