3 Replies Latest reply on May 12, 2017 2:38 AM by keywords

    Conditional Formatting based on a Feild Value in a Found Set on a Seperate Layout

    fratinize

      I have a Main layout with Buttons that Find a Set of Records, takes me to Its Layout and Allows me to Gather Data for those Records and Stored in its Own Table (Lets call it the Data Table). During Data Collection, I set a Flag to No (IN a Field called "Archived", in the Data Table)

       

      At the End of the Collection, I press a Button it gathers all of the Records with the "N" in the Archived Field) and It creates an Email with The Data, Then a Message Pops Up saying DI you send the Email? then Once the User clicks OK to the Message, it converts the Archived Flag to "Y"

       

      On My Main Layout I want to Make a Note basically to Remind the User to Send the Data Collected (If it has not been) Otherwise  IF its been sent, You will not see the Note.

      So I Created a Text Box With the Words Please Send Your Data) I made the Font white so It blends with the background and is unseen, I want to Use Conditional Formatting to Make it Brite RED if The archived Flag is Still "N"

      I get how to Use Conditional Formatting, but Have Not Attempted to Do this on a Different Layout than Where my field/Data is.

       

      I am Not Getting how to Write the Formula for the Condition. I understand the Logic, just not the Syntax.

      Anybody?

      Thank You

        • 1. Re: Conditional Formatting based on a Feild Value in a Found Set on a Seperate Layout
          jamiebah

          With conditional formatting, any non 0 value will be true and will activate the rule

           

          Example 1:

          Use a global variable ($$ before variable name) , store a value in it, and use that for the conditional formatting test. (If you are using loop in your script, put this in the loop)

           

               If ( PatternCount ( Archived Field ; "N" )

                    set variable - name=$$sendEmail value=1

               End if

               Exit Loop if $$sendEmail = 1

           

          Formula for conditional format:

          $$sendEmail > ""

           

          Example 2:

          In the relationship graph create a new table occurrence for your data table

               Table 1(Main layout) -----x---- Table 2 (data table)

           

          Formula for conditional format:

               PatternCount ( List ( Table 2::Archived Field ) ; "N" )

           

          Example 3:

          Formula for conditional format:

               ValueCount ( ExecuteSQL ( "SELECT \"Archived Field\" FROM \"Data Table\" WHERE \"Archived Field\" = 'N'" ; "" ; "" ) )

           

          Note: with ExecuteSQL if any field or table name has a space in it, you need to wrap it in quotes.  You need to escape the quote or FileMaker will not realize it belongs to the statement.

           

          Disclaimer: I didn't test the statements in the examples, I'm outta time and have to hit the road...

          1 of 1 people found this helpful
          • 3. Re: Conditional Formatting based on a Feild Value in a Found Set on a Seperate Layout
            keywords

            Another option is to forget about Conditional Formatting and use conditional hiding instead. From what you describe you want, in some circumstances, to hide the text ("I made the Font white so It blends with the background and is unseen") so rather than achieve that through tricks such as making the text the same colour as the background, which is the workaround we had to use once, use the new "Hide object when" option:

            Screen Shot 2017-05-12 at 7.36.28 PM.png

            In the dialog you can insert whatever calculation you need to provide the condition. This is much superior to CF.