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

    Need Help for Find record script


      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

          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

            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



              Thank you very much, erolst and fmpdude!


              I will look at both your invaluable tips tomorrow.


              Thanks again!



              • 4. Re: Need Help for Find record script

                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!