Select related record of child table in portal

[MrBond]

Hi!

I have four tables which are connected as follows:

A < B > C < D

In the layout of table A i have a portal which is displaying the records of table B. In this portal I have also a field where I can select a record of C and another field where I can select a related record of D. The value of table D is stored in the field of table B. When I change the record in the layout of table D it doesn't affect the stored value in table B. How can it be done that a change of a record in table D will also affect the record in table B?

[comment]

Please do not use abstract names for your tables, it's very difficult to follow. Use meaningful names instead, for example:

I have four tables which are connected as follows:

Invoices -< LineItems >- Products -< Options

In the layout of table Invoices i have a portal which is displaying the records of table LineItems. In this portal I have also a field where I can select a record of Products and another field where I can select a related record of Options. The value of table Options is stored in the field of table LineItems. When I change the record in the layout of table Options it doesn't affect the stored value in table LineItems. How can it be done that a change of a record in table Options will also affect the record in table LineItems?

Feel free to replace these names with others that fit your situation better.

 

Now, the problem seems to be here:

The value of table Options is stored in the field of table LineItems.

Instead of storing the actual value, you should store a link to the original record in Options (i.e. the value of OptionID). Then you need an additional relationship between LineItems and a new occurrence of Options, based on matching OptionID. Use this relationship to display the actual value from the related record in Options in the portal (or in any other context of LineItems).

[MrBond]

Hi!

Well, I have the tables like you said, Invoices etc...

But when I place a field of the Products table in the portal row of LineItems (join table), I can not add or select content in that field in the portal row. It is locked. Why that?
Then same when I put a field of Options in the portal row. 

Thank you!

[Kris M]

If you are on a layout based on Invoices and have a portal to LineItems with a field from Products in the portal you must have an established relationship for the product field to be editable.

LineItems

PK

fk to invoices must have value

fk to products must have value

 

Either that or Create must be appropriately turned on in the relationship graph.

Edited April 16, 2015 by Kris M

[MrBond]

I have placed now two fields into the Portal row of LineItems: fk_Products and the field Productname of the table Products. When I create a new record in this Portal, I can just edit the field fk_Products, the field Productname is locked. I have linked the tables like this: Invoices has a pk, this Primary key is linked to the field fk_Invoices in the table LineItems. There I have also pk_LineItem and fk_Product. The fk_Product is linked with the pk_Product of the table Products.

Creation is appropriately set on in the relationship graph between the relationship Invoices and LineItems on the side LineItems and between LineItems and Products on the side LineItems.

Edited April 16, 2015 by MrBond

[comment]

It's difficult to understand what you're after. IMHO, you do not want to edit any fields from the Products table that you have placed into a portal to LineItems on a layout of Invoices. That would go against the very purpose of the LineItems join table, that enables a many-to-many relationship between Invoices and Products.  Editing a field in the Products table - no matter where this edit takes place - will affect all invoices that are linked to the said product.

[MrBond]

Well, for truth, it's not about invoices. The names are as follows (I thought it would be the same concept): WORKER < JOBHISTORY > COMPANY < CONTACTPERSON

So, the change of a record in the table COMPANY should therefore also change the record in JOBHISTORY. hm?!

Edited April 16, 2015 by MrBond

[comment]

So, the change of a record in the table COMPANY should therefore also change the record in JOBHISTORY. hm?!

​No, it most certainly should not. This goes to the very basics of a relational database:

• A field in the Companies table describes the company and only the company. Fields like company name, address, etc. are not worker-specific;
• A field in the Workers table describes the worker and only the worker. Fields like last name, date of birth, etc. are not company-specific;
• The JobHistory table has fields to describe a specific worker-company interaction. That would mean fields like start date of employment, position, salary, etc. (and also the ContactID of the contact that was the worker's supervisor during this job).

It is the same concept as with invoices, with a possible exception: In an invoicing solution, you copy the price from Products to LineItems, because (1) you don't want future price changes to affect existing line items and (2) you want to be able to modify the price for this invoice only. This may be true for your situation, too: if, for example, you want the record in JobHistory to reflect what was the company's address at the time of this worker's employment there, then you must add a company address to the JobHistory table, too.