4 Replies Latest reply on Aug 10, 2009 8:34 AM by philmodjunk

search query/analysis of multiple fields

Title

search query/analysis of multiple fields

Post

I am using FileMaker Pro 10 to analyze voter registration data. Each voter in the database is assigned a unique ID number.  The database also contains four fields which represent four different election dates, lets call them "Election A", "Election B", "Election C" and "Election D".  Each of the four fields contains either a "V" (voted) or "DV" (didn't vote) to indicate whether the voter voted in the particular election or not.  I want to run a search query on those four fields (representing the four election dates) to identify the universe of voters that voted in exactly one of the four elections (but no more than one election).  Any ideas on how this could be done?

• 1. Re: search query/analysis of multiple fields

Add a new calculation field: If (Election A = "V" , 1, 0 ) + If (Election B = "V" , 1, 0 ) + If (Election C = "V" , 1, 0 ) + If (Election D = "V" , 1, 0 )

Place this field on your layout and search for "1" in it.

I'd suggest a design change, however:

Put your voting history in a related table where one record = 1 election. Now you can have any number of elections to track not just four.

With the right setup, you can use the Count function to count votes when you use a related table.

• 2. Re: search query/analysis of multiple fields

It's possible, but somewhat awkward. Try:

Enter Find Mode []
Set Field [ Election A ; "V" ]
Set Field [ Election B ; "DV" ]
Set Field [ Election C ; "DV" ]
Set Field [ Election D ; "DV" ]
New Request
Set Field [ Election A ; "DV" ]
Set Field [ Election B ; "V" ]
Set Field [ Election C ; "DV" ]
Set Field [ Election D ; "DV" ]
New Request
Set Field [ Election A ; "DV" ]
Set Field [ Election B ; "DV" ]
Set Field [ Election C ; "V" ]
Set Field [ Election D ; "DV" ]
New Request
Set Field [ Election A ; "DV" ]
Set Field [ Election B ; "DV" ]
Set Field [ Election C ; "DV" ]
Set Field [ Election D ; "V" ]
Perform Find []

Another option woud be to define a calculation field to return the number of elections in which the user voted, and search for 1 in the result.

---
Note:
It would be better to make these fields of type Number and use 1 for voted, 0 or empty for didn't vote.

It would be even better to have a related table for this data.

• 3. Re: search query/analysis of multiple fields
In accordance with your suggestions, I modified my database and made a related table of voter history in which one record= one election.  You indicated that with the right setup I could use the Count function to count votes.  Can you elaborate on this?
• 4. Re: search query/analysis of multiple fields

Count ( Relatedtableoccurrence::NonBlankfield )

Will count the number of related records.

Put this in a calculation field and you can search on it instead of the earlier calculation. There is one significant drawback, however, the above calculation will be an unstored calculation and this will result in significant search delays when working with moderately large numbers of records. Some developers script a work around where adding/updating a "vote" record would add/subtract from a counter field. That can be a bit of a challenge for some systems and it is a tradeoff between flexibility and performance.

You can also define a summary "Count of" field that counts the total number of cases where someone voted. This field can be used in a summary report to list the total number of elections that a person has participated in.