updating fk of a record gives an error (libre base ver: 5.1.6.2)

[rajko]

Hi,
i recreated this example:

Base: How to design an editable query, but sorted by a value in a related table?

Given two related tables how to edit the primary table while sorted by the secondary table. Some Queries are not editable. Some Queries even crash LO when edited! My question is about how to build an editable Query (or a Form based on that Query) that is sorted by a related value. Here are the two example tables: [image description] – [image description] They are related like this: [image description] Here is a simple db with the two related tables, Houses and Colors to play with.

but Im getting an error saying “Error updating current record”. The problem is when i try to change DepID on a record libre base also try’s to change the id of referenced record in Dep table. Why is libre base doing that?

Manny thanks

[Ratslinger]

Hello,

original post now deleted after addition of Edit #2. Was totally incorrect.

Edit:

Based upon your comment have deleted above.

Have tested the sample in the link provided and have had problems. While I can add new records (as shown), and I can modify the name field, changing an existing department using this method (query) produces the error shown - this on Ubuntu 18.04 using LO v6.3.0.4.

As asked in comment please elaborate on what you are looking to accomplish and there may be a different method.

Edit #2:

OK, have found the problem with the linked post answer. It is in the Query. Using the Right Outer Join is the cause. But to correct the situation had to use a different method only through SQL View. Therefore you cannot use the graphical interface. Have attached a sample based upon you original post. This sample does NOT include the macro referred to in the linked post. You can add that if you wish a refreshed list after each modification. Up to You.

sample ---- FKproblem.odb

[rajko]

Dep stands for Department, just needed two tables to illustrate my problem

[Ratslinger]

Dept may have been a bit more helpful.

Best if you post a sample as can’t tell much from images.

Also, why such an old version of LO? What OS?

Edit: Please explain what it is you are attempting as what you show as wanting to accomplish does not require a query or macro as in the provided link.

Just a simple straight forward form.

[Ratslinger]

Another possibility is for @EasyTrieve to respond to this as that was who posted answer in the link. Maybe can shed some light.

[EasyTrieve]

@rajko, I would love to be able to help further with this right now, but am unable to due to other time constraints in my life. Seems like everything is falling apart at the same time. But I think the example I laid out is pretty clear, and I would suggest that you first try to get that example to work and make sure you clearly understand each and every step of it. Good luck.

[Ratslinger]

@EasyTrieve,

Sorry to hear all is not well with you. Best wishes and will offer prayer for you that things get better.

Thanks for responding but just found the problem. You can check back in some future point as the resolution.

[rajko]

@Ratslinger separate query does the job, thank you!