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.
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?
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.
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]
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.
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.
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)