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.