5 Replies Latest reply on Jun 23, 2017 3:16 PM by jbryan

    Field Calculation for child record by ID and boolean field

    jbryan

      I'm relatively new to FM but here's an attempt at a question I can't seem to find answers for...

       

      BACKGROUND

      Duplicated an icon and set unique colors on each with different "Hide Object When" calculations. The calculations look at a specific field value in the current record. Right now that field is a drop-down manually set by the user. To help illustrate, the field is called "Photoshoot Needed". The values are "NA", "Needed", "Scheduled". The icon is gray for "NA", gold for "Needed" and black for "Scheduled".

       

      THE GOAL

      The same layout includes a portal to a child table; "Milestones". All three values of "Photoshoot Needed" can be determined by records populated in Milestones.

      – If there is a child record with "ID" field of 62, the "Active" field is true and the date field is NOT empty, the value should be "Scheduled".

      – If there is a child record with "ID" field of 62, the "Active" field is true and the date field is empty, the value should be "Needed".

      – If there is a child record with "ID" field of 62, the "Active" field is false, the value should be "NA".

      – If there is NO record in that portal with "ID" field of 62, the value should be "NA".

       

      Can I rewrite the "Photoshoot Needed" as a calculation field that will automatically set it's value according to these criteria? If so, my color coded icons would show status without the step of the drop-down picker for users (which is redundant since they have to create the Milestones records).

       

      SOLUTION

      I assumed I could use SQL but my problem seems too complex to work as a calculation field...or maybe just over my head to write. I can think of other ways to do this but a calculation field seems the cleanest. I welcome any feedback.

        • 1. Re: Field Calculation for child record by ID and boolean field
          philmodjunk

          What is the significance of ID = 62? Is that the ID of the parent record that then matches to a field in the child table?

           

          I will assume that this is the case.

           

          The problem is that you can have multiple child records. Any calculation, using ExecuteSQL or other aggregate function would need to check for those values in any of the related records. ExecuteSQL would seem a good choice for this though it is possible to add a calculation field to the child table that you can then check for values via FilterValues and List if you want to go that route.

           

          But you might have more than one child record and each might match your criteria differently. What should happen if you have one record with "active" and a date and another record with "active" and no date?

          1 of 1 people found this helpful
          • 2. Re: Field Calculation for child record by ID and boolean field
            erolst

            jbryan wrote:

             

            Can I rewrite the "Photoshoot Needed" as a calculation field that will automatically set it's value according to these criteria?

            Try

             

            Let [

              has62 = not IsEmpty ( FilterValues ( List ( ChildTable::id ) ; 62 ) ) ;

              isActive = yourActiveField ; // assuming this field holds a 1 for True or a 0 / is empty for False

              hasDate = not IsEmpty ( yourDateField )

              ] ;

              Case (

                not Has62 ; "NA" ;

                not isActive ; "NA" ;

                not HasDate ; "Needed" ;

                "Scheduled"

              )

            )

            1 of 1 people found this helpful
            • 3. Re: Field Calculation for child record by ID and boolean field
              jbryan

              No, the 62 value is the "Milestone Type ID" field from the child record. It's specifically a Milestone called "Photo Shoot" in the Milestones table. It will only show up ONLY once in the child record on this parent record. On other parent records it can show up ONLY once there too.

               

              I'll take a look at erolst's post now too...not sure if this clarification helps there too...

              • 4. Re: Field Calculation for child record by ID and boolean field
                philmodjunk

                Then your two basic options are ExecuteSQL or an added relationship that uses two pairs of match fields to narrow the set of related records to just those with that ID.

                 

                You can leave your current relationship unchanged by adding another occurrence of the child table. The match  field in parent can be a calculation field that always has the value 62.

                1 of 1 people found this helpful
                • 5. Re: Field Calculation for child record by ID and boolean field
                  jbryan

                  I went with the table occurrence route and it's working. Followed your instructions and used a version of erolst's suggested calculation.

                   

                  Let ( [ has62 = not IsEmpty ( Milestone Photo Shoot ID::ID ) ; isActive = Milestone Photo Shoot ID::Active ; HasDate = not IsEmpty ( Milestone Photo Shoot ID::Scheduled Date ) ] ;

                  Case (

                      not has62 ; "NA" ;

                      not isActive ; "NA" ;

                      not HasDate ; "Needed" ;

                      "Scheduled"

                    ) )