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?
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.
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?
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.
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.
I sent further information to you in a private message.
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.
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 )
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::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?
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.
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.
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.
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.
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?