1 2 Previous Next 19 Replies Latest reply on Apr 9, 2014 12:59 PM by philmodjunk

    Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year

    EricKent

      Title

      Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year

      Post

           I have a table (Assistance) containing a date field (Assistance Date) and a type field (Assistance Type).  I want to create a text box that is conditional formatted to have various color fills based on both of these fields.  

           For example, I want the field fill to be red if the type is X or Y and has occurred 2 times in the past 365 days, yellow if the type is X or Y and has occurred 1 time in the past 365 days, and green if there have been no occurrences of X or Y in the past 365 days.

           The part I am having trouble with, predictably, is creating the function.  I am rather new to the Filemaker function building as I have previously just used fields without much interaction between them.  I am hoping someone more proficient can help me with this formula so that I can use it as a stepping stone to learn function building.

           Thank you in advance for any assistance you may be able to give me with this - I am hoping it is rather simple for someone who actually knows what they are doing more than I do.

        • 1. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
          philmodjunk

               What do you mean by "occurrences"? Is each "occurrence" a different record? And is it a different record in the layout's table or a related table?

          • 2. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
            EricKent

                 A photo is attached to hopefully help explain.  If "gas" or "utilities" entires are made in the portal 2 times in a rolling 365 day period, as they are in the picture, the conditional formatting formula should return true so that the the field tied to the formatting fills red.  Based on the picture, after Jun. 12, 2014 the conditional formatting would be yellow, and after Mar. 3, 2015 it would be green, assuming no further entries are made between now and that date.

                  

                 My thought was creating 3 conditional format rules for the field, each with its own formula:

                 If no occurrences of gas or utilities exist for the past 365 days;

                 If exactly one occurrence of gas or utilities exists for the past 365 days; and

                 If more than one occurrence of gas or utilities exists for the past 365 days.

            • 3. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
              philmodjunk

                   What I see appears to be a portal. Therefore, each "occurrence" is a record in a related table. The context involved is crucial and the version of FileMaker you are using may affect what options you might use to get that result.

                   Is the field for which you want this conditional format a field that is inside or outside the portal row?

                   Is this a filtered or unfiltered portal?

              • 4. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
                EricKent

                     Filemaker version is 13.0v1.

                     The portal is located inside a tab in the Assistance table, and I want the conditionally formatted field to be in a separate Information table.  

                     The portal is filtered.

                     I apologize for my inexperience here, I would imagine it is hampering efforts a bit.

                • 5. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
                  philmodjunk

                       Yes it is making this a bit more difficult. What do you mean by a "separate information table"? Do you mean that you need to see a single value located somwhere else on the same layout but not inside the portal? I'm confused by the term "table" here as this will be only a single value on the layout that you have described.

                       Please describe the relationship that you have defined to link the layout's table to the portal's table. What match fields? What operator(s)?

                       What is your portal filter expression? (This can be found inside portal setup...)

                       There are a number of aggregate functions that can access data from all the related records in the portal's table, but they will ignore the portal filter and thus not produce/search data consistent with the portal filter's reduction of the total records shown in the portal. Thus, a means to access the group of records that are both related to the current record in the layout's table and that pass the filter criteria will need to be devised.

                       The most straight forward solution may be difficult to implement unless you have a working knowledge of SQL.

                  • 6. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
                    EricKent

                         I sent further information to you in a private message.

                    • 7. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
                      philmodjunk

                           I will eventually read your PM, but:

                           Please keep the discussion here in the forum. This is a better user interface for technical discussions and by keeping it public, others can learn from and contribute to the ongoing discussion.

                      • 8. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
                        EricKent

                             Certainly.  I just used the PM to send you a link to the actual database itself to clear up my inability to describe things in any form of articulate manner.

                             I am not certain what you mean here: "Please describe the relationship that you have defined to link the layout's table to the portal's table. What match fields? What operator(s)?"  I have based the database off the Personnel Record starter solution, and am customizing it heavily, and there is a single serial number match field between tables in the layout.

                             As long as the formula for the conditional format can be written using some for of if statement, I do not believe ignoring the portal filter will be an issue - it is simply a date filter and should have no bearing on what color fill is displayed.  My planned approach I was struggling with was similar to the portal filter shown below.  Each color fill (red, yellow, green) for my indicator would have its own conditional format formula that would basically filter the portal table between the current date and the current date, year minus 1.  For the red it would have an if statement that was if 2 results were found, return true.  For yellow, one result found return true.  And for green, no results found, return true.  I was struggling with the syntax language though.

                             The portal filter expression is probably not written terribly well, but it functions for filtering based on date, and is pasted below.

                        If  ( 
                        IsEmpty ( Assistance::Start Assistance Date Filter ) and IsEmpty( Assistance::End Assistance Date Filter ) ; 1 ) or
                        
                        If  ( 
                        IsEmpty ( Assistance::Start Assistance Date Filter ) and not IsEmpty (Assistance::End Assistance Date Filter );  
                        Assistance::End Assistance Date Filter ≥ Assistance::Assistance Date
                        ) or
                        
                        If  ( 
                        not IsEmpty ( Assistance::Start Assistance Date Filter ) and IsEmpty (Assistance::End Assistance Date Filter );  
                        Assistance::Start Assistance Date Filter ≤ Assistance::Assistance Date
                        ) or
                        
                        If  ( 
                        not IsEmpty ( Assistance::Start Assistance Date Filter ) and not IsEmpty (Assistance::End Assistance Date Filter );  
                        Assistance::Start Assistance Date Filter ≤ Assistance::Assistance Date
                        and
                        Assistance::End Assistance Date Filter  ≥  Assistance::Assistance Date
                        )
                        • 9. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
                          philmodjunk
                               

                          and there is a single serial number match field between tables in the layout.

                               That suggests that if we were to take a look at manage | Database | Relationships, we'd find something like this:

                               Personnel----<Assistance

                               Personnel::PersonnelID = Assistance::PersonnelID  (though the field and table names may be different.)

                               Your layout would specify "Personnel" in Layout setup | Show Records from and your portal would specify "Assistance" in Portal Setup...|Show Related Records From.

                          And your portal filter doesn't make any sense to me. What are you trying to do with it? Limit the assistance (portal) records to just those for a specified 365 day interval?

                          • 10. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
                            EricKent

                                 Yes, all the above associations and relationships you said are true.

                                 The filter is for an individual viewing the database to see all aid given, from monetary categories or food bounded by up to two calendar inputs, a start date and an end date.  If the user enters no dates, display all results, if only a start date is entered display everything after that date, if only an end date is entered display everything before that date, and if a start and end date are entered display everything between those two dates.

                            • 11. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
                              philmodjunk

                                   What looks strange besides the unecessary nested IF functions is that there is no reference to data in your personnel table.

                                   Here's a simpler portal filter for what you want:

                                   First define the start and end date fields as date fields with global storage. That's not absolutely necessary, but that way, you can move from personnel to personnel record and not have to reselect dates in these fields unless you want to use a different range. You also don't need to define global fields in their own globals table but that can make them easier to keep track of:

                                   Case ( Isempty ( Globals::gStartDate ) and IsEmpty ( Globals::gEndDate ) ; True ;
                                              IsEmpty ( Globals::gEndDate ) ; Assistance::Date > Globals ( gStartDate ) ;
                                              IsEmpty ( Globals::gStartDate ) ;Assistance::Date < Globals ( gStartEnd ) ;
                                              Assistance::Date > Globals ( gStartDate ) and Assistance::Date < Globals ( gStartEnd )
                                             ) // case

                                   The good news is that it appears that we can ignore the portal filter for your conditional formatting as you want it always to be for the most recent 365 day interval.

                                   I would suggest adding a field to Assistance named MonetaryAd and put a 1 in it when the assistance is Monetary. If you link in a table of assistance types to Assistance, this field can look up that 1 from the related table each time that the user selects a type of aide listed as "monetary" in that related table. This is not strictly necessary but makes it easier to check for and makes it easy to add new types of monetary assistance in the future.

                                   The check that you need could be set up using ExecuteSQL, but let's go with an easier to understand method instead.

                                   Go to Manage | Database | Fields and add both the MonetaryAid field to Assistance (just set it up as a number field for now, I can show you how to add the related types table and set up a look up for this field after we get this part working) and add a calculation field to Personnel, constOne, where you select number as the result type and type in a 1 as the calculation. This gives us a field in Personnel that will have  1 in it for all records in the table.

                                   Also, define a unstored calculation field (click storage options in the specify calculation dialog), cTodayYear in Personnel as:
                                   Get ( CurrentDate ) - 365

                                   Then click over to Relationships, select Assistance and click the duplicate button (two green plus signs) to make a duplicate Tutorial: What are Table Occurrences? of that table. Link it to Personnel like this:

                                   Personnel::PersonnelID = Assistance 2::PersonnelID AND
                                   Personnel::constOne = Assistance 2::MonetaryAid AND
                                   Personnel::cTodayYear < Assistance 2::date

                                   Then your conditional format expressions can be:

                                   Count ( Assistance 2::date ) = 1 // specify color for when there is just one instance in the last 365 days
                                   Count ( Assistance 2::Date ) > 1 // specify color for when there is more than one

                                   No need to specify an expression for when there are no instances of monetary aid as that can be the default formatting specified for the field.

                              • 12. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
                                EricKent

                                     Unfortunately the new table and relationship method did not seem to work.  I have the following SQL code that works for one record, but it does not work for multiple records - it reads across all all of them when evaluating.  So when two individual records each have one of these entries each, it still returns true for both records.

                                Let ( [
                                ~cd = Get ( CurrentDate ) ;
                                ~sd = Date ( Month ( ~cd ) ; Day ( ~cd ) ; Year ( ~cd ) - 1 ) ;
                                
                                ~sql = 
                                " SELECT COUNT (*) 
                                FROM Assistance 
                                WHERE ( \"Assistance | Type\"  =  ? OR \"Assistance | Type\"  =  ? ) AND 
                                \"Assistance | Date\" BETWEEN ? AND ? " ;
                                ~count = ExecuteSQL ( ~sql ; "" ; "" ; "Gas" ; "Utilities" ; ~sd ; ~cd )
                                ] ;
                                
                                ~count > 1 
                                )

                                      

                                     Is it somehow possible to limit the calculation to only the currently open record?  Also, the calculation only updates when records change, I need it to update even if no modifications have been made to the record but a day has passed.

                                • 13. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
                                  philmodjunk

                                       Your SQL would need either a Join clause linking to your Assistance table to Personnel or an additional WHERE criterion that species the ID of the current personnel record.

                                       And if you later identify a different form of assistance that you also want to add to "gas" and "utilities", you'll have to redefine this calculation.

                                       The other option that I specified will work so feel free to make a detailed description of what you tried to set up if you want help in getting that option to work.

                                  • 14. Re: Conditional Formatting Formula Based on Occurrences in Rolling Calendar Year
                                    EricKent

                                         I started from a backup copy of the database and tried this again, and it works with the field I have to put a "1" in.  Thank you very much for the help!

                                          

                                         Now to make that field auto-enter, my thought was to base it off the following calculation

                                    Case(
                                    Assistance | Type ≠ "Food (1)"; 1;
                                    Assistance | Type ≠ "Food (2)"; 1
                                    )

                                          

                                         but that calculation is returning 1 for single entry.  Am I somehow using the case statement incorrectly?  If I remove the "Food (2)" line it works properly and identifies everything that is not "Food (1)", but I need it to knock out multiple types before marking the remaining with a 1.

                                         The last issue is that the conditional format does not update until a user clicks somewhere outside of the portal where the fields are located.  Even clicking to change to the other tab dies not update the calculation.  Can calculations be forced to run so that the click outside the portal would not be necessary?

                                    1 2 Previous Next