in GC Master List, you need a primary key. A field that auto-enters a unique value that is never, ever changed for that record. An auto-entered serial number is the simplest to set up. you can also set up a text field to auto-enter Get ( UUID ) in a calculation.
I would name such a field in this fashion: __pkGCID Note that this is NOT the control number that you describe as what you describe for it is unnecessarily complex for uniquely identifying records in your database.
You'd then define a number field in your transactions table to serve as the foreign key: _fkGCID.
In Manage | Database | Relationships, you'd then use the mouse to drag from __pkGCID to _fkGCID to link the two tables in a relationship.
The simplest way to add a new record to transactions and link it to a record in Gift Cards, is to format the _fkGCID field with a drop down list of ID values from __pkGCID and a second field value that identifies the particular gift card in a more user friendly manner--such as a name or a value printed on the card. The user uses the second field value to select a gift card and the value list enters the ID in order to link it to that record in the Gift Card table.
Once you can get that option working, there are other, more user friendly methods. You can scan a barcode on the card or data encoded in a magnetic strip. You can set up search tools where you enter a few letters of a name and the list filters down to just the entries that match that name. For examples of such search tools (not bar codes or mag strips), see:
As to that control number, why do you need it? Generally speaking, there are better, simpler ways to get the job done so unless you have some specific reason such as "the cards come imprinted that way" or "the client/boss insists" or "Such and such an agency requires it". It's simpler not to bother.
I can't express how thankful I am for this bit of knowledge that you have so generously shared with me. As to why we formatted the control #'s to be that way, well, let's just say my boss deems it necessary for us to have a second layer of 'security' aside from the serial number that we have.
Oh.. and by the way, your tip works! I never doubted it actually, just took a while for me to get to it again.
Thanks again and I look forward to learning more from you and this forum! :)
I would not use this to generate IDs to be used as primary keys in relationships, but for those times when you need a "secret decoder ring" identifier to satisfy a requirement specified for your project....
Define a self join relationship like this:
GCMasterList::CreationDate = GCMasterList|SameDay::CreationDate
Note that these are two occurrences of the same table and CreationDate is a date field that auto-enters the creation date. The relationship will match any given gift card record to all other gift card records created on the same date.
Now define a number field named Sequence in this table. Use a script to create a new GiftCard record that runs like this:
Set Field [GCMasterList::Sequence ; Max ( GCMasterList|SameDay::Sequence ) + 1 ]
Your ID field can be defined as a calculation field that looks something like this:
GetAsNumber ( CreationDate ) & Right ( 8 ; "0000000" & Sequence )