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.
maryj152Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresident, Dedication Technologies, Inc.Commented:
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.
maryj152Author Commented:
Out of time for today.
will try to work on it tomorrow
maryj152Author Commented:
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?
Will LovingPresident, Dedication Technologies, Inc.Commented:
Try this: create a calculation field in the Credits table called Fiscal Year.

FiscalYear = Year( DateParticipated ) + Case( Month (DateParticipated > 6 , 1 )
[Result = Number]

This takes the Year for DateParticipated and if the Month is in the first half of the year, it adds one to display the Fiscal Year. Then you can just use a single Global Number field for the Year you want to report on.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
maryj152Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.