Searchable Summary or Calculation

[Sherri]

I have a couple of questions. I am very new to FMP. I am working in an existing database with several tables.

1. The first challenge is we are looking for instances of an event happening in a date range. So we put in:

Case (movedate > Date(1;1;09) and movedate > Date(5;31;09); 1; 0)

Then we can find the total of it with a summary Total of. It shows the correct result.

The problem is we want to be able to search that field to find the ones who have moved 2 times versus the ones who have moved 1 time. So I am guessing the summary is not the way to do it.

What other way could I do this and find a searchable total?

2. We are marking records with a certain text field in one table by way of a calculation with an If statement.

homemove = If (move = "HOME"; 1; 0)

When we bring that calculation result (homemove) into the other table, the results do not show correctly. When I do a sort for a one in homemove, it finds the right records, but the number 1 does not show in about 2/3 of them. Any words of wisdom? Does the calculation have to be stored? I just thought of that and will try it.

[Sherri]

Storing or unstoring it is not helping, still needing ideas.

[bcooney]

It would be helpful to better understand the tables that you have. Can you elaborate the tables and their relationships?

[Sherri]

Here is my best description without specific questions. I am sorry if I am not clear. I am learning about the system and the program at the same time.

The tables we are using for this are for moves and one for basic info about the person. They are related through the ID number for the person.

[Sherri]

Is it necessary for the field to be related from table to table for the calculation result to show correctly?

[bcooney]

So, you have a table Moves and a table People and they are related, so that there are many moves per person? Each move record has homemove, which if it is the field =1.

You want to find all people that have more than one home move within the date range 1/1/09-5/31/09? It's not clear what count you're looking for.

[Sherri]

Thank you for responding.

I do have a table of Moves and a table of People. They are related by the record number of the person. There are many moves per person.

There are two different problems.

FIRST,

If the name of the move is HOME, then the field will equal 1, from homemove = If (move = "HOME"; 1; 0).

There are other moves such as AUNT, UNCLE, etc.

In this case, I just want to show (on a layout in the People table) all of the ones who say 1 because they have a move with the name HOME.

SECONDLY,

Each of these moves are tracked by a date. I am looking only for the moves that happen between 1/1/09-5/31/09. For these I want to count the number of moves that happen in that time. Such as if the person moves on 1/3/09 and 2/3/09, then the count should return 2.

I can make this work with a summary function. The problem is I can not search to find all of the people who have moved 2 times since I can not search a summary field.

Is there another way I can count these youth that will be searchable when it is complete.

[bcooney]

To count the number of moves that happened for the date range:

1. Enter find mode in the Moves table.

2. Enter 1/1/09..5/31/09 in the move date field.

3. Perform find.

Then, if you only want Home moves, constrain the found set above to homemove=1.

Then, to see the corresponding people. Go to related record MATCH FOUND SET from move to people. This will show you a found set of people who had home moves between the dates 1/1/09 and 5/31/09.