I currently have two tables. Account Executive table and a related File table. I'm trying to figure out how to reassign Files to a different account executive.... When i try to change a filed reflecting the account executive's name

(ie. change from Adam to John) it directly changes the related record in the parent table and I end up with duplicates (parent records Adam and John turn into John and John).

Does anyone have any suggestions how I can do this?

So, it isn't 100% clear what you are doing, but I am guessing you do not have a key field.

The "right" way to set this up is for

Accounts to have an "id" field which is unique/auto-enter serial number.

File needs to have an "account_id" field.

You need to change that account_id field. My guess is that you are instead changing the accounts::firstname field.

Erik

No I do have an Id field AEID which links the two tables together otherwise I wouldn't be able to view the 2nd through the portal...

What happens when I try to adjust the AEID link in the child table it just adjusts it in the parent table. At the end I end up with two AEs with an identical ID.

Let me clarify that actually..

When I change the AEID in the linked file table it just adjusts it in AE table and I end up with duplicate AEIDs in the AE table.

When I adjust the AEID in the AE table it correctly reassigns the list of file. ie. if I change the AEID from 1 to 2 it'll update the portal to reflect all files from AEID 2....

I have had the same problem and posted it at:

http://fmforums.com/forum/showpost.php?post/301984/

I hope to get an answer...

Is it possible, in the portal, you have the real parent ID (from the parent table) instead of the parent ID foreign key field, which is in the child table? They will have the same value, and they are in a sense both related fields, since they are in the portal. But you only want to show the ID field local to the child table in the portal. (You can of course show other related fields from the parent table, but not the ID.)

But I could be wrong. It would be helpful to post a small example file. Because I for one find it confusing to follow these "ID this and ID that" explanations :-]

First, I agree with Fenton. It would be much easier to help you if we could see an example of the file.

So, what you are saying is when you change the fk_AEID from John to Mary it creates a new record in the AE Table? If so, check your relationship. Do you have 'Allow Creation of New Records in this table' checked on both sides of the relationship? This would cause the record to have erroneous data if you change the fk_Id or the AE Name.

Edited August 30, 200817 yr by Guest