Table Relations

asembereng

I want to create a table relation in such a way that any changes made on the main table will reflect on all other table having relations to the main table.
I used to table field property to create table relations but it only allows me to relate only one table. whenever i try relating more than one table i have this error
"the cursor is in front of an invalid character"

Does anyone knows how to do this? All i want is to create a table relation in such a way that any changes made on the main table will reflect on all other table having relations to the main table.

bbrown

You define the table relations on the other tables. Let's say you have a master table (Customer) with a primary key (No.) and you have several tables with a field ("Customer No.") that is related to the Customer table. Set the table relation on the "Customer No." field on each of those tables.

asembereng

Thank you. Its workimg. If i import data to the main table will the changes reflected on all other tables?
Basically the reason why i am doing all this is because we have change to new set of numbers and we want to replace the old numbers by new numbers and we want this to take effect in all tables where we have employee numbers as a field there.

bbrown

This behavior is tied to the rename function of the main table. When you rename the primary key of a main table record the system locates the records for related tables and renames the field value.

If you simply import new values into the main table they will be treated as new records (insert) and no renaming will occur. Your import process must match the new records with the old records and execute a rename.

asembereng

Sounds pretty interesting. Any idea on how to achieve that?

bbrown

You will need to establish a relation between the old a new records. Retrieve the old record and use the RENAME function to set the new value.

asembereng

I tried doing it but it always insert a new record. Can you help me on this please?
I have two fields from a table called employee, the two fields are called No. and ssno. I want to rename No. from the txt file if the other field on the text file has the same number as that of No. on the table.

For example, the text file

No. SSNO.

A123 123
B123 345

I want field No. to be replaced on the table if SSNO on the text file is the same as that on the table.

Please help me on this.

bbrown

Retrieve the employee record that matches "SSNO.", then use RENAME to rename the primary key;

IF Employee.GET("SSNO.") THEN
Employee.RENAME("No.");

asembereng

Thanks