13 Replies Latest reply on Aug 10, 2014 3:53 AM by Christic

    Unique in field

    Christic

      Title

      Unique in field

      Post

           Ok, I am trying to set a customer id based on first 3 letter of last name plus first letter of first name plus 3 digits.

            

           Example  John Smith would  be SMIJ000, then the next person say Jill Smith would be SMIJ001.

           So it will pop up and say that is not unique, but I would rather have it in the calculation know that SMIJ000 is used and add 1 to the end and if that is used go to the next number..

            

           This is replacing a database that is in place so it needs to be like this...

            

           Any ideas?

        • 1. Re: Unique in field
          philmodjunk
               

                    This is replacing a database that is in place so it needs to be like this...

               But that does not mean that you have to use this identifier to link tables in your new database that replaces the old. Keep it in the system as a data field used for searches, sorts and labeling, but use a simple serial number field or a text field that auto-enters Get ( UUID ) for you primary key.

               You can use a self join relationship to produce this identifier, but keep the unique values validation as you can still get duplicate values if two users try to generate this value at just the wrong split second interval of time.

               First, separate the text and the number parts of this ID into two separate fields: cIDText and Sequence. You can set up a text field with an auto-enter calculation to combine them and validate for unique values.

               cIDText can be a simple calculation field with a text result type: Left ( LastName ; 3 ) & Left ( FirstName ; 1 )

               Now set up a self join relationship by creating a new table occurrence of your table and linking it to the current table occurrence by cIDText:

               YourTable::cIDText = YourTable 2::cIDText

               Now you can use this calculation, either in an auto-enter calculation or the set field step of a script for creating the new record:

               Max ( YourTable 2::Sequence ) + 1

               If you set this up as an auto-enter calculation, be sure to clear both the "do not evaluate if all referenced fields are empty" and "Do not replace existing value" check boxes.

               Your Auto-enter calculation to produce the ID code in a text field would then be:

               cIDText & RIght ( "000" & Sequence ; 3 )

          • 2. Re: Unique in field
            briancrockett

                 I'm assuming the number part is unique per the text part. For example you could have SMIJ001 & JOND001

                 You'd use a relationship like the one pictured based on the text part. "SMIJ"  

                 This is basically a rewording of what Phil says.

                 I would use a script to set the identifier rather than an auto enter field. I tried this out and the sequence doesn't increment if you set "Do Not Replace" and if you don't then the sequence number increments any time you make a change to the record. Like if you change John to Jonathan the id increments. Using a script with Phil's calcs should work.

                 Just one change  
            "If(IsEmpty(YourTable 2::Sequence);1;Max(YourTable 2::Sequence) + 1)"  
                 This set's the first record, because if you add 1 to NULL ( "" ) the result is NULL.
            • 3. Re: Unique in field
              philmodjunk
                   

                        I tried this out and the sequence doesn't increment if you set "Do Not Replace" and if you don't then the sequence number increments any time you make a change to the record.

                   Because the data is from a related table occurrence, it will not increment any time you edit the record. I've tested this in a file previously.

              • 4. Re: Unique in field
                briancrockett

                     I must be doing something different. I set this up in a test file just now and it's incrementing.  Only the sequence (Serial in mine) number is coming from the relationship. 

                     It's too bad I can't attach my file. Here are my calcs though.

                • 5. Re: Unique in field
                  philmodjunk

                       You can't upload a file, but you can share one using a file sharing site such as Drop Box.

                       Compare your file to mine: https://dl.dropboxusercontent.com/u/78737945/CategoryBasedSerialIDs.fmp12

                       If you edit the contents of "other field" the contents do not change.

                       I suspect that your auto-enter calculation refers to a field from the wrong table occurrence.

                  • 6. Re: Unique in field
                    briancrockett

                         I opened yours In FM 13 and it looks pretty much the same as mine. When I changed Fred to Freddie the CategoryIDField incremented. I can change "Other Field" and it does not increment.  I think because FirstName and LastName are part of the calc changing them causes the increment.

                    • 7. Re: Unique in field
                      philmodjunk

                           And in that case, you are changing the value of a name field and it should increment in most cases.

                           But you raise a good point that I hadn't considered. (This is yet another reason NOT to use this type of calculated ID if you can avoid doing so.)

                      • 8. Re: Unique in field
                        briancrockett

                             I agree, not a good idea to use a calculated ID. But I'm thinking, once set, the CategoryIDField should never, ever change as it will be used as a key in relationships. 

                             Here we do use a calculated ID's but it's much simpler. We just concatenate a single letter on the end of our serial numbers. Examples 12345C for contacts table, 12345V for invoice table and so on. This way when someone sends you an ID number you know what table they are referring to.

                        • 9. Re: Unique in field
                          philmodjunk
                               

                                    as it will be used as a key in relationships.

                               But to repeat, this should never EVER be used as a key in relationships!

                               Use an auto-entered serial number or UUID for that.

                               The only reason to use this ID in the first place is when legacy systems/business practices require it or when you need an "encoded meaning" label that must fit within a very tight space requirement. But you can support all of that in your database without using such an ID as a match field in relationships.

                          • 10. Re: Unique in field
                            briancrockett

                                 True. Still it shouldn't change as it's a unique reference to the legacy system. If you ever have to refer back to the old system, you'll need it. 

                            • 11. Re: Unique in field
                              philmodjunk

                                   As I said before, you have a good point, though the necessity for this can depend on how it is used in the original system.

                              • 12. Re: Unique in field
                                Christic

                                     Wow, you all are great, I was very impressed to see all your help.

                                      

                                     Brain you are correct, the 1st four letter will depend on the name and the number wild change only if there is another name with matching 1st four letters.

                                      

                                     I am not sure how that works with 2 tables, I will have to try out,

                                     I with there was a way to just check unique in an calculation.

                                      

                                     Thanks

                                      

                                     Clarence

                                • 13. Re: Unique in field
                                  Christic

                                       You guys rock!!!  I can not Thank you enough!!!!  I was not sure if it was even possible.  

                                        

                                       Thank You!!!

                                        

                                        

                                       Clarence