4 Replies Latest reply on Jun 7, 2017 5:37 AM by PeterChen

    Need Help for Find record script

    PeterChen

      I have 2 tables 'Employer' and 'Worker' and a field named "NationalityPref" in Employer table.

       

      'Employer' and 'Worker' tables are related with a field named "EmpCode"

       

      How should I write and structure the Find Script to do the following?

       

      I want that:

      if "NationalityPref" field in Employer table= Indian, find records in Worker table where "Nationality" field=Indian;

      if "NationalityPref" field in Employer table= Filipino, find records in Worker table where "Nationality" field=Filipino and so on...

       

      I'm quite lost with my FMPro 14 as I've been using version 5 for far too long:(

       

      Thank you very much!

        • 1. Re: Need Help for Find record script
          erolst

          You can use a script like this, utilizing the existing relationship:

           

          Set Variable [ $nationalityPref ; Employer::nationalityPref ]

          If [ IsEmpty ( $nationalityPref ) or IsEmpty ( Worker::EmpCode ) ]

            Exit Script

          End If

           

          Go to Related Record [ from Worker ; matching only ; current only ]

          Enter Find Mode [ pause: off ]

          Set Field [ Worker::nationality ; $nationalityPref ]

          Set Error Capture [ on ]

          Constrain Found Set [ restore: off ]

           

          To see at a glance if there any matching workers to be found, you could use a portal on the Employer layout that points at Worker, and add the filter expression

           

          Employer::nationalityPref = Worker::nationality

          • 2. Re: Need Help for Find record script
            fmpdude

            Method 1: One way to do this is to setup a relationship between your two tables like this (here, you don't really need to do a Find at all -- just have possibly a special relationship do the work for you.):

             

            Here you're matching on both EmpCode and Nationality Preference so, if you had a portal to show the "M" side of the relationship (worker), you would only see matching records:

             

            -----

             

            Method 2:

             

            Just have a relationship on EmpCode between your two tables, but do a Find in the child table, via the Worker layout (Note, Finds don't work as you might expect in the portal itself).

             

             

            -----

             

            Method 3:  Use SQL (below, initially prototyped using external tool).

             

            Select w.NationalityPref, w.Empcode from worker w, employer e where
               e.EmpCode = w.EmpCode and
               w.NationalityPref = 'Indian'

             

            Gives you...

             

            ---------

             

            (THEN, take working SQL to Data Viewer to verify...)

             

             

            ---

             

            And as erolst showed above, it's straightforward to script however you choose to approach the problem.

             

            HOPE THIS HELPS.

            1 of 1 people found this helpful
            • 3. Re: Need Help for Find record script
              PeterChen

              Wow!

               

              Thank you very much, erolst and fmpdude!

               

              I will look at both your invaluable tips tomorrow.

               

              Thanks again!

               

              Blessings!

              • 4. Re: Need Help for Find record script
                PeterChen

                Thank you very much again, erolst and fmpdude for your kind effort!

                 

                I've spent some hours figuring out and found another way around which I could better handle.

                 

                Best regards!