OK. The company runs exhibitions all around the country and has clients who exhibit at these exhibitions located all around the country. What I'm trying to do is, for any given exhibit taking it's location and finding all the companies in the area, who have a local interest, who would be interested in exhibiting and also the companies located elsewhere in the country that have a national interest.
I hope that makes sense!
The devil is in the details and many of them are still lacking from your description of the issue.
How do you record a "local" or "national" "interest" for a given customer record in your database?
Are there multiple "interests" recorded for a given customer? If so, how did you structure your database to manage such multiple entries?
Examples of what this data looks like would be extremely helpful.
I haven't yet decided how to store their interest as I didn't know how I am going to need to search.
Each company has basic details, Company name, address etc. The exhibitions have a location detail being a city.
I want to be able to run a script for any given exhibition and to be returned a list of clients that might be interested in that exhibit based on their geographic interest and location in regards to the exhibit.
Any ideas would be helpful...
I strongly suggest working out how you would gather and store this information first--that will in all probability be much harder than designing a method to search the data. Once you can describe how the data is stored in the data base, the search and report functions will probably fall into place.
One method for documenting potential "interest":
Define a list of keywords that match to exhibition categories or topics.
For each customer, define a related "interest" table where one record records one topic of interest.
For each exhibit, define a related table of interests using the same list of terms.
Customer: John Smith
Exhibit: History of NASA
Once you have the data in the tables, performing a find to match customers with a given list of exhibit "interest" categories becomes fairly straight forward.
I have created a field in each table which gives a checkbox selection for each of the interest categories. Both the sets of checkboxes come from the same list of values.
Obviously the checked interests are then stored as plain text, how would i go about splitting up the text in each field, customer and exhibit, to compare them in a search?
Any help appreciated!
Hmmm, that's not the exact method I suggested, but it should work OK for you. ( I pictured a portal of "interest" records where you selected a different value from a value list on each row of the scrolling portal. )
Using your method, establish a relationship that uses the two check box fields to link your exhibit and customer tables:
Exhibit::InterestsCheckBoxes = Customer::InterestsCheckBoxes
(Existing relationships may require you to create new table occurrences for one or both tables and use them in the above relationship.)
Multiple check box selections in the same field are stored as text separated by carriage returns. When you use this field in a relationship, filemaker matches the records using "OR" logic.
Example: Referring back to my two examples in my previous post, the NASA exhibit record would match to any customer whose interest check box field has Space Travel OR Rockets OR Astronomy OR Astronauts selected.
In a script, you can thus pull up all customers with at least one matching interest selection using the Go To Related Record (GTRR) step. If you want to then restrict the matching records to a specific geographical area or list of geographical areas that you've entered in other fields, you can use Constrain Found Set to pare down the list of matching customer records.
Here's a possible script that would be launched from a layout based on your exhibits table:
We'll assume a field in Exhibit, Location records a geographical area we want to use to restrict our customer list.
Set Variable [$Loc; Exhibit::Location]
If [Count (Customer::CustomerID) > 0 /* There are related records that match */]
Go To Related Records [Show Only Related Records; From table: "Customer"; Using Layout: "Customer" (Customer)]
Enter Find Mode
Set Error Capture [on] // keep filemaker's records not found dialog from interrupting the script
Set Field [Customer::Location; $Loc]
Set Error Capture [off]
Constrain Found Set 
If [Get (foundcount ) > 0 /* Some records were found with this location */]
Sort [No dialog; Restore]
#Do whatever else you need to display/process your group of customers with matching interest/location data
Go To Layout [Original Layout]
Show Custom Dialog ["No customer records with this location were found."]
Show Custom Dialog ["No customer records matching this list of interests exists."]
GTRR Note: This is a powerful but woefully undocumented (by Filemaker Inc.) tool with many quirks and a few dangerous pit falls. If you're interested in learning more, see the following link:
Thanks for your help it's done just the trick!
One last query -
Is there any way to be able to search using the location field of the exhibition and find customers within a certain distance of that exhibition? I have been looking and I can't find anything on it except Postcode PA.
I'm not familiar with any method you could use here. I'd assume a method does exist, but haven't seen one you could "plug in" to your database.
You might want to open a new thread and post this question. Another forum participant may know of a method.