I'm using filemaker to track gift cards given to clients, and I'm having trouble working out the relationships between two tables.
Here's the setup:
In Table1 I have: client ID, Gift card number (GCN), gift card amount, payment date. Each gift card gets its own record.
In Table2 I have client ID and a log of nine different events, each of which has its owne separate field for: gift card number (GCN), payment amount, payment date. Each client ID has its own record, but can have up to 9 gift cards associated with it. Also, each GCN field in Table2 displays a value list of the GCNs existing in Table1.
Here's what I need:
1) As soon as a GCN is entered in any one of the nine distinct GCN fields in Table 2, I'd like the client ID and the payment date to automatically be filled next to the matching GCN in Table 1. (So far I've been able to set up a relationship and calculation to update these values for the first payment event. I can't seem to make it work simeltaneously for the other eight.)
2) Once a GCN is selected in Table 2 and assigned to a client ID, I'd like it to disappear from the GCN value list.
I may be going about this the wrong way. Any help would be much appreciated!