I have a table “companies” with company-information like company name, contactPerson, address etc.
Then I have a table “interventions” that contains service records for each company.
When I create a new intervention (service record) for company A, it automatically fills in fields like contactPerson, address via lookup and auto fill.
So far so good.
Now when i make a copy of an existing intervention it automatically copies the contactPerson and addressdetails from the source intervention record, even if the data in the “companies” table has changed (e.g. contactPerson has changed).
So what I would like to achieve is that when I make copy of an intervention, the contactPerson and addressdetails are looked up again from the “companies” table.
That is why you should use database normalization. You should not copy the info from one table to another, it is better use a CompanyID field in the table “interventions”. To get company information when you use the “interventions” table, you can use Lookup field (see the topic Lookup Table in the help file) or you can use a database view to join the 2 tables by CompanyID field.
yes, I understand in case of fix company data, like company name and address…
But contactperson, phone,… might change in time. I wish to keep old contact data in existing interventions and update new data in new interventions.
I do have a workaround: the user who makes the copy, once he is in the add-page, has to reselect the company (which is already selected, presented as radiobuttons).
At that moment the lookup-function is “refreshed”.
Triggering this automatically would be nicer.
But I don’t know how.