12 Replies Latest reply on Mar 17, 2017 8:38 AM by philmodjunk

    Primary Key Serial Number Creation with Current Year as Prefix

    dennismoorejr

      Hello all.

       

      I am trying to accomplish the following;

       

      Transaction Table

       

      Field: __TransIDpk

       

      I am trying to have the field Generate a serial number on create with the current year as the prefix to and incremental increase.

       

      Example would be

       

      2017_001 etc.

       

      The idea is to have it reset after the new year automatically.

       

      I was playing with the Get function without any success.

       

      Thank you

       

      Dennis

        • 1. Re: Primary Key Serial Number Creation with Current Year as Prefix
          Vaughan

          Noooooo...

           

          Keep the primary keys simple and use these for relationships. If you really must, create another field that displays the year and number. Try very hard to talk the client out of it.

           

          Real-life scenario: the year rolls over and we're into the second week of January. Client now requests that some records need to be entered with last year's numbers.

           

          The best way I have found to accommodate this is to make a table of "Years" and store the incremental number in this -- that way the information for this year and last year are kept. Creating new records involves incrementing the serial number in the Year table being very careful to trap for errors because other users could be incrementing it at the same time.

          • 2. Re: Primary Key Serial Number Creation with Current Year as Prefix
            RickWhitelaw

            I agree with Vaughan. The user should NEVER see the primary key regardless if it's concatenated with the year or not. Keep the PK hidden and do something else to create your field. Check out Serial Increment.

            • 3. Re: Primary Key Serial Number Creation with Current Year as Prefix
              dtcgnet

              I agree with Vaughan and Rick. It's best to have a PK, and if you need this field, then generate it separately.

               

              Options: this field needs to be a text field because of the underscore, but you can still use the auto-enter features to serialize your values. Click the Serial Number option, then enter something like 2017_0001, increment by 1. If you do this, you'd have to change the "2017" to "2018" on January 1 of next year. If you want the serial numbers to reset so that each year starts at 1 again, you'd just set the value to 2018_0001 and away you'd go. You'd have to do that every year.

               

              You could do it with scripting to make it more dynamic and flexible.

              • 4. Re: Primary Key Serial Number Creation with Current Year as Prefix
                richardsrussell

                I agree with Vaughn, Rick, and dtcgnet. DO NOT use "smart" keys — primary or otherwise! They sound as if they should be preferable to "dumb" keys, but they are not, and you will live to regret having ever gone down that road.

                 

                A so-called "smart" key is one that has useful information embedded in it, whereas a "dumb" key is just an arbitrarily assigned (but unique) number that will always stay with that record and will not change, no matter what else about the record changes.

                 

                Now, to answer your question, the nested functions "Year ( Get ( CurrentDate ) )" will return the year of record creation, and you can use Auto-Enter to put that value into a field of its own. Notice that Auto-Enter happens by means of a calculation, but the field itself is a Date field, not a Calculation field. If it were a Calculation field, then its value would change every year on January 1. As a plain Date field, it retains whatever value it was originally assigned unless and until you change it manually. It's up to you whether you want to place that field on a layout where it can be changed manually (by turning on the Browse option) in the Data Inspector:

                 

                Browse Option.jpeg

                 

                Once you have that field (and let's call it YearMade), you can create a Calculation field that will cause it to be displayed along with your actual key, something along the lines of Display = YearMade & "-" & ActualKey. But why?

                • 5. Re: Primary Key Serial Number Creation with Current Year as Prefix
                  coherentkris

                  Concur with Vaughn

                  "Smart" keys are not a good idea because they leverage business rules that may change over time.

                  Implement them ONLY if your willing to do alot of data refactoring in the future

                  • 6. Re: Primary Key Serial Number Creation with Current Year as Prefix
                    philmodjunk

                    I'm going to get a bit "picky" here, but details are important in DB design:

                    the nested functions "Year ( Get ( CurrentDate ) )" will return the year of record creation, and you can use Auto-Enter to put that value into a field of its own. Notice that Auto-Enter happens by means of a calculation, but the field itself is a Date field, not a Calculation field.

                    Perhaps you have a typo and meant "data" field, but this would normally be a number field. It won't work as a date field as it isn't entering a date.

                    If it were a Calculation field, then its value would change every year on January 1.

                    This is only true if this is an UNSTORED calculation field. A stored calculation field using Year ( Get ( CurrentDate ) ) will not update again. Of course, you can't edit it either. Stored calculation fields and auto-enter calculations with the "do not replace..." check box both update only if a field referenced in the calculation is modified. Since there is no field referenced in this expression, only a Get field, it will not update on January 1 unless "do not store..." is specified in storage options.

                     

                    PS. I tend to think of "smart keys" as "Secret decoder ring" keys. These had their uses back when the keys were filing numbers hand written on file tabs or printed on sticky labels. They still have their uses as a way to pack information into a limited space such as on some kind of label so that informed personnel can "decode" important info at a glance without having to scan a bar code or something, but not as a key used to link records in a relationship.

                    • 7. Re: Primary Key Serial Number Creation with Current Year as Prefix
                      bhagara

                      Hoping to add more weight to the "don't do it" side of things.  Primary keys should be simple and invisible to the user.  If you want a 'human-readable' number, feel free to make them and display it to the user, but don't base relationships on it.

                      • 8. Re: Primary Key Serial Number Creation with Current Year as Prefix
                        Stephen Huston

                        I agree with all above-- primary keys should never be "meaningful" data, preferably not even visible to or readable by users.

                        If they think it means something, they'll want to change 'em at some point.

                        What you are trying to create is a meaningful serial sequnce for display/print, but it's not reliable for a primary key.

                        • 9. Re: Primary Key Serial Number Creation with Current Year as Prefix
                          philmodjunk

                          I do have one minor point of disagreement with the advice given here. While I understand the logic and agree that it is a good idea to not show the Primary Key to the user, "Never" is a strong word. I've worked with a number of solutions with visible keys and as long as users are not allowed to directly edit them, there hasn't been any trouble. If your key is a simple serial number and the users can't ever modify it where used as a primary key and can't directly edit it where the value is used as a foreign key, there really doesn't seem to be a problem. If your users ever asked me to change the value or how it is formatted, at that point, I'd introduce a "label" field that does what they need while leaving the primary key untouched.

                          • 10. Re: Primary Key Serial Number Creation with Current Year as Prefix
                            William-Porter

                            Dennis,

                             

                            I want to pile on here and agree with everybody else.

                             

                            If we polled this question among experienced developers, 99.97% of them would agree that (as Stephen H. put it) "primary keys should never be meaningful data" — and the remaining 0.03% would be simply wrong. The job of the key is to hold the database together and absolutely nothing else. Give it any other job and at some point you'll have a conflict of duties.

                             

                            Pseudo-Exception 1

                             

                            Now I do understand that sometimes the client wants or needs some sort of a record or job identifier, something like an Invoice Number of Customer Number or Account Number. But you create these separately. You'd never use them as a key value for the relationships.

                             

                            Pseudo-Exception 2

                             

                            I would also note that it's possible for keys to be generated from ostensibly meaningful elements — so long as they're not truly meaningful, that is, so long as nobody is ever going to try to extract meaning from them. For example, my usual formula for generating primary keys is

                             

                            GetAsNumber ( Get ( CurrrentTimestamp ) ) &

                            Right ( Random * 10000 ; 4 ) &

                            Right ( "0000000" & Get ( RecordID ) ; 8 )

                             

                            Generates a twenty-three digit number like "63625268394848400000077". It may look like there are meaningful elements here — and in theory, my keys could be analyzed to reveal creation time and record ID in the database at time of creation. But I've never done that and don't expect ever to do so. If I discover that the clock in my computer is wrong, for example, it's not going to make any difference at all to my keys. Every record also has a Created Timestamp field, so if I wanted to know when it was created, I'd look at that — and if that was meaningful and if my computer's clock was off by a month or something, I'd fix the Created Timestamp field if I needed to. The point of the formula for the key is to maximize the odds of the key being unique. In fifteen years, hasn't failed me yet. The point of the meaninglessness of the key is to make it absolutely pointless ever to think about changing a key.

                             

                            There are other ways to generate key values and it's a favorite topic among developers. So long as the value is absolutely reliably unique and will never need to be changed, any system you use is okay.

                             

                            Will

                            • 11. Re: Primary Key Serial Number Creation with Current Year as Prefix
                              richardsrussell

                              Phil is, as usual, correct. The above formula for "Year" would produce a result of type Number, not of type Date. Which is, of course, exactly what you want.

                               

                              He's also right about the "stored" part, as I learned to my chagrin when one of my clients asked me why their ages and years in school weren't rolling over as expected, based on my calculation of the difference between "Today" on the one hand and "Date of Birth" and "Year of Graduation" (respectively) on the other. It turned out that "Today" was calculated on the server computer, which hadn't been turned off since April, and that's what was still showing up as "Today" as late as September (when the school year very definitely should have turned over).

                               

                              But here's the thing: Just changing it to an unstored calculation wouldn't have fixed the problem, either, since there was still no reason for FileMaker to change the value from what it had been set to when the server first fired up the file. What I ended up doing was not treating it as a calculation at all but rather as a Global field of type Date, with the startup script for the file specifying that it would be calculated as "Get ( CurrentDate )" whenever the file was opened by an end user. That meant that it was correct for that user for that log-in session. (Still not ideal if the users remain logged on indefinitely, but they don't, so I didn't spend any more time on it.)

                              • 12. Re: Primary Key Serial Number Creation with Current Year as Prefix
                                philmodjunk

                                But here's the thing: Just changing it to an unstored calculation wouldn't have fixed the problem,

                                an unstored calculation re-evaluates every time you bring it up on a layout. This is both a good and bad thing, but if your unstored calculation didn't update, it wasn't a storage issue but that the values it referenced had not changed and thus the recalculation did not produce a new value.