I need some help with a find that I am trying to do. I have 3 tables: account, accountstatus, gift ( there are more tables but these 3 directly affect the search). I want to find all accounts that have given a gift in the last 12 months that do not have a status of AD, CGP or CLP. Here is what I have so far but am stuck now:

enter find

GiftDate >= CurrentDate - 365 days

Perform find

enter find

status = "ad"

new request

status = "clp"

new request

status = "cgp"

constrain found set

I was then going to omit all the records that had these statuses and show what was omitted to give me everyone that donated in the last year but didn't have those statues. I quickly discovered that everything that was omitted after the second find will show instead of what I really want.

I can't do a not equal find for the 3 statuses, correct? I know I can do this but can't get my mind around how to do it correctly.

I was just looking through some other posts. Am I trying to do too much in the search? Do I have to do a search for the first criteria and then loop through the found set and omit records with the 3 statuses? Kind of backwards but I am not sure if there is another way to do it. I know in SQL you can just set give me giftDate> 4/6/2004 and Status NOT IN (AD,CLP,CGP). It would be nice if FMP had a similar function.

Thanks for the help!

john

After talking with another colleague, we figured it out. I forgot about the ability to change the action in the contrain found set. YOu have the options of find record & omit record. I change it to omit record and set my status codes. Everything works like it should. Hopefully this post will help someone else out. But maybe everyone else already knew this and I am just slow!

John