6 Replies Latest reply on Feb 11, 2010 4:33 PM by philmodjunk

    relationship setup question



      relationship setup question


      I am using filemaker 10 advanced on a mac and I am a new user. I am wondering how I should properly setup the relationships for my database. I have customers and tests that I provide for them. For example I have 100 customers and 50 possible tests that they could take. Each customer is charged a different price for each of the 50 tests based on the contract they make. I was thinking I would have a one to many relationship from a table named customer to a table named test_price, and also a one to many relationship from test to test_price. Is this the right way to go? Will this cause problems when I try to create my invoicing, and how should I setup the relationship from test_price to invoice.

        • 1. Re: relationship setup question

          You're pretty much on the right track. I'd adjust those relationships just a bit:


          Relate customers to invoices


          Relate Invoices to test_price and put a portal to test_price on your invoice.


          Link test_price to test just like you described.


          In your portal to test_price on Invoices, you can add fields from test to better identify the test purchased on the invoice.


          Now you've got nearly the basic invoicing system most people use with a database, but without looking up prices from tests as you've indicated those prices will be different for every customer.

          • 2. Re: relationship setup question
               OK so I have things setup as you say. The portal is really great on the invoice layout.  How do I go about creating a layout so I can see a customer then a portal to see all the possible tests listed on the "test" table, and be able to fill in the prices that customer pays for those tests? 
            • 3. Re: relationship setup question

              Is it possible that a customer will purchase tests from you on more than once?


              That would seem to be the case and if so, I would set it up that way.


              You can put a drop down on your invoices layout to use to select a customer.


              Given the relationship: Invoices::customerID = Customers::CustomerID


              You can then display additional fields such as customer name and address on your invoice layout.


              If you want a list of tests in a portal so that you can select them for a given invoice, You can make this relationship:


              Invoices::InvoiceID X AllTests::TestID    (AllTests is a 2nd table occurrence of tests and the X operator is a "match all records" operator)


              A portal to AllTests will then list all the tests you offer. You can put a button on these portal rows with a script that adds that test to the customer's invoice.


              Freeze Window

              Set variable [$TestID; Value: AllTests::testID]

              Set variable [$InvID; Value: Invoices::InvoiceID]

              Go to layout [Test_Price]

              New Record / Request

              Set Field [Test_Price::TestID; $TestID]

              Set Field [Test_Price::InvoiceID; $InvID]

              Go To Layout [original layout]

              • 4. Re: relationship setup question

                I probably didn't explain well enough. I think you are really close to what I want to do. Every customer is going to order every test multiple times during the year. At the beginning of each year they renegotiate their test prices based on how much they ordered the year before. So right now I have one sheet of paper for each customer with a list of all 50 tests and the price for each test. At the beginning of the year I have to print out all 100 customers again and on each of their customer sheets write down the price they are going to pay for the following year for each test. To replace this paper system I need a customer layout in the database that lists all 50 possible tests and 50 spots for prices so that I can easily update all of the prices that customer is going to pay for the next year. Then monthly I will invoice them for the tests they have had taken that month (This part I think we have figured out) and of course I need to be able to go back through the invoices from last year and not have had the price change on the previous years invoices even though I update the price for the test every year. Not that it matters but the tests are for things like soil ph, salt levels, moisture content, mineral content, etc. 


                BTW thank you so much for your help this is really amazing and I'm sorry I didn't explain fully, and I appreciated and further help as I will probably have a bunch of questions as a newbie.  

                • 5. Re: relationship setup question
                     Wait actually I think you did answer my question exactly. I think I just don't understand how to setup the relationship to the additional table instance and how to setup the portal to AllTests. Could you explain that in a little more detail? Thanks.
                  • 6. Re: relationship setup question

                    I'm running behind and the alligators are circling my desk...


                    I think we can tweak the idea a bit to get what you want. I see you want to have a kind of invoice template for each customer that you can update for each year.


                    Until I get a bit more time to help or another person pitches in, This link describes how to make a new table occurrence among other things.


                    Many folks find the terms Table and Table Occurrence confusing. To learn more, click the following link:

                    Table vs. Table Occurrence (Tutorial)