1 2 Previous Next 15 Replies Latest reply on Aug 26, 2016 1:36 PM by Mike_Mitchell

    Conditional Formatting Quandary


      I want to understand why my conditional formatting is only working some of the time.


      I have my calculation looking for a exact text string in a related table record.
      RelatedTable::Related field = "Text"


      I am looking into the related table through a portal and adding records via a button.
      If I add the record containing the 'Text' before any other record the conditional formatting works.  But if I've added a different record prior to adding the one containing 'Text' it doesn't apply the condition.


      I want the condition to apply no matter when the record is created or if it exist with other records.

      Is there something simple I am overlooking.

      Sorry for the newb question.

        • 1. Re: Conditional Formatting Quandary

          It depends on what you're conditionally highlighting, and where it is. If you're on Table (as opposed to RelatedTable), and you're asking about a related table's field, then the FIRST value for that related table is the one that will be evaluated. That's why you're seeing the behavior where if the first record you add contains the text, your conditional formatting is applied.


          Are you wanting to conditionally highlight something in the related record? If so...put the object you want to apply the formatting to in the portal. Are you wanting to highlight something in the record you're viewing based on something in a related record? Then you need to approach it in a different way. Also...you may want to use PatternCount rather than "=". It depends.


          If you want to highlight something any time a pattern of "text" occurs in the records related to the one you're viewing, you might use something like PatternCount ( List ( RelatedTable::RelatedField ) ; "Text" ).

          • 2. Re: Conditional Formatting Quandary

            'PatternCount ( List ( RelatedTable::RelatedField ) ; "Text" )'


            This works.  Thanks for the help and the quick reply.

            • 3. Re: Conditional Formatting Quandary

              OK so I have another condition that I need to apply.


              Each of the records in the related field have a time stamp.
              I need the condition to apply only if the record containing related field 'Text' content is NOT followed by (based upon the time stamp) a record containing different content: 'OtherText'.


              Does that make sense?

              • 4. Re: Conditional Formatting Quandary

                Flesh it out a bit. Are you looking at a list view of records with portals to related records on each record? Is it:


                Record 1 has related records 1, 2, 3 and one of those related records contains 'Text', so highlight.

                Record 2 has related records 1, 2, 3 and NONE of those related records contains 'Text', so unhighlight record 1.


                What you're asking is possible...just flesh it out a bit more with what you're after. Screen shots would help, too.

                • 5. Re: Conditional Formatting Quandary

                  Record 1 (Context) has related records (a history) that consists of 2 fields ('occurrence name' and 'date') along with an fk_ID field


                  occurrence name is a text field

                  date is a timestamp


                  Conditional formatting should be applied IF occurrence name 'X' is preset in the related history AND it has a more recent 'date' than all other events with the occurrence name 'Y'.


                  'X' and 'Y' can happen multiple times in the related history but the 'date' make those records unique.



                  Is that helpful?

                  • 6. Re: Conditional Formatting Quandary

                    Getting close...and you want to highlight something in Record 1 (Context)? Or in one of the related records?

                    • 7. Re: Conditional Formatting Quandary

                      The context record.

                      • 8. Re: Conditional Formatting Quandary

                        If you can send a screen shot with actual data, that would be helpful. I'm thinking offhand that the easiest way to accomplish what you're after is something involving ExecuteSQL. You're going to need to determine the most recent date for X and for Y. It sounds like something I'd do in a Let statement, which involves enough specificity that real-world examples would be incredibly helpful. I realize it might be confidential data.

                        • 9. Re: Conditional Formatting Quandary

                          It sounds like you're using a direct reference to a related field. When you do that, FileMaker evaluates it as it if were only the first record in the related set. That's why it doesn't change for the different related records.


                          You might want to look at the GetNthRecord function (FileMaker Pro 15 Help) to allow you to extract the value of a specific related record.

                          • 10. Re: Conditional Formatting Quandary



                            There is a link to my test file.


                            The 'event' of 'Approved' triggers the conditional formatting.
                            The functionality/conditionality I want is that if the 'event' of 'Change Requested' is inputed at a more recent date the conditional formatting does not apply.


                            That way the history of approvals stays intact and the conditional formatting is applied only when 'Approved' is more recent than 'change Requested'.


                            Screen Shot 2016-08-25 at 3.34.29 PM.png

                            • 11. Re: Conditional Formatting Quandary

                              I'm taking a look now. In the meantime, when a portal allows record creation, that last record "appears" to be present, so you get a garbage can on a row that appears empty. Users will sometimes think they should delete the blank record, but when they click the trash can, nothing happens. For that reason, I usually hide buttons when the ID of the related record is empty.


                              Hide when IsEmpty ( z_fkID_Projects ), for instance.

                              • 12. Re: Conditional Formatting Quandary

                                I am using that in my real document.  Thanks.
                                I created this on the fly and just didn't take the time to add that functionality.
                                i sincerely appreciate how helpful you have been with all of this.

                                • 13. Re: Conditional Formatting Quandary

                                  The attached file is one way to accomplish what you're after. Give it a test and see if it'll work for you.


                                  Couple of things you'll have to dig into.

                                  1) When you make a calculation such as List ( RelatedTable::Field ), the list will be made based on the sort order of the RELATIONSHIP, not the portal. So I've sorted the relationship in descending order by your Date field (which is a Timestamp field). That way...the most recent event will always occur first in your list.

                                  2) I've added a script trigger which fires OnModify in the event field. As soon as an event is created, records are committed, which causes the refresh and therefore the calculations will be evaluated.

                                  3) I've created a Let statement which looks at the Positions of each of your types of Event. If an Event hasn't occurred, that event is set to "" (as opposed to zero). A list of the occurring events is generated, the Minimum position is evaluated, and if it's the same as the first occurrence of "Approved", then Approved is first in the list, so highlighting (or unhiding) will occur. I'd go with the Hide condition instead of Conditional Formatting in this instance. Conditionally formatting to white has issues when rows or backgrounds have colors.


                                  There are no doubt a lot of different ways to go, and others will have improvements on the formula I'm using. But...the formula is readable as is so hopefully it'll give you an idea of how it's working.


                                  Here's the formula, which evaluates to 1 (true) if Approved is first, and 0 if it is not first.


                                  Let (


                                    FirstApprovedInList = Position ( List ( History::event ) ; "Approved" ; 1 ; 1 ) ;

                                    FirstChangeRequestInList = Position ( List ( History::event ) ; "Change Request" ; 1 ; 1 ) ;

                                    FirstNewPrototypeInList = Position ( List ( History::event ) ; "New Prototype submitted" ; 1 ; 1 ) ;


                                    FirstApprovedInList = If ( FirstApprovedInList > 0 ; FirstApprovedInList ; "" ) ;

                                    FirstChangeRequestInList = If ( FirstChangeRequestInList > 0 ; FirstChangeRequestInList ; "" ) ;

                                    FirstNewPrototypeInList = If ( FirstNewPrototypeInList > 0 ; FirstNewPrototypeInList ; "" ) ;


                                    FirstToOccur = Min ( FirstApprovedInList ; FirstChangeRequestInList ; FirstNewPrototypeInList )


                                  ] ;



                                  Case (

                                    FirstToOccur = FirstApprovedInList ; 1 ;




                                  • 14. Re: Conditional Formatting Quandary

                                    I think I'd prefer to use ExecuteSQL.  How do I limit the SQL to just the related records and not all records?

                                    1 2 Previous Next