asked on

conditions for filemaker pro 10 if statement.

This statement works and gives the correct total.
Sum ( People to Registrations to Events::NoOfCredits)
When I add conditions to limit what is counted, the result is 0(zero).
If ( People to Registrations::DateParticipated >7/1/15 and People to Registrations to Events::CreditType = "CE";Sum ( People to Registrations to Events::NoOfCredits);0)
I get no error messages or hints from the field's Options Window.

Will Loving

It's not working because the if statement is being applied to the result of the calculation rather than limiting the related records that the calculation is working on, which is what you want.

There are different ways to do this but you most likely need to limit the relationship itself and/or the NoOfCredits field's

For the NoOfCredits field you could make a calculation field that only displays the number of credits if the type is CE. The use that for your Sum.

For the date range which will asssumedly change, you'll need to add an additional predicate to your relationship. Create a global Date field in People and add it as a predicate between people and events using the greater than or less than operator depending on the direction of the relationship.

maryj152

ASKER

Out of time for today.
will try to work on it tomorrow

maryj152

ASKER

The totals for NoOfCredits will be for a year(July 1 to June 30). For this year I don't even need date range yet since all are new. But to keep track of totals from previous years so I will be redoing the formula once a year.
Can a single date field have a beginning and ending date.. a date range that would give a fixed report?
Or would it be best to put in two global date fields for beginning and ending dates and make the result dynamic?

ASKER CERTIFIED SOLUTION

Will Loving

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

maryj152

ASKER

Have to work on something else for now.Will be working more on this later and will be back in touch if that is OK