4 Replies Latest reply on Apr 30, 2017 9:36 AM by philmodjunk

    Refreshing a field from a different table

    cevans017

      I am so stuck.  I have a database that has two tables and they are related.

      Sample:    I want to get field 4, out of record 1, and go to the other table

      and see if it exists in any record of field 4.

      Ex:

       

      parent                      child

      field 4= e1004p          field4 =f444p    = no match

      field 4= e1004p          field4 =e1004p   = match (YES)

       

      I have an if condition in the child table but when it goes

      to the next parent record. the if field is not being

      updated to the new record information.

      Hope this is not confusing...

       

      Thanks in advance for any help.

       

      I have looked at Refresh, I have set variables but they do

      not come over either.

        • 1. Re: Refreshing a field from a different table
          TSPigeon

          cevans017:

           

          Thank you for your post!

           

          You could have a portal display related records if you just need to see what records match.

          If you actually need to change a field such as "status" you may need to have a script to find and set such fields on matching records.

           

          I'm going to move this thread from the FileMaker Community Feedback Space (which is specifically for input on the Community itself) to the Discussions Space where you should receive more views and potentially more feedback on this topic!

           

          TSPigeon

          FileMaker, Inc.

          • 2. Re: Refreshing a field from a different table
            philmodjunk

            As TSPigeon implied, you need a relationship.

             

            Parent::Field4 = Child::Field4

             

            A portal to child will then list any records with a matching value in Field 4. If you already have a different relationship linking the two tables, you can use the button with two green plus signs to duplicate the child table occurrence and use it for this relationship.

             

            The expression:

             

            Not IsEmpty ( Child::Field4 )

             

            will be true only if at least one child record has a matching value in Field4.

            • 3. Re: Refreshing a field from a different table
              cevans017

              Attached are the two  scripts that I built.  The FIRST pdf represents

              the first script and DRS represents the working engine to find and place

              "YES" in field 18 of the parent.  Field 1 of both tables I have related.

              The issue I have with this is that it is very slow.   1,000 records takes

              about 10 minutes.  Any help would be most appreciated.

               

              Thanks in advance

               

              ////First
              Go to Layout [ “DRS” (DRS) ]
              Go to Record/Request/Page
              [ First ]
              Set Variable [ $$trigger; Value:"NO" ]
              Loop
              Set Variable [ $$check ; Value:DRS::Field 4 ]
              Perform Script [ “DRS” ]
              Go to Layout [ “DRS” (DRS) ]
              If [ $$trigger = "YES" ]
              Set Field [ DRS::Field 18 ; "YES" ]
              Set Variable [ $$trigger; Value:"NO" ]
              Else
              Set Variable [ $$trigger; Value:"NO" ]
              End If
              Go to Record/Request/Page
              [ Next; Exit after last ]
              End Loop
              Go to Layout [ “BadReport” (DRS) ]
              Show All Records
              Perform Find [ Specified Find Requests: Find Records ; Criteria: DRS::Field 18 : “==” ]
              [ Restore ]
              Print [ ]
              Show All Records
              Go to Layout [ “Front” (DRS) ]

              ////DRS
              Go to Layout [ “lookup” (lookup) ]
              Go to Record/Request/Page
              [ First ]
              Set Variable [ $$check2 ; Value:$$check ]
              Loop
              If [ $$check2 = lookup::Field 4 ]
              Set Variable [ $$Trigger; Value:"YES" ]
              Exit Script [ ]
              Else
              Set Variable [ $$trigger; Value:"NO" ]
              End If
              Go to Record/Request/Page
              [ Next; Exit after last ]
              End Loop

              • 4. Re: Refreshing a field from a different table
                philmodjunk

                Can't make any sense out of that script. Why name fields with numbers instead of descriptive names?

                The script calls another script which you do not list.

                 

                Don't see any indication that you are using my suggestion.

                 

                Why put yes in that field? What is supposed to determine which records are so modified?