Best way to move a record from one table to another

[bluearrow]

Hi,

I was wondering how other people achieve what seems to be a very common task: copy/move a record (and all the necessary fields) from one table to another related table?

Think of 3 tables: PEOPLE, TEACHERS and STUDENTS. The people table is where every new record is created. It has all the fields it needs for what is known as PEOPLE. People may become TEACHERS or STUDENTS in the future (you do not know it in advance when you create a new record, that being the reason to create a general people table).

The TEACHERS and STUDENTS tables each have additional field that are only pertinent to a teacher or a student, but I would also like them to have some fields present only in the PEOPLE table.

When PEOPLE become either STUDENT or TEACHER, I have to find the best way to copy/move the record from people to its new table (actually, copy the record rather than moves it, but the main point is the same).

Basically:

1. - Create the new record in the TEACHER or STUDENT

2. - Copy all the necessary fields’ data from the record in PEOPLE to the new record in TEACHER or STUDENT.

Please think in abstract. I am not really dealing with people, teachers and student, but with the problem of moving dozens of field from one record in one table to another record in a different table. Although using a drop-down list in the people table would allow to easily classifying some people as teachers or students, I do not want to do that. I prefer to have them in separate tables (I am talking about real tables, not just different TOs).

In my experience, copying field data is a task that is prone to errors and not precisely fast. Maybe it is because I am using a wrong technique.

Thank you,

[jrr316]

Thinking through this, If you copy the data to other tables, you will have to update the data in those tables should something change. You want to have one point of data entry so you only have to update 1 record should a phone number change.

I would handle this by creating all records in the People Table. With Serial Id's of course.

Then you could have a button that would classify the person as a student or a teacher or both.

The script would go to the desired table, create a new record and set a filed to the Person SID. you could then do lookups or case calcs to pull in the data to that table.

I could probably throw together an example file for you if you'd like.

-Justin

[mz123]

if you're opting out from using the drop-down to classify the different types of people, I recommend the following:

Create a relationship between PEOPLE and TEACHER and PEOPLE and STUDENTS

For PEOPLE and STUDENTS:

Make the match fields in the relationship equal all the information you want to transfer (e.g., name, address, email, phone, etc). Also, create a field in STUDENTS called "Transfer" or something like that. Make sure the relationship allows records to be created in the STUDENTS side.

On your PEOPLE layout, add the field "Transfer" from the STUDENTS layout. Put a "1" or a "Y" or some variable in the field. Now, if you go to your STUDENTS layout, a record should have been created with the matching information.

Keep in mind, this does not guarantee that the data in both locations will be synchronized. If you make a change in the PEOPLE table for Joe Shmoe, it won't be reflected in the TEACHER Table for that same individual. That's why the drop-down is so much better.

Another option, is to keep the PEOPLE table as the source for address, phone, email, etc. and the table STUDENTS and TEACHER only contains the ID# of the PEOPLE, so that the data is not in two places.... only one.

Hope that makes sense! Attached is an example file. The table "Students by Field" uses the first method I explained: relating all the fields you want to transfer. "students by ID" uses the second method: relating the data by ID# and having the information from People stay in People.

You'll see if that you edit the data in "Students by Field" the data will not match up. If you change the data in "Students by ID", the data will match up

Martha

20070214_Test.fp7.zip

[bluearrow]

Thank you both, Martha, Justin,

I see that both of you point out a key factor: if I move data from PEOPLE to STUDENTS and TEACHERS, then I would create a possible break point in data integrity. It seems clear (thank you for the sample file Martha; very explicative) that:

- it is best to keep data that should remain identical in all tables, in only one source table

- it seems compulsory to keep one point of data entry, although this could be compatible with tunneling data in other tables

- I could tunnel data from the main source table (PEOPLE) when I need it, to show it to the user in another related table

- The user could modify data in the PEOPLE table using a layout in STUDENTS or TEACHERS tables (tunneled data).

- I could copy data that should remain unchanged in the PEOPLE table but that can be changed in the TEACHERS and STUDENTS tables

- The TEACHERS and STUDENTS tables might contain fields that are not present at all in the PEOPLE table (an atributev that is related to STUDENT or TEACHER but that PEOPLE would never have)

Do you agree with all this?

Regards

[bluearrow]

The script would go to the desired table, create a new record and set a filed to the Person SID. you could then do lookups or case calcs to pull in the data to that table.

-Justin

But then, what do you think it is best? Lookup/Calculated fields or Set field steps? (what I mean is that I could have a lookup or calculated field in the target table, or I could use several Set fields stpes to copy the data. Take into account that most probably this data would never be looked up again from the source table. I am not sure which method is faster and less prone to erros.

Thank you

[Søren Dyhr]

Lookup/Calculated fields or Set field steps?

Depends on the purpose, if the data is snapshot of a price that a likely to fluctuate due to marked demands are lookups the only choise otherwise is it neither Set Field[ or a calc' that are most suitable, since you're on fm7 can they be referenced as long the keying is valid. The issue here is how historic the data need be?

--sd

[jrr316]

I like to use case statements to lookup data. So lets say you create each PERSON with and SID (Serial ID). You would create a field in Students for Person.SID and create a relationship between the 2. All your "Lookup Fields" would reference this relationship.

Ex: for your FirstName Field in the Student Table.

Case (Person.SID=Person::SID, FirstName=Person::FirstName,"").

This way your Script in the PERSON table would only be to Set Field to a global, GO to Layout Student, Create New, Set Field.

Attached is a demo.

People.fp7.zip