11 Replies Latest reply on Mar 5, 2015 10:36 AM by philmodjunk

    Custom Serial number  by Calculation

    RobertT

      Title

      Custom Serial number  by Calculation

      Post

      I'm trying to figure out how to create a calculation serial number.. I have a table with field of first name, and last name, and the date of when patient first starting seeing the doctor.  I want the serial number to equal the first 3 letters of the last name and first 2 letters of first name and the two digit year (15 etc) when they joined followed by a zero.   Is this doable?? 

       

       

       

        • 1. Re: Custom Serial number  by Calculation
          gsokolsky

          I think something like this should work:

          Left ( last name ; 3 ) & Left ( first name ; 2) & Right ( Year (date field) ; 2 ) & "0"

           

           

           

          • 2. Re: Custom Serial number  by Calculation
            RobertT

            Awesome thanks!!

            • 3. Re: Custom Serial number  by Calculation
              philmodjunk

              This does not sound like a good idea. What you describe cannot be guaranteed to be unique--something that could have catastrophic consequences for a patient's medical records. Even if you validated to ensure uniqueness, you complicate the design of your database to support using this field for anything other than a supplementary means of identifying patients.

              It's much simpler to use a simple serial number internally to identify each record and to use the patient's name and other info in your interface design when you need to pull up that patient's info.

              • 4. Re: Custom Serial number  by Calculation
                RobertT

                Thanks  I see the design flaw... right now I'm doing a testing database... but will inform the doctor that this combination wouldn't be an advantage over a unique serial number string

                • 5. Re: Custom Serial number  by Calculation
                  RobertT

                  And the doctor said he's been figuring out how to make a unique identifier to a patient's record

                  • 6. Re: Custom Serial number  by Calculation
                    philmodjunk

                    A simple serial number is usually best. A UUID string generated by Get ( UUID ) also works. The doctor and his staff need never see this ID, it's for internal use to link records in relationships. The users should be able to search out records by name, but with safe guards built in that identify and warn the user of duplicate names that then present the user with enough supplementary info for them to distinguish between patients.

                    You might be interested in "Adventures in FileMaking #2 - enhanced value selection" as one of the topics it tackles is the need for good use of Primary Keys and yet still producing a system with a user friendly, error resistant interface design.

                    • 7. Re: Custom Serial number  by Calculation
                      RickWhitelaw

                      I believe Phil is saying that it's a bad idea to use such a custom identifier as a primary key and I agree wholeheartedly. A meaningless serial ID should identify each record. However there's no reason you can't also have an identifier as you describe. A reason for this might be to have a code that is easily readable by humans and may be a requirement that has been sent your way. Just don't think of using as a match field for all the reasons Phil has already mentioned.

                      • 8. Re: Custom Serial number  by Calculation
                        philmodjunk

                        Which is why I mentioned earlier that it might be used as a supplementary identifier. wink

                        But except for very limited uses--such as a small label that might need to be printed and affixed to something, I would not use this even for that purpose as it is simpler and far more user friendly to build your interface around using a person's name to find their main record and then the ID is used to pull up all supplementary documentation linked to that patient's main record.

                        • 9. Re: Custom Serial number  by Calculation
                          RobertT

                          I'm just following doctors orders lol ...  He's been trying for 3 years to create a unique record number/caluculation...  But right now it's all in trial mode.   So i have a separate table that has progress notes... He wants me to input the unique identifier to correlate a caculation string of codes that would calculate an amount for different codes (this I already did) but I cannot seem to get the  ID of the main layout table to match an IDfk of the second layout table.  I'm getting multiple matches of the first table to different identifications to the second table.. what am I doing wrong here?

                          • 10. Re: Custom Serial number  by Calculation
                            RobertT

                            Nevermind I figured it out.... needed to make it a text field not a number field

                            • 11. Re: Custom Serial number  by Calculation
                              philmodjunk

                              Hmmm, but just as you shouldn't do your own "doctoring", your doctor shouldn't do his own "databasing". Leave "doctor's orders" for things in which the doctor is an expert.

                              And yes, the hardest (and sometimes riskiest) thing to do is tell a strong willed client that their design ideas are not good, but it's in his own best interest if you can gently point out that there are major reasons for trusting an expert to BE that expert for what he hired them to be.