5 Replies Latest reply on May 13, 2010 10:57 PM by Shruti

    Re: Go to related record automatically if unique ID already exists

    Shruti

      Title

      Re: Go to related record automatically if unique ID already exists

      Post

      Hi,

       

      I'm doing some work for an insurance company. All the records in the database are identified by a unique policy/agency number. My boss wants to update this database with every new document. When he inputs the policy number, it is flagged as an existing value and the user is asked to input another. Instead, my boss wants the existing record to show up automatically so he can modify it easily.

       

      I'm using FM Pro 10. I'm fairly new to FM, and learning on the go. How do I do this? I've never actually written a script yet, so if that's the way to do it a nudge in the right direction would be useful.

      Else, is there some way of using the find screen? Type in number, if exists -> go to record, if not -> creates new record... something like that?

       

      Thanks!

        • 1. Re: Re: Go to related record automatically if unique ID already exists
          philmodjunk

          With Filemaker 10 and 11 you have a feature called a "script trigger" that can be used to perform a script when a specific event occurs. In your case you could set up a trigger to perform a script when the policy number field is exited.

           

          I'm assuming a relationship is being used to "flag" your duplicate records. Let's further assume it looks like this in your relationship graph:

          Policy:: PolicyNumber = SamePolicy:: PolicyNumber (SamePolicy is a second table occurrence of Policy)

          Hmm, let's modify that and I'll explain why at the end.

           

          Change it to be:

          Policy::gPolicyNumber = SamePolicy:: PolicyNumber  (Make gPolicyNumber a new field with global storage specified.)

           

          Your script would look like this:

          If [ IsEmpty ( SamePolicy:: PolicyNumber ) ]

             New Record/Request

             Set Field [Policy:: PolicyNumber ; Policy::gPolicyNumber ]

          Else

             Enter Find Mode[]

             Set Field [ Policy:: PolicyNumber ; Policy::gPolicyNumber]

             Perform Find[]

          End IF

           

          Why the global field: Since you don't know if the policy number is a duplicate until you enter it, you don't want to create a new record until after the system has checked for duplicate for you. A global field will allow you to enter a policy number first then the triggered script will run and either find the policy or create a new policy record.

            

          • 2. Re: Re: Go to related record automatically if unique ID already exists
            Shruti

            What mode should that script operate in?

             

            As I understand it, it allows the user to input something into the policynumber field, and once that is exited it performs the script. In browse mode, that would be editing an existing record.. so I would normally click new record and then input.

             

            Is this supposed to work in find mode?

            • 3. Re: Re: Go to related record automatically if unique ID already exists
              Shruti

              I played around for a while and figured it out, I believe.

               

              As in normal data entry, I click New Record and type something into the PolicyNumber field. Once it exits, a script like below runs:

               

              Set Field[Policy:: gPolicyNumber ; Policy:: PolicyNumber ]

              If[Policy::gPolicyNumber=SamePolicy:: PolicyNumber ]

                 Delete Record/Request

                 Enter Find Mode[]

                 Set Field [ Policy:: PolicyNumber ; Policy::gPolicyNumber]

                 Perform Find[]

              End If

               

              It works fine so far with my test data. Thanks so very much for your help! I also have a fair idea of how scripts work now. :smileyhappy:

               

              • 4. Re: Re: Go to related record automatically if unique ID already exists
                philmodjunk

                If you are using a global field, you can enter the number without starting a new record. Then the script will create one for you only if the entered value is unque.

                 

                If you create a new record and then trigger this script, you will end up with an extra blank record.

                • 5. Re: Re: Go to related record automatically if unique ID already exists
                  Shruti

                  I'm doing it backwards, apparently. I enter data directly in the policy num field, and use the global field only for searching. The Delete Record/Request command removes the blank record before opening the existing one.

                  I set it up already, and it works perfectly. Thanks so much!