4 Replies Latest reply on Sep 10, 2013 10:37 AM by DranLang

    Check If Record Already Exist Before Allowing to Add a Record Number

    DranLang

      Title

      Check If Record Already Exist Before Allowing to Add a Record Number

      Post

           Good morning everyone!  I have a question regarding verifying if a record number already exist before it allows a user to add record.  It could be easy for me if I could use Serial Number but my boss specifically told me that it needs to be manually done. 

           Here's how he wants it to work.  When a number is entered it needs to verify first if that record number already exist and if it does, we need to tell the user to try again before it can proceed.  If the number entered hasn't been used yet, the layout where adding record will appear and the number that was entered will automatically be assigned to the field where it should be.

            

           I've written a script but for some reason sometimes it works and sometimes not, and I'm not sure if my script is correct since I'm relatively new to FileMaker script writing.  Below is the script I wrote, any input/ help to make this script work is highly appreciated.

            

           Copy[Select; Table1::ID_LookUp]

           If{IsEmpty(Table1::ID_LookUp)]

                Show Custom DIalog["MESSAGE";"MESSAGE AGAIN"]

                Go to Layout [original layout]

           Else If(Table1::ID_LookUp = Table2::ID#)

                Show Custom DIalog["MESSAGE";"MESSAGE AGAIN"]

                Go to Layout [original layout]

           Else

                Go to Layout ["Lay_RECORD" Table2)]

                Set Variable [$ID_Temp; Value:Table1::ID_LookUp]

                Go to Record/Request/Page [Last]

                New Record/Request

                Set Field ["Table2::ID#; $ID_Temp]

           End If

            

            

        • 1. Re: Check If Record Already Exist Before Allowing to Add a Record Number
          philmodjunk

               WHY does your boss insist on a manually entered number? That seems like a recipe for really frustrating a user as they try possibly many times to enter a unique number and fail repeatedly. Could you use a portal to list existing values in sorted order so that the user can enter a value not on the list?

               Am I correct that you have this relationship between table 1 and 2?

               Table1::ID_LookUp = Table2::ID#

               If so, then this expression:

               IsEmpty ( Table2::ID# )

               will be true if the number in ID_LookUp does not match any value in any record of Table2::ID#.

                

          • 2. Re: Check If Record Already Exist Before Allowing to Add a Record Number
            DranLang

                 For some reason the people working in the other department argued that their people have been doing it for years already and don't want to change anything and my boss bought it.  My boss gave me a layout design that the other department wanted to have and there's no space for portal.   The relationship between the two table is Table1::ID_Num = Table2::ID#, I'm just using the ID_LookUp field in the interface.  The idea is to check first the value entered by the user in the ID_LookUp Field in the GUI exist already in Table2::ID# and if not it will bring the user to the Layout where they can continue entering data and Table1::ID_Num will automatically populated by the value entered by the user (by Set Field(Table2::ID#; $ID_Temp).

            • 3. Re: Check If Record Already Exist Before Allowing to Add a Record Number
              philmodjunk

                   Make a new table occurrence of table 2. Link it to Table 1. so that you have:

                   Table 1::ID_lookup = Table 2|LookUP::ID#

                   Then you can use the method I have recommended by using:

                   IsEmpty ( Table2|LookUP::ID# )

                    to check for an existing value.

                   You might want to demo an alternative to this if you think office politics will give you a reasonable chance to do so. Sometimes SEEING the change in action will change minds otherwise locked on "we've always done it this way".

              • 4. Re: Check If Record Already Exist Before Allowing to Add a Record Number
                DranLang

                     Thank you Phil, I'll sure do what you told me about the alternative so that other people would really see it clearly how things work.  Again, thank you so much!