2 Replies Latest reply on Jun 22, 2017 1:40 PM by siplus

    If Then inside Loop Script

    clayhendrix

      I have created a script. I want it to mark records in FAMILES for which there are 3 or fewer records in GUARDIANS. FAMILIES and GUARDIANS are table occurrences that are linked with a one to many relationship with one record in FAMILY having any number of records in GUARDIANS.

       

      To prepare for next school year, the registrar asked me to find records in FAMILIES for which there are =< 3 records in GUARDIANS, or in her words, find which families have 3 or fewer contacts, in the case of emergency.

       

      I first created a summary field which is a count of the pk field in GUARDIAN and I placed that summary field on the Family layout. Works great. Of course, you cannot search summary fields, and I understand why. So I found I solution on here indicating that I should create a script which looks at each record in FAMILY and omits those with >3 related records in GUARDIAN.

       

      What I knew then, and am more certain of now, than ever, is that I cannot properly functioning scripts with loops, especially those with IF, ELSE, THEN statements.

       

      I set out to create a script that would OMIT records in the FAMILY table with any value >3 in the summary field that counts related records from GUARDIAN. Then, I thought it would be helpful to the user if that same script modified a field in FAMILY based upon the =<3 related Guardians, so she would not have to wait for the script to run every time she came back to this work.

       

      I does not work. It does OMIT some records in FAMILY with >3 related records in GUARDIAN, but it also fails to OMIT some records in FAMILY with >3 related records in GUARDIAN, and I do not know why.

       

      I know someone can help and I know based on my years of activity on this forum that someone will. I do plan to learn something.

       

      Attached is a screenshot of the current iteration of the script (there have been many).

       

      Thank you, in advance.

        • 1. Re: If Then inside Loop Script
          erolst

          1. You can create a calculation field in Family as Count ( Guardians::pk ), and search that field.

           

          2. Your script always goes to the next record, regardless of a previous omission. You need:

           

          Loop

            If [ condition ]

              Set Field [ flag ]

              Omit

              Exit Loop if [ Let ( f = Get ( FoundCount ) ; not f or f ='Get ( RecordNumber ) ) ]

            Else

              Go to Record [ next ; exit after last ]

            End if

          End Loop

           

          3. But if you flag the matching records anyway, it is conceptually much easier to

           

          Loop

            If [ condition ]

              flag

            End if

          End Loop

          Find flagged

           

          or just

          Replace Field Contents [ flagField ; Calculation: If ( condition ; flag, "" ) ]

           

          4. Note that you can clear a field across the found set by using Replace Field Contents with a calculated result of ""

          • 2. Re: If Then inside Loop Script
            siplus

            You might solve your problem with a self relationship and a calc field that does a count(selfRel::PK).