3 Replies Latest reply on Mar 27, 2013 5:47 PM by LaRetta

    Existing Record Prior to New Record


      I am looking for a cleaner or easier way to do this...


      I have a file that has the following fields....


      First Name (text)- Last Name (text) - Name Validation (calculation just combines the two names)


      currently I have when a user selects new record it enters into a find mode

      a trigger activates after leaving the last name field and does a search if results are found it gives you the options to enter new or use existing and ect... but if a user enters select new contact

      it does a copy of the first name field and creates a new record then does a paste to first name field. This makes the user re-enter the last name.


      What I am looking for is the ability to Create a New Record trigger the last name to do a search if found delete the new record and go to the selected found record. My hang up is the deleting of the New Created Record with the name that already exists. I can't revert record because of the search. Any ideas or better ways to do a pre-search/


      Thanks in advance!

        • 1. Re: Existing Record Prior to New Record

          "What I am looking for is the ability to Create a New Record trigger the last name to do a search if found delete the new record and go to the selected found record.  "


          Hi Orangej,


          If the concatenation were auto-enter ( replace ) instead of calculation then you can apply field validation, even trapping the result OnObjectTrigger OnObjectValidate and Reverting the new record and proceeding to the existing one.


          But why not use global fields, search the results, if more than one record go to a list view with their details, if one record returned go to form view and if no records, create a new record and set the fields with the global values?  Also if 12, you can check for the existance of the customer using ExecuteSQL() immediately before creating the new record.


          Corrected script trigger and added sentence about ExecuteSQL()


          I was on ipad and couldn't correct the post properly.  please FMI allow ipad edits. :^)

          1 of 1 people found this helpful
          • 2. Re: Existing Record Prior to New Record



                 Thank you for the reply...   I feel pretty silly but I am not sure how to trap the result for the validation could you please explain a little on that.  If I use global fields then I have to add those fields in addition to the existing First and Last Name fields so the user isnt entering directly into those fields it might cause confusion.  Am I missing something?


            Thanks for your time on this

            • 3. Re: Existing Record Prior to New Record

              Hi Jim, :-)

              You will not want to use field validation on FirstName & LastName alone because that would mean that two Bill Smiths could not both be clients but it is important that guaranteed-duplicates are refused whenever possible.  You have really two issues, 1) stop exact duplicate people and 2) find/create new clients in User-friendly manner.  And I would add - less chance of error.


              As for using globals, splitting the find process from the data-entry is known as 'dedicated find layout' and many developers use it particuarly for more advanced finds but also because it is *safer but it takes more work and I should not cover that aspect here other than to reference it below.  So let's proceed with the concept of performing the find on your data-entry layout - user clicks your button and enters name and then script takes over.  And then secondly I will show how to add tight validations at the field level.


              The Find/Create New script:

              # based upon User entering in FirstName and LastName fields

              Enter Find Mode [ Pause ]

              #  user types in first and last name into their standard fields on regular Data-entry layout.

              Set Error Capture [ On ]

              #  this suppresses FM's error message and allows you to handle it yourself

              Perform Find [  ]

              If [ not Get ( FoundCount ) ]

              #  No matching record.  Create new record and stay here to data-enter.

              New Record/Request

              Modify Last Find ... this is a cheat to grab entered data for re-use by you

              Set Variable [ $firstName; Value:Data::FirstName ]

              Set Variable [ $lastName; Value:Data::LastName ]

              Enter Browse Mode

              Set Field [ Data::FirstName; $firstName ] ...it is using the find criteria entered to fill the fields

              Set Field [ Data::LastName; $lastName ]

              Show Custom Dialog [ Message: "New record created for " & Data::FirstName & " " & Data::LastName; Buttons: “OK” ]

              Else If [ Get ( LastError ) = 400 ]

              #  No find criteria entered.  Do nothing

              Exit Script [  ]

              Else If [ Get ( FoundCount ) > 1 ]

              #  Display list for User to select the right person

              Go to Layout [ “List View” ]


              #  No action required - stay here to work on this single found record  :^)

              End If



              Please note in my file on the ID field and the calculation that I have unchecked in Inspector > Data ‘allow field entry in find’.  This is an often-overlooked step if folks allow finding on data-entry layouts and it can really slow a network when folks start searching on fields such as unstored calculations so uncheck all you do not want them to search.


              The field validation for unique ( using FirstName, LastName and Phone ).  This will do two things, plant your calculation as actual data and set up for unique validation on that new data:

              1. Back up first and then change your calculation to include the phone, something like:
                • FirstName & “ “ & LastName & “ “ & Phone
                • This is a perfect field to use for right-hand display of value lists etc as well.
                • You cannot use just phone for unique because two people can share the same phone ( yes even in today’s world, LOL )
                • Select your calc and go to Options and then Storage Options.  If it is checked 'do not store calculation results' then uncheck it.  If it will not let you then it means you have fields from related tables and/or it includes unstored calculation or global fields.  If it will not let you uncheck it, STOP.  If unstored, it will not work.
              2. So unstored is NOT checked and we can proceed.
              3. Back at your fields tab in Manage Database , change the calc to type TEXT and select ‘change.’
              4. Go into Options and Auto-Enter tab and click ‘specify’ on ‘calculated value’.  Your calc is still there.  Below in the dialog make sure it is checked ‘do not evaluate if all referenced fields are empty.’
              5. Say OK and back at the auto-enter tab, be sure that ‘do not replace existing value’ is UNCHECKED.
              6. Say OK and then OK to exit completely out and back to your layout.  This has planted the calculation as actual data into your records.
              7. Now go back in and go to its validation tab and specify UNIQUE and select 'always' if you might import and want to skip duplicates always or select 'during data entry'. Again, there are many possibilities in handling this process and I do not have enough requirements to pin it perfectly for you.

              If you tighten up your system so finds can only be performed using your script then that should be all you need.  The validation will protect if in some way your system is bypassed.  If you have version 12, you can simply use ExecuteSQL() to check if the client exists before creating a new record.  That would save ‘search’ time if that client didn't exist which would certainly be worth using.  Version 11 file attached (can be converted to 12 by dragging to 12 icon or opening in 12). 


              I realize I provided quite a bit of information, Jim, and my apology if a bit much but there are many approaches and subtle differences in what we might suggest depending upon very specific requirements and, since we don't really have much to go on, I took my best ( safest and most likely ) stab at it.  So if I'm incorrect, just fill me in and we'll get you there, okay?  And if typos or something, I really DO try but I am human.


              One must log in to download the attachment.


              *  Refrence to being unsafe ...  If somehow a user drops out of find mode on a regular data-entry layout then they might mistakinly think they are still in find and change a client's REAL data.  So if you must use regular data-entry, use techniques to change the layout ... I didn't add that part in my file.  But thats another reason it isn't quite as safe.  Also,
              this script is only finding by two fields.  If you want system to use more fields, you will need to modify the script by adding the variables and set fields between the 'modify' and the 'browse' ... they are easy to do.


              Message was edited by: LaRetta replaced file forgot to include the validation, LOL.