Using different Primary key when saving changesetArray via query GUI

[davblo]

I think I've seen this mentioned in a post before, but it didn't seem to be well understood.

As all will know, populating a table with data from a query that used JOINs can result in the table presenting data from several tables. in the table properties, the Primary key will generally come from one of those tables.

When editing the data in the table, the changesetArray gets the primary key added automatically. Then in the query GUI we also select the Primary key by name, presumably so the GUI uses the correct key when updating the database.

The problem arises when the data being edited actually comes from another table (brought in via the JOINs).
In the GUI we can select to update this other table, but then the Primary key needs to be the one for that database table, not the one in the displayed table. We can select the correct primary key in the GUI, but then we get an error because that key isn't present in the changesetArray.

changesetArray
[
  {
    "Long_description": "Some description being updated.",
    "Package_content_Id": 4302
  }
]

The required key is present in the table, but it can't be the primary key because it can appear duplicated on several rows.

We need to be able to nominate a "non-Primary" key to be added to changesetArray.

(I looked into using changesetArray.map() to add the correct key, but I don't know how to find the right key value for each of the objects in the changesetArray.)

Edit 1
Actually, I see that it is possible to select the "other key" as the Primary key in the displayed table, even though it has duplicates.

There is a warning triangle...

...but the key is added to the changesetArray, so the GUI query can run without error.

But is that a good way to go?

Edit 2
Answer - no, it's not a good way to go!

All seemed to go well at first. I was able to edit "within" the rows and save using changesetArray in the GUI query.

But then some weird things started happening to the table. I have grouping on a particular field and suddenly rows were appearing in the wrong group, all over the place. Setting the Primary key back to the "proper" one got the displayed table back how it should be.

So back to the drawing board....

Edit 3
I found a way to do this without messing up the displayed table.

Here as a screen shot of the query GUI...

And the actual code as text here...

{{
  table16.changesetArray.map(obj => (
      {...obj, Item_Id: table16.data.find(ob2 => 
      ob2.Package_content_Id === obj.Package_content_Id).Item_Id}
    )
  ).map(({Package_content_Id, ...rest}) => rest ) 
}}

So I have a table where the Primary key is Package_contents_Id.
But I want to update details of an Item (from the database table Items, with key Item_Id) shown in that table, possibly multiple times (like on different days).

The first .map adds the new key Item_Id: to each object in the changesetArray.
To get the value for that key .find locates the matching Primary key in the table16 and gets the corresponding Item_Id.
Then the final .map strips out the unwanted Primary key from every object in the array.

[MiguelOrtiz]

Yup, I would have ended up with pretty much the same solution. Well done.

The obstacle comes whenever you have editable columns that are for different tables. When that's the case, I usually end up with a form rather than edits directly in the table.

[davblo]

Yes, a form is fine when there is a significant amount of work to do on individual rows, and they can be taken slowly, one at a time.

But when there are a lot of small changes needed on many rows, then editing in the table is much faster and all the changes can be updated in one go.

[MiguelOrtiz]

A way of working with multiple small changes for two or more tables is by mapping the keys of the changeset array to different trigger queries, e.g.

const allowedCustomerKeys = ["customer_id", "customer_name"];
const changeset = customer_invoices.changesetArray[0];

const customerKey = Object.keys(changeset).find(k => k !== "id");

// Conditional logic based on key
if (allowedCustomerKeys.includes(customerKey)) {
  bulkUpdate_invoices.trigger();
} else {
  bulkUpdate_vendors.trigger();
}

I add this to a change cell event handler (and usually have my table's data with a variable as data source so I can update the variable on success rather than having to run the source query on each change.

[Darren]

@davblo

I found a way to do this without messing up the displayed table.

Here as a screen shot of the query GUI...

And the actual code as text here...

{{
  table16.changesetArray.map(obj => (
      {...obj, Item_Id: table16.data.find(ob2 => 
      ob2.Package_content_Id === obj.Package_content_Id).Item_Id}
    )
  ).map(({Package_content_Id, ...rest}) => rest ) 
}}

So I have a table where the Primary key is Package_contents_Id.
But I want to update details of an Item (from the database table Items, with key Item_Id) shown in that table, possibly multiple times (like on different days).

The first .map adds the new key Item_Id: to each object in the changesetArray.
To get the value for that key .find locates the matching Primary key in the table16 and gets the corresponding Item_Id.
Then the final .map strips out the unwanted Primary key from every object in the array.

I'm documenting this here just so it can be marked as the Solution. Thanks for sharing your thought process, @davblo!