Subform field not updating with records

[gshockxcc]

This is related a previous post I made here https://ask.libreoffice.org/t/lo-base-concatenate-existing-fields-and-create-a-field-that-displays-it/111165?u=gshockxcc, but I think I’m high jacking that thread, so I figured I would start a new topic.

Since I already created my form, and I was able to get the query working properly, I just wanted to add the subform to the Main form, and update the field with the Queried results.

I found this to help me figure out how to add the subform, and it appears to have worked …
https://ask.libreoffice.org/t/add-subform-to-existing-form/27313?u=gshockxcc

But the ContactName field isn’t updating as I scroll through each record. It’s always displaying the ContactName for the first record.

Thank you in advance for any assistance.

[CRDF]

Add a simplified DB, so we can figure out it in a practical environment.

[gshockxcc]

TestContacts.odb (2.9 MB)

I kept all the table headings, but deleted the data and added some fictitious names.

[RobertG]

This will be shown by form navigator:

Form for concated name isn’t a subform. It is a form parallel to MainForm without any connection.
You could move this parallel form into MainForm:

After moving the form you have to connect “MainForm” and “ConcatName”. Choose “ID” from “ConcatName” and “ID” from “Client_List”.
Set the textbox “ConcatName” as “readonly” in the properties of the field.
.
Better solution:

• Remove the field “ConcatName” from the table “Client_List”.
• Change query to
  SELECT "Client_List".*, "FirstName" || ' ' || "LastName" AS "ContactName" FROM "Client_List"
• Take this as data source for “MainForm” of your form. No subform will be needed.
  .
  Note: You have created the database by migrating from a HSQLDB-database. So the connection to a subform won’t run. This is a known bug of the (experimental) migration wizard 117589 – Firebird: set the "ParameterNameSubstitution" parameter at migration time. I have corrected the file.
  TestContacts.odb (2.9 MB)

[CRDF]

@gshockxcc

just wanted to add the subform to the Main form

What is the point for a subform in this situation?

 
As it was explained in that post, ContactName should not be a field of Table Client_List.

 
Take notice: you CAN put it in the Table via a computed column

[...], "ContactName" COMPUTED BY ( [...]

but for what purpose?

[gshockxcc]

Now that you mention it, you have a good point. It was part of the database that I imported and I assumed that it needed to be there. But the table is a data source in a spreadsheet. So, to your point, “What for?”

Still, your suggestions helped me solve other problems. So, thank you.

[gshockxcc]

@CRDF

[...], "ContactName" COMPUTED BY ( [...]

Can you explain this? What is contained in the […] on both ends. I realize it’s a continuation, but I’m now well-versed enough to know what should go here.

[CRDF]

@gshockxcc

So, to your point, “What for?”

Then there are 3 ways you can insert “Full Name” into table.

1. Via form – see attached DB.
   You do have a “ClientName” field in the table, and after updating the field “LastName”, the “complete name” is inserted. You can also manually edit this name.
2. Via trigger – also a “hard” insert.
3. Via Computed Column:

CREATE TABLE "Tabela2" (
    "Id" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    "FirstName"  VARCHAR(25),
    "LastName" VARCHAR(25) NOT NULL,
    "ClientName" GENERATED ALWAYS AS (COALESCE("FirstName" || ' ', '') || "LastName")
);

 
Attached DB contains options 1 + 3:
Contatos.odb (13.4 KB)

[CRDF]

@CRDF

Via trigger – also a “hard” insert.

This is a trigger for INSERT into Tabela3 (= Tabela2).
Of course should exist also one for UPDATE.

CREATE TRIGGER CLIENT_NAME 
FOR "Tabela3" ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  NEW."ClientName" = COALESCE( NEW."FirstName" || ' ', '') || NEW."LastName";
END