4 Replies Latest reply on Jun 28, 2017 7:08 PM by user19752

    SET A FIELD - CONDITIONAL TO FIELDS IN A PORTAL (Test all Rows)

    rickmerl

      SET A FIELD - CONDITIONAL TO FIELDS IN A PORTAL (Test all Rows)

       

      I have a portal with 6 fields per portal row. The fields are organised as 3 pairs

      Pair1 - Field_A1 (text)    Field_A2 (date)     Field_A2  Placeholder text  when empty = “Insert Date”

      Pair2 - Field_B1 (text)    Field_B2 (date)     Field_B2  Placeholder text  when empty = “Insert Date”

      Pair3 - Field_C1 (text)    Field_C2 (date)     Field_C2  Placeholder text  when empty = “Insert Date”

       

      I have a single field on my main database page called:

      ABC_Indicator (text)

       

      I want to set ‘ABC_Indicator’ to “X”, when the following conditions are met:

      When  Field_A1 is not empty, and  Field_A2 is empty

      and/or Field_A1 is not empty, and  Field_A2 is empty

      and/or Field_A1 is not empty, and  Field_A2 is empty

      (Where any one (or more) of the date fields  (..A2, ..B2, ..C2) are empty, whilst their corresponding text field (..A1, ..B1, ..C1)  is not empty)

       

      I have created a script (Set Indicator Field). And it all works fine when there is only one portal row.

       

      MY ISSUE: Occurs when there are multiple portal rows.

      For Example:

      First Portal Row      - One or more date fields are empty, whilst their corresponding text field is not empty.

      Second Portal Row - One or more date fields are empty, whilst their corresponding text field is not empty.

      The ‘Set Indicator Field’ is correctly set as “X”

       

      What happens: I input a date into one of the empty date fields in the second portal row, and ‘ABC_Indicator’ is cleared.

      What should happen. ‘ABC_Indicator’ should continue to be set as “X” whilst any  (X1=1 & X2= 0) condition exits in any portal row.

       

      Im hoping someone can help. (Im still consider myself as a learner, so a descriptive reply or example script will save effort in my asking “What do you mean?”)

      ____________________________________________

       

      My Script: Set Indicator Field

       

      If          If (

                  Portal::Field_A1 > "";

                  Portal::Field_A2 < "1"

                   )

       

                  or

       

                  If (

                  Portal::Field_B1 > "";

                  Portal::Field_B2 < "1"

                  )

       

                  or

       

                  If (

                  Portal::Field_C1 > "";

                  Portal::Field_C2 < "1"

                  )

       

            Set Field [ MainScreen::ABC_Indicator ; “X”

       

      Else

            Clear [select ; MainScreen::ABC_Indicator ]

       

      End If

      ____________________________________________

       

      My script is executed via each date field trigger (OnObjectModify)

        • 1. Re: SET A FIELD - CONDITIONAL TO FIELDS IN A PORTAL (Test all Rows)
          philmodjunk

          I'm not sure that the expected outcome is completely clear.

           

          Say you have 3 records in the portal, that makes for 9 pairs.

           

          Do you want an "x" result if any one or more of those 9 pairs shows as first member not empty and second member empty?

           

          So the field would only clear when:

           

          none of the 9 pairs is first not empty, second empty?

          1 of 1 people found this helpful
          • 2. Re: SET A FIELD - CONDITIONAL TO FIELDS IN A PORTAL (Test all Rows)
            rickmerl

            Hi Phil..

            Thanks for replying

             

            In response to your question:

            I want "x" to be set, if ANY pairs (regardless of the portal row) meets the condition of [..text1 = 1, and ...date2 = 0]

             

            It works fine for the first row.

            However, if I set the date on any row other than the first, it ignores that the condition still exists with one or more pairs on other rows.

            And clears the "x"

            • 3. Re: SET A FIELD - CONDITIONAL TO FIELDS IN A PORTAL (Test all Rows)
              philmodjunk

              When you set up a calculation to refer to data from a related table, except for aggregate functions and a few other exceptions, your expression only references the data in the first related record.

               

              But [..text1 = 1, and ...date2 = 0] is NOT the same as: Field_A1 is not empty, and  Field_A2 is empty

               

              That leaves the actual criteria that you want to use a major question mark.

               

              To get the results you want, there are several possible approaches:

               

              Use Go To Related Records to pull up a found set of these records, you can do this in a window hidden off the edge of the monitor, and loop through them, exiting when you encounter one pair that matches your criteria or when you reach the last pair of the last record.

               

              You can loop through the portal rows by using go to portal row. This is "brittle", minor layout changes can keep it from working, but it can be gotten to work.

               

              You can use ExecuteSQL, to build a list of these value pairs and loop through the list.

              • 4. Re: SET A FIELD - CONDITIONAL TO FIELDS IN A PORTAL (Test all Rows)
                user19752

                Another simple way may be adding a calculation field in portal table,

                ( not IsEmpty(A1) and IsEmpty(A2) ) or

                ( not IsEmpty(B1) and IsEmpty(B2) ) or

                ( not IsEmpty(C1) and IsEmpty(C2) )

                then calculate on Indicator, Case(Sum(Portal::theCalc) ; "X")