14 Replies Latest reply on May 28, 2013 5:29 AM by JohhnyHilly

    ID Number/Primary Key generated from 2 fields

    JohhnyHilly

      Title

      ID Number/Primary Key generated from 2 fields

      Post

           I am generating an ID number from 2 fields. This ID number is my Primary Key for a table. How it is generated is a Company name is selected, then a Country Code is selected and the ID number is created. This is no problem if I have "Do not replace exsting value of field" unchecked in the field options.

           I want to have this field checked as I don't want the Company ID number to change at all. But if I check that option, once I select the Company, the ID number generates and doesn't allow me to include the Country code, as it can't update. 

           Does anyone know how to get around this without unchecking that Do not replace field..?

           The only thing I can think of is creating an onobjectenter script trigger prevent the Company and CountyCode fields to be changed once they are populated. But even this I don't want to do as at some point a Company name may change, but I want to keep the ID number the same. Thoughts?

        • 1. Re: ID Number/Primary Key generated from 2 fields
          JimMac

               It is not necessary to "compound" ID numbers.  You can have 3 Primary keyssurprise

               On your relationship graph, simply connect Name, Code, Serial number to foreign keys of same type in that table.  The connections will be AND logic require all 3 to be true to Relate.  Then to prevent change, save them in a non Available field that is Primary "frozen" key, if the company name changes, the Frozen Name won't.wink.

               Jim...

               PS: For some reason, many want to make a compound Serial Number.  Why?  I am not sure, but FMP makes it too easy to associate a simple number to Names and Country Codes and Portals can find them.

          • 2. Re: ID Number/Primary Key generated from 2 fields
            JohhnyHilly

                 Thank Jim but this isn't a relationship between 2 tables. This is creating a ID number for a Client and is on the very first table. All other tables are connected to that one Client table. Is what you described still applicable? Maybe I am not understanding something?

            • 3. Re: ID Number/Primary Key generated from 2 fields
              JimMac

                   Yes it is applicable.  We seem to think a table can be related by just one "line", but you can connect muliple fields [lines].

                   The Connections or relastionships are very flexible with logical operators too.  Most helpers will suggest to avoid Names and stay with Unique Numbers to avoid, duplication, typos, and changes in names.

                   In your proposed ID method  I would suggest simpifing to just the Unique Number ... ONLY.  Which will remain the Company Identifier "forever", no mat

                   ter Name or Country Code changes. 

                   You can FIND all Companies with same Country codes.  You can save a non changeable field as Original Name....to allow name changes in future.

                   Lets say you want to generate a Contact Event table that was related to that pkClientID.

                   Then just that single number will link all Contact Events to that fkClientID.  Why make it complex adding a Name and Country Code to relate the tables.

                   But if you desire all 3 then...

                   pkName, pkCode, pkClientID   in the Client Table

                   fkName, fkCode, fkClientID  int the Contact Event Table

                   Now connect each pk to fk on the relastionship graph.surprise

                   Which means all 3 pk and 3 fk must match to get a related record.  Make sure field types match.

                   Jim...

              • 4. Re: ID Number/Primary Key generated from 2 fields
                JohhnyHilly

                     Jim, I don't want all three. What I want is to build the __pkClientID from 2 other fields in the same table. One is Country Code and the other is Company Name. So if the US is the country code and the company is called Apple, then the _pkClientID will be US-APP. This primary key is then used in the connections/relationships to the next table.

                     This then brings me back to the original questions of how I can "freeze" the primary key while having it based on the 2 fields from that same table

                • 5. Re: ID Number/Primary Key generated from 2 fields
                  JimMac

                       BUILD is the key term.  I called it Compounding.  I understand completly.  Why not use a single number and build the compound "look", if it makes your concept seem better.  You don't need the compound pkClientID.wink

                       Good Luck!angel

                       Jim...

                        

                  • 6. Re: ID Number/Primary Key generated from 2 fields
                    JohhnyHilly

                         Sorry, how do you mean "use a single number"?

                    • 7. Re: ID Number/Primary Key generated from 2 fields
                      ninja

                           Johnny,

                           You reference "Do not replace..." checkbox.  I take that to mean that you are in the autoenter dialog...and that is probably your initial problem.

                           Make your assembled field a calculation field, not an autoenter.

                           Then define it as Table::CompanyName & Table::CountryCode

                           This references your company name field, but certainly won't replace its contents.

                           It is OK to have a calculated field be a key field.

                      • 8. Re: ID Number/Primary Key generated from 2 fields
                        JimMac

                             In your Client Table...

                             Create a Number field say .... pkClientID.  In the Options...  Auto Enter Sequence number starting at 1 increment by 1.  Make that field non Modifiable by USER.

                             In all other Tables... like the Contact Event table I used for example...

                             Create a Number field say... fkClientID.  Relate the    pkClientID>------<fkClientID   and Allow creation of records in the Relationship.

                             When you make a New Record in the Contact Event table... you put in the fkClientID=pkClientID showing which client made the Contact Event.  There are many ways to do that, depending on how you set up your DB.

                             In your Client Table...

                             you have Country Code and Name fields....  these can be shown... using the relationship... on the Contact Event Table also... No need to Compound a serial numberyes.

                             Jim...

                        • 9. Re: ID Number/Primary Key generated from 2 fields
                          JohhnyHilly

                               Ninja, I have done as you suggested but if I change the Company name field it replaces the contents in the __pkClientID calculation field, so just the same as the AutoEnter one. Thoughts? And yes I tried checking the "Do not store calculation results--recalculate when needed" but still same problem.

                          • 10. Re: ID Number/Primary Key generated from 2 fields
                            JohhnyHilly

                                 Sorry Jim I don't really want to create a Client from another table. I'd rather create the client first then go from there. The thing is I want to compound the __pkClientID. I've already had it setup with an incremental number and for my purposes wasn't great.

                                 Anyone else have an idea as Ninja's suggestion doesn't seem to work unless I'm doing something wrong.

                            • 11. Re: ID Number/Primary Key generated from 2 fields
                              philmodjunk

                                   I would never try to produce a pk from two or more fields. I'd always use either an internally generated serial number or an auto-entered Get (UUID) to unqiuely identify each record in the table.

                                   THe only way, I've have such a field in my database is to support specific user requirements to to facilitate specific search/reporting needs and then I would have just those fields in the parent table and would not use it in relationships--which would mean that I am not using it as the pk field.

                              • 12. Re: ID Number/Primary Key generated from 2 fields
                                RickWhitelaw

                                     Ditto what Phil said. You can still have your concatenated field. Just. Don't use it as a primary key. There's not really any reason to.

                                • 13. Re: ID Number/Primary Key generated from 2 fields
                                  JimMac

                                       yes, ditto to the 4th power and add sugar on top.

                                       @Phil  I  HAD to make a Compound Invoice number for one solution since for Posterity reasons, as you noted, they had everyone trained on those archaic type serial numbers.

                                       But...

                                       I still used a "secret" numeric field to Relate records and just displayed the Archaric compounded number for them.

                                       Jim...

                                  • 14. Re: ID Number/Primary Key generated from 2 fields
                                    JohhnyHilly

                                         Ok thanks guys. I'll use it as a display only then and have an auto-generated one in the background. Thanks for the advice.