I have encountered upon a fairly complex report I need to generate from Filemaker, and while I have come up with one possible way of doing it, I am interested in ideas as to how it might be accomplished.
This is the following situation. I have two databases. One has customers, the other database has the purchases that a customer has made. I have set fields of information I store about the purchases made. There is no limit to the number of purchases a person can make.
The Customers Database is my main database, and there is a relationship to the "Purchases database". I can click on the individual items a customer has purchased through a portal.
But now I need to report on those customers in an efficient manner. The types of information I need to find the data as follows:
* Which customers have purchased x,y and z
* Which customers have purchased x,y, but not z
* Which customers have purchased x, and had purchased z
* Which customers have purchased x and z, but get y for free
* Which customers from the state of foo have purchased x,y and z
And so forth.
This causes an interesting conundrum. I can narrow down my criteria if need be, and I have done this to a point. I am more interested in reporting on the "Contact" information, based on the purchasing criteria, than I am on the actual purchases themselves (to a point).
I need to be able to generate one report that answers questions as above per contact (not displaying three contacts etc).
Every contact has an ID, every purchase has that contact ID for each particular contact it is attributed to.
A loop-back/self relationship is not helpful here per purchase (because I could related them via the contact ID) because it still doesn't allow me to do the finds above.
So what I have done is the following - I run a script that finds matching ContactID in the purchases. It then runs through and compiles into a field in Contacts database, all the subscription information for all the subscriptions for a particular contact in a slightly mnemonic form.
In the contacts database I have a Layout that has a search/report engine that allows the users to construct searches using field identifiers and operators (such as AND, OR, NOT), this then allows me to create search criteria for a find based on the search request that the user has entered.
I am not sure if this will work completely, but I am going to give it a shot. Does anyone else have any suggestions regarding filemaker and that kind of reporting?
(and I am talking about 20,000 contacts and 30,000-ish purchases, with roughly 4000 contacts with more than one purchases)