3 Replies Latest reply on May 31, 2015 7:00 PM by philmodjunk

    Relationship

    KatNicole

      Title

      Relationship

      Post

      I would just like to ask how to successfully connect one field with the other successfully.

      I have a table - GC Master List which contains the pertinent identifiers of gift certificates and to each addition to the list, the system generates a control number for it.

      I have another table - GC Transactions where only GCs existing from the master list are accepted to transact, I would like to ask how I will be able to show the automatically generated control # in a field in this layout once a user encodes in a valid serial number?

      Also, how to automatically reset the the control number  series back to "0" every day because my control number consists of the current date converted into numbers and a 000 series that is incremental by 1 for every record committed.

      Hope you guys can help as I am really new to developing filemaker solutions. Thank you.


      smiley

        • 1. Re: Relationship
          philmodjunk

          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:

          "Adventures in FileMaking #2 - Enhanced Value Selection"

          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.

          • 2. Re: Relationship
            KatNicole

            Hi PhilModJunk,

            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! :)

             

            Sincerely,

            Me :)

            • 3. Re: Relationship
              philmodjunk

              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 ------<GCMasterList|SameDay

              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:

              New Record/Request
              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 )