Need help with invoicing solution

     Can you explain the purpose and function of the Product Groups?

     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:
http://www.youtube.com/watch?v=VJqA03X33DQ

     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.

Relationships:

     ProductGroup::ID-----------[=]-----------Tests::ProductGroupIdFk

     Invoice::ID-----------[=]-----------InvoiceDetail::InvoiceIdFk

     InvoiceDetail::InvoiceIdFk-----------[=]-----------InvoiceTests::InvoiceDetailIdFk

     Clients::Id-----------[=]-----------Invoice::ClientIdFk

     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"?

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