Sounds like your initial relationship is many to many instead of one to many. More than one individual can be linked to more than one plot. And then, at a later point, (possibly at the time a person is interred, possibly sooner), the plot will be linked to just one individual. That sound correct?
If so, your relationships will look something like this to manage the pre-sale of plots to your clients:
Start with these relationships:
Clients::__pkClientID = PrePurchase::_fkClientID
Plots::__pkPlotID = PrePurchase::_fkPlotID
You can place a portal to PrePurchase on the Clients layout to list and select plots for each given Clients record. Fields from Plots can be included in the Portal to show additional info about each selected Plots record and the _fkPlotID field can be set up with a value list for selecting Events by their ID field.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Then, when clients do specify specific individuals for specific plots, you can use a different relationship with a different match field that is left empty until the assigment is made to record that detail:
Plots::_fkInterredID = ClientsInterred::__pkClientID (ClientsInterred would be a Tutorial: What are Table Occurrences? of clients.)
However, even this may be too simplistic. Since prepurchase takes place on a family basis, you may want to set up both a Person table and a families table with a one to many relationship from Families to Person. In which case, you'd use Families in place of clients in the PrePurchase set of relationships and an occurrence of Persons would take the place of ClientsInterred.
Thank you very much for taking the time to reply, that makes sense what you are saying, and yes it would be a many to many so a separate table would resolve this.
i will have a go and see how i get on, i have thought about the family basis, however i thought i may run into issues here if we have a couple who are not married and wish to pre purchase but want to leave the choice of plot (from the chosen ones) open.
i'm sure i will get the hang of this, Filemaker seems a relay great program to use and i'm sure what we need is a simple database to setup and use within Filemaker, i had a go with MS Access a few years back and just got nowhere with it.
"Family" was a convenient term used by me as I expected it to cover most such situations. But as far as your database is concerned, your couple can be treated as a "family"--you'll just need to document any needed details to cover the legal/emotional aspects of the fact that they are not married.
Here's a demo file that illustrates a Many to Many relationship. You may find some ideas in it that help you implement the relationship: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
Hi Phil or anyone else reading this.
I'm now getting on fairly well with my DB but i'm stuck.. I'm trying to set up the invoicing side of things, now i get the 'normal' invoicing situation
But what i have at the moment is tables for the following
Pre Purchase Certificates
These all relate to the 'Plot information' Table.
I can't get my head around how i 'invoice' these?
What i'm thinking is within the Line Items Portal on the invoice I have the first choice is a category selection listing Pre Purchase, Reservation, Burial and an "other" option
"Other" would then allow the next drop down to show the 'Product' items e.g Plaques, Trees, Grave prep charges etc.
But selecting one of the 'Certificate' options would let the nexdrop downwn lisrelevantnt certificate information e.g a value list from Pre Purchase table listing the ID but displaying the field "Pre Purchase Cert Number"
I'fairlyly sure this is where conditional value lists come in but i just can't work out how.
Any help would be fantastic.
Why do you need four different tables for the three kinds of certificates plus "other" items?
In a "typical" invoicing system, you put all products and even services in one products table so that you can select them all from the same table when filling in the data in your portal to lineitems.
Thanks again for your reply, sorry for the delay in coming back, its been a busy week!!
I have a table for the individual certificates because i want them all to be stored within the database to eliminate having paper copies.
I couldn't think of another way to do this other than a seperate table for each?
I have (hopefully) managed to add a screen shot of the relationship graph which may help explain what I am trying to do?
But storing them in the database does not require using separate tables for them. You can store them all in the same table.
I'm returning to this project after a long break from it and I'm wondering if it would be easier to start from scratch?
i don't know how to invoice for the certificates and products?
An invoice that we would issue at the moment (created in Word) would have all the usual header items like invoice number, date and client.
The invoice lines would look like this:
1. Woodland plot AA22 for the late Mr Customer. Price
2. Pre purchase of plot AA23 for Mrs Customer. Price
3. Plot preparation and completion Price
4 Woodland plaque for plot AA22 with the following. Price
Wording - Mr Customer 1920 -2014
So looking at this example, line 1 has information in the burial certificates table, line 2 is from the pre purchase table, line 3 and 4 would both be a 'product' however with the plaque, I would like the option of being able to store the wording so we have it to refer to should the plaque ever need replacing?
I would be really thankful of any assistance on this to get things moving, even if it means starting the whole thing over.