Can you explain the purpose and function of the Product Groups?
Phil, I asked for help with an invoicing solution because I thought readers of this forum would be familiar with the model. Actually, the database I'm building is for recording lab tests for a doctor's office.The doctors commonly like to run panels of tests that consists of, say, 25 individual tests. Every time they want to run this panel, the same 25 individual tests must be ordered. Furthermore, the same individual test may appear on several different panels.A real-world example may go something like this. Say the doctor wants to run two panels. One panel consists of 10 tests and one consists of 14 tests. The nurses, who will be using the database, will go to a layout and create an invoice containing the two panels. Out prints an order to our lab testing company for 24 individual tests that need to be run on the blood sample.A few days pass, and we receive the results. Now the nurses go to a different layout that lists all 24 individual tests, and records the results for each one.
Sounds to me you will need to have some automatic importing going on while adding the product groups to your invoice.
And if you want, in stead of two layouts, I would suggest using just one layout with two portals
(One portal where you enter the productgroups, and the other where tests are imported automatically)
using this technique:
So here's what I think you'll need:
A ProductGroup table where you enter all your product groups. This also needs an ID.
(ID is always a number field set to auto enter a serial number.) (IDFK is a simple number field)
A Tests table that has it's own iD, a ProductGroupIDFK field, and a name for the test.
This way every test is linked to a Productgroup. (If you have tests that can be related to multiple productgroups you will need a join table here.)
An invoice table. With ID, InvoiceDate, InvoiceNumber, ClientIdFk, ...
An InvoiceDetail table where you will be enteriing your product groups.
This needs it's own ID, An InvoiceIdFk, ProductGroupIdFk
InvoiceTests table for your tests.
ID, InvoiceDetailIdFk, And any fields you need to enter test results.
And a Client table for your clients / patients. With ID field etc.
Then, when adding a productgroup on your invoice you could use a script trigger that imports the tests in the InvoiceTests table.
The nurses then enter the invoice and select a product group and start entering results.
P.s. this is all off the top of my head, so forgive me if I missed something.
If you need any more help on something let us now.
Your invoicing reference is actually a pretty good model for this. Many retailers sell "kits" or "package deals". Buy this complete set of the works of Author xyz for %10 of the individual price. Doing so does not require an added table as they list the "kit" as another item in their products table, but then use a self join relationship to link each "kit" to the individual items that make up such a kit.
Thus, a doctor can select a blood test to measure a patient's glucose level by selecting that specific test or the doctor can order a panel of tests that includes that test with others.
You can implement that method with these relationships:
TestingOrder (Invoices) -------<OrderedTests (LineItems)>------Tests------<TestPanels>----Tests|Panels
TestingOrder::__pkTestingOrderID = OrderedTests::_fkTestingOrderID
Tests::__pkTestID = OrderedTests::_fkTestID
Tests::__pkTestID = TestPanels::_fkPanelTestID
Tests|Panels::__pkTestID = TestPanels::_fkTestID
Tests|Panels is a second Tutorial: What are Table Occurrences? of Tests to facilitate creating records for test panels and selecting the tests that make up such a panel.
Then, to get your list of individual tests, a script kicks in when your doctor selects a test panel that uses the above relationships to pull up the list of individual tests and adds their TestID's to the OrderedTests table to include them in the Test Order.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Do you think it makes sense to trigger the script when the ordering invoice is "finalized"? I put that in quotes because the order may need to be edited for a day or two afterwards. Or should the script trigger as each new panel is added to the invoice? My gut tells me to try the later.Can you give me a description of what such a script might look like? I don't need the step-by-step script items; just a description is fine.DaSaint, I've found several of your videos on YouTube helpful over the last couple months as I've been getting my feet wet with FileMaker. It wasn't until I followed the YouTube link in your post that I realzed that you are the guy whose videos I've been watching. Thank you for posting those! For this problem, your portal technique will be useful.Phil, I really appreciate your tip of using a self join; it makes my life a lot easier. I've set up an ERD like the one you suggested and will be playing around with scripts to add tests as line items.Thank you both!
Do you think it makes sense to trigger the script when the ordering invoice is "finalized"?
If you are referring to my suggestion, that could work. I was thinking in terms of a script that was triggered as each "panel" Test was selected so that the individual tests show up immediately, but I can see "pros" and "cons" with either approach.
Either way, a scritp can use an IF step to identify that the test is a "panel" and then use Go To Related Records to pull up the list of individual records in Products. It can then loop through these records, using variables to copy the ID's into the lineItems table. (A second variable can be used to enter the TestID.)
Thanks again for your help, Phil. I was able to implement a script like the one you suggested. Everything is working great. :)