As you have discovered, count will count all related records, not all related records that pass the filter. It completely ignores the portal as it is wired directly into the relationship.
But you can define a "count of" summary field in the portal's table. Then make a copy of your portal (so it has the same portal filter), change the number of rows to 1 and then place your summary field in this portal. You now can display the count of the number of records in your portal, but you cannot access that value for use in a calculation. In those cases, you'll need to either incorporate the "filter logic" into the underlying relationship such that an unfiltered portal correctly displays the count or use ExecuteSQL with the filter criteria replicated in the WHERE clause of the SQL query.
Thanks, Phil. I cannot change the relationships and I actually need a calculation. The ExecuteSQL function sounds promising. Could you please give me some help with this for the following example:I have a questionnaire. In the corresponding Questions table there is a field Category. The layout of the questionnaire is based upon a TO from table ClientMeasurements showing a portal from table Answers. This portal is filtered so that only questions/ answers from one category of questions are shown. I need to make sure that a client answers all questions in this portal before allowing him to go to the next page (switching layout to portal with next category of questions).My workaround here is to use two globals. One acts as a counter and is incremented with the help of an OnModify script trigger bound to the answers field. The other global holds a list of AnswerIDs so that the counter is not incremented when a given answer is only modified. However, for each filtered portal I have to manually set the total number of questions in order to know when all questions in one portal are answered.
I cannot change the relationships and I actually need a calculation.
But you can add a new relationship that incorporates the needed "filter logic" via match fields without changing existing relationships. Just because you have a given relationship between table A and Table B does not mean that you can't create another or even many different and separate relationships between the same two tables. The secret is to create additional Tutorial: What are Table Occurrences? so that you can keep the relationships separate.
I managed to replicate the filter using ExecuteSQL. Thanks for pointing me to this function which is really powerful. For anyone trying the same, this site helped me understanding the relevant syntax: http://www.filemakerhacks.com/?p=6406
Coming back to your suggestion, when saying that I cannot change the relationship I actually meant that I could not find a way to do so. The table structure is this:
Clients ---< ClientMeasurements ---< Responses >--- Answers >--- Questionnaires
with TO's being linked by their serial numbers.
The layout in question is based on ClientMeasurements using a portal to Responses. This portal is filtered by ( Answers::Category = "A" OR Answers::Category = "B"). How would I replicate this filter using relationships only?
I tried adding a global field containing the two values of "Answers::Category¶Answers::Category" to Responses as well as to Questionnaires and related these to Answers::Category. Both approaches did not match the filtered portal.
Well stick with ExecuteSQL since you have it working. That looks like a much better way to go. Not all portal filters can be replicated at the relationship level and this one would be tricky to do.
Create a calculation field in ClientMeasurements with text as the result type:
List ( "A" ; "B" )
Use this calculation field as an added match field in a relationship to a new occurrence of Answers to match only to answers records with one of these values in the answers::category field. But that only gets you halfway there, then you have to link in a new occurrence of Responses to answers with a global field in answers that will link only to the desired records in Responses. A script would have to set that global field to the correct value--presumably by copying the ClientID fk from the current record in ClientMeasurements.