8 Replies Latest reply on Apr 17, 2014 9:30 AM by TomVassie

    Stuck at the start.. with tables

    TomVassie

      Title

      Stuck at the start.. with tables

      Post

           Hello everyone, 

           I'm looking for a little help if possible please.

           We run a natural burial ground, all information has always been recorded in books and copies of word docs etc, and i need to get a good database going to store everything.

           I'm trying to resolve all the relationships to one to many relations but i'm getting stuck with a few things.

           One thing i cant wort out is our Pre Purchase information, this would be quite simple if one person pre purchased one plot, however, what happens quite often is a couple or family will come to pre purchase - lets say they decide on two plots, they may say, we don't know who is having which plot!

           At present, they have a pre purchased certificate for each plot with Pre Purchased by Mr and Mrs ..... 

           should i resolve this with a Pre Purchase table? 

           This way, one person can have many Pre Purchases (if needed)

           But this then gets confusing when it comes to the actual burial, because only one person has one grave! so should this persons information be stored in the plot table or should the plot info be stored in the customer table or should i still setup a "person buried" table even though it will only be a one to one relationship (i think)?

           If anyone has any ideas or could point me in the right direction that would be great!!

           Thank you.

        • 1. Re: Stuck at the start.. with tables
          philmodjunk

               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-----<PrePurchase>-----Plots

               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.

          • 2. Re: Stuck at the start.. with tables
            TomVassie

                 Hi Phil, 

                 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.

                 Thanks again.

            • 3. Re: Stuck at the start.. with tables
              philmodjunk

                   "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

              • 4. Re: Stuck at the start.. with tables
                TomVassie

                     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

                     Invoices-----<Lineitems>----Products

                     But what i have at the moment is tables for the following

                     Pre Purchase Certificates

                     Reservation Certificates

                     Burial 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. 

                      

                • 5. Re: Stuck at the start.. with tables
                  philmodjunk

                       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.

                  • 6. Re: Stuck at the start.. with tables
                    TomVassie

                         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?

                    • 7. Re: Stuck at the start.. with tables
                      philmodjunk

                           But storing them in the database does not require using separate tables for them. You can store them all in the same table.

                      • 8. Re: Stuck at the start.. with tables
                        TomVassie

                             Hi Everyone, 

                             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.

                             Thank you.