9 Replies Latest reply on Jun 23, 2009 12:43 PM by RickWhitelaw

    Script vs. formula

    RickWhitelaw

      Title

      Script vs. formula

      Post

      Hi,

      In the "Contacts table of my solution I use "Short Name" as a code and match field for several other tables. This five character name code is also a convenient shorthand in a few other layouts. Although it works fine, I'm beginning to see that as the database grows, it might be advisable to automate the creation of Short Names. I currently make them up according to the usual practice. Here's the bare bones (only) of a formula that would automate the process:(field would be stored as text-auto enter)

       

      Case(Length(Last Name) ≥ 4; Left(Last Name;4)& Left(First Name;1); Length(Last Name)  =  3;Left(Last Name;3)& Left(First Name;2);Length(Last Name)  = 2; Left(Last Name;2)& Left(First Name;3)) 

       

      This would return "SMITJ" for John Smith, "SIMJE" for Jean Sim, and "KOSTE" for Stephanie Ko. The snag is due to the validation requirement that each Short Name be unique of course. As far as I can tell, there's no use for Get(LastError) in a formula, so I couldn't check validation automatically and capture the error. I am aware that I can trigger a script On Commit that would accomplish what I'm trying to do. I was wondering if there's a way to avoid this. To be clear, "John Smith" would evaluate identically to "Joan Smith". By capturing the error (504) a script would eventually evaluate these as "SMITJ" and "SMJOA". Is there a way to accomplish this as an auto-enter operation?

      I'm tempted to leave things as they are. I tend to remember character combinations I create myself. However this could change with the addition of a couple of hundred names. Given that a five character alpha only code can produce over 11,000,000  combinations, it seems that a five-character code, with its creation well automated, is more than adequate.

       

      Thanks. 

        • 1. Re: Script vs. formula
          raybaudi
            

          Hi

           

          "I use "Short Name" as a code and match field for several other tables"

           

          Do not use that any more.

           

          Instead use IDs ( autoenter serial ) as your primary key.

           

          In that way you haven't to worry for dupes on your Short Name calculation, neither it needs to exist any more ( you'll be able to see the REAL FullName )

          • 2. Re: Script vs. formula
            RickWhitelaw
              

            Hi,

             

            I know what you mean and of course it makes sense. However the Short Name method is quite valuable as a "shorthand" and is recognizable as opposed to a serial. It needs to be typed in originally only once and poses no risk to data as it must be unique. The program later uses it to concatenate other IDs based on project and position. It all works well, and I suspect I'll never have more than a couple of thousand entries in the DB so, if there's no way to force validation in a formula, I'll leave things as they are or script the process.

             

            RW 

            • 3. Re: Script vs. formula
              raybaudi
                

              You said:

              "John Smith" would evaluate identically to "Joan Smith"

               

              Every alternate way to write a 5 chars string do not let you know for sure who is who.

               

              But:

               

              Id        FullName      Short
              1       John Smith    SMITJ
              2       Joan Smith    SMITJ

              will !

               

              And there is the solution to have not to remember serials.

              • 4. Re: Script vs. formula
                RickWhitelaw
                  

                Yes,

                 

                They would evaluate identically. That's why the error 504 must be captured and an alternate Case scenario applied. "John Smith" would then evaluate as "SMITJ". Joseph Smith as "SMIJO" and Joan Smith as "SMJOA". I know how to make this happen by scripting. What I'm uncertain about is whether or not it can be accomplished as a calculation.

                 

                RW 

                • 5. Re: Script vs. formula
                  RickWhitelaw
                    

                  I've written a script that's triggered On Exit of field "Last Name" that assembles quite decent "short names" and handles a fair number of duplicate names well. It's not robust enough for the Census Bureau (they would have thousands of duplicate names) but serves well in a solution with a few hundred to a few thousand names. I imported 10,000 records and it assembled short names for all. The best part is that, even with identical names, the "cipher" is somewhat recognizable. For the vast majority of cases, where a last name has 4 or more characters and assuming first and last name have a minimum of 2 characters, the result is quite "friendly". For hundreds of thousands of records of course the simple serial number key would be preferable, necessary actually, but I assemble orchestras . . . not standing armies, and I like the idea of using, as I mentioned, a "shorthand". I'd post the script but I think it's too long.

                   

                  RW 

                  • 6. Re: Script vs. formula
                    ninja
                      

                    Howdy Rick,

                     

                    An interesting debate...whether a shorthand name is adequate in some cases and not in others.  I assume you wrote your last post to continue the dialog so I figured I'd post back.

                     

                    Daniele is most correct that SerialID# is a safer and more 'bulletproof' method for relating your tables (as much as anything is 'bulletproof').  But at the same time, it is quite understandable why you would want a somewhat recognizable shorthand.

                     

                    Might I suggest that you can have the robustness (is this a word?) of using the SerialID# AND the recognizability.  They are not mutually exclusive.

                     

                    1. Link your tables using the SerialID#.  This is tried, tested, proven and reproven and very robust.

                    2. Put your ShortName on the layout right next to the Serial ID# for recognizability.

                    3. Consider whether you even need the SerialID# to appear on the layout at all...you probably don't.

                    4. Now you have the Proven SerialID# working for you AND the Shortname for you to recognize.

                    5. Consider further whether you even need the Shortname anymore...you could use the fullname...more recognizable, no?

                     

                    I guess my instinctive reaction is: Why build in a weakness when you can everything you're after without the weakness?  Just because you don't ever expect the weakness to cause a problem doesn't mean it won't...doesn't mean it will either.  The topic is a conceptual approach...sometimes good enough is good enough, but better is "good enough" more often.

                     

                    Just thinking with my keyboard...Enjoy the day.

                    • 7. Re: Script vs. formula
                      RickWhitelaw
                        

                      Hi Ninja,

                       

                      Yes, interesting! I should be clear: all the "child tables" in the Contacts module of my solution use serials. Address, phone numbers etc. There's no advantage to do otherwise. In an "equal?" table, and I'll give an example, I use this "short name" approach. In another file which basically assigns people to orchestra positions, the position itself has certain parameters independent of the person in the chair. To assign myself to a position I simply type "whitr" in the field. The program concatenates a code which runs through many other stages of the program, from substitutes to payroll and finally to year-end tax paperwork. Productions also have "short names". My position in We Will Rock You is represented as "WEWRY01whitr". The instance (week)  of that show  ending this week is WEWRY06/28/2009. In both these examples it's only necessary to assign, or automate, the short name once, and a serial might do as well. However every week on every show (I'm responsible for more than one at a time) musicians send in substitutes (subs) for various performances. This ends up being a lot of data entry and the "short name" strategy saves a lot of time. It's also bulletproof EXCEPT for one situation: when a user alters the short name in the original "contacts" DB. To counter this I use a warning script OnObjectKeystroke in the Short_Name field. It allows the user to move on without changes or continue modifying while putting up with the warning each of the five keystrokes. The script won't trigger if the keystroke is the Tab key. So, call me old-fashioned, but I get codes that have a human feel to them, and it's all but foolproof.

                       

                      RW 

                      • 8. Re: Script vs. formula
                        ninja
                          

                        Fine by me...(as if you would need my permission ;) )

                         

                        Your original question, in light of the ensuing discussion, was how to do something that is generally frowned upon due to the risks it opens up in larger databases.  Not surprisingly, the response was 'don't do that' along with reasons why not.  If you're sold on the approach... heck, it's your database.  Who's gonna stop ya?  You're essentially reinventing a Unique ID the hard way.

                         

                        While considering, consider this bullet for your 'bulletproof' testing:  You come down with mono and are out for a month, or much more pleasantly you decide to sail the Carribean for a month.  Who's gonna recognize all the shortnames and cocatenated "shortnameEventDateEnddaySubstitutionSeatInstrument" values?  With a SerialID, you can use fullnames and easily recognizable full words.  Then someone else can muddle through with a minimum of hair-pulling.  You don't want to get stuck as the only one able to use the system (assumption on my part).

                         

                        My two cents are used up...raybaudi has more to offer than I anyway, I just thought I'd offer an encouraging voice to the "you can have what you want but don't do it that way" tone.  'Cause after all, with FMP you CAN have what you want...just don't do it that way.

                         

                        Enjoy the day!

                        • 9. Re: Script vs. formula
                          RickWhitelaw
                            

                          Ninja,

                          Thanks again for your reply. I'm finding this interesting, the key field discussion. The user never sees, or has to see, all the concatenated codes. All that's done in the background. There are only a few situations where the user enters "short name" and is presented with a list alongside in case he/she doesn't twig on the 5 character code. The option of using the short name for data entry is a big time saver, as opposed to entering full names. In those few situations (in related tables) the field will only accept values from the "short name" field in the Contacts DB. I"ve further refined my OnObjectKeystroke script (attached to the Short Name field in its native table) and, believe it or not, it takes some real effort to make a mistake . . . someone could probably find a way to mess it up I suppose. There's no doubt in my mind that the serial number approach is the safest. The way I'm doing it now I gain significant convenience. Giving the user the option to choose a string which ends up being a match field seems to go against the prevailing grain. That's why I automated the process with a different script on Last Name OnObjectExit. 99% of the time the user has nothing to do with the process yet ends up with the advantage of a somewhat "human" code. When the program runs out of options the user can enter a short name. This only happens when there are many duplicates. As I mentioned (I think), I imported 10,000 names and the script assigned a unique short name to each one. Not a bad script, but very long, and I don't see a way of attaching it here. 

                           

                          Thanks,

                          Rick.