AnsweredAssumed Answered

Need to copy contents between fields in related tables

Question asked by alexchoh on Oct 22, 2016
Latest reply on Nov 6, 2016 by BruceRobertson

Hi, I need advice for my filemaker solution. 

I have Table A and Table B related via one to many.  Table A (one), Table B (many).  Table A is currently populated with data of different unique members that in different groups (say 1,2,3 and 4).


Table B starts empty and I need Table B to record the instances when a member in Table A changes groups.  Table B's fields for example are foreign_key (this key identifies the unique member), old_group, new_group and remarks (to explain why the group change is needed)


E.g. When John from Group 1 changes to Group 2.  I have a layout to update the data into the new_group and remarks fields in Table B.  I want to copy the current_group data (from Table A) automatically into the old_group field in Table B before I commit the record in Table B.


I have been unsuccessful in using the Set Variable and Set Field script lines to "copy" the data into the old_group field in Table B.


If you can advice me on how to do this successfully, my next question would be how I can keep creating new records in Table B as more members change groups.


So if John changes groups 5 times in 2016, He would have 5 records documenting his 5 transfers in Table B. Therefore the one to many relationship.


Please help me if you can.


Thank you.