a way to find who attends often and who doesn't

[hartmut77]

Hello

I am new to filemaker but i have to make a database with it. I am interested in making a file that will keep track of people who attend our classes ( they are free) and I am trying to figure out how to locate the people who don't come for 4 weeks. There are like 400 possible people but most don't come. That's where I am having the problem.

I am staring at 4 fields. "eventDate" , "name" , "attending" "30days" - People who have attended once but are not there for 30 days. How can I track those people? Please suggest to me. I am all ears. Thanks

Cindy

[Ugo DI LUCA]

Hi,

There are millions of ways to have this list displayed, depending on what exactly you're looking for.

Is it that you're looking for people whose gap from one event to another is greater than 4 weeks, or only those that disn't attend since Today-4 weeks.

It's also hard to guess and tell what to do from the fields you mentionned though.

[hartmut77]

I need help with this .... PLEASE

Sorry Ugo , I just got home. I am looking for people who have attended in the past but now have a greater than 4 week absence. That is what I am somehow trying to flag.

Thanks

I am not sure the best way to go about doing this. I usually end up with over the top solutions and you guys usually point out something a whole lot easier.

Thanks again

[Fenton]

I recently did something like this. First, you need 2 files, 1 for the people, one for the attendance. This is basic relational design. If you only have 1 file, you could sort by person, then export the result summarized by person (ask).

The problem in Attendance is that you are trying to Find "not attended," which you can't really. But you can find attended. So do a date range find for who has attended.

Use Copy All Records to get all the IDs (yes, you should have IDs). Do this on a layout with only the ID. Paste into a global field (could be in either file, read on).

There are 2 scenarios possible. The simplest is that you want to find everyone who hasn't attended. All you need to do is create a relationship from the global multi-line field to the ID. Then Go To Related Record ["People__gIDs"].

That's the people who attended. Show Omitted, that's the people who didn't.

If however, you want to limit the group to those matching some other criteria, such as only "active" people, you'd need to first isolate that group, via a Find or relationship, then Loop through them.

Loop: Check their ID against the list in the global field. If it's not there, they haven't attended. So if it is there, Omit them. You'll be left with a found set of those who haven't attended within that date range.

Best to do the Loop backwards, that's easier with Omits:

Go To Record/Request ["Last"]

Loop

[Ugo DI LUCA]

Hi,

Assuming you have a MemberID...

1. create a SelfJoinByMember.

2. create a global field gGap where you'd enter the gap you're looking for (currently 30)

3. create a Value List of Dates related to the SelfJoin

4.Use this calculation (unstored) and place it in one layout only.

Case(

cMatchGlobalGap =

Date(SelfJoinByMember::date)-TextToDate(Substitute(MiddleWords(Substitute(ValueListItems(Status(CurrentFileName),"dates"),"/","bar"),PatternCount(ValueListItems(Status(CurrentFileName),"dates"),"