Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Need help with invoicing solution

Question asked by StylisticGambit on Jul 27, 2013
Latest reply on Jul 29, 2013 by StylisticGambit

Like • Show 0 Likes0
Comment • 7

Title

Post

I need help designing an invoicing solution that is slightly different from the standard one.

1. The INVOICES table is in a many-to-many relationship with the PRODUCT_GROUPS table. Each invoice contains multiple product groups, and each product group can be on multiple invoices.

2. The PRODUCT_GROUPS table is in a many-to-many relationship with the PRODUCTS table. Each product group contains multiple products, and each product can be in multiple product groups.

In my mind, I envision something like the following during data entry:

A. The user visits a layout and creates a new invoice with multiple product groups on it.

B. The user then visits a second invoice which lists all of the corresponding products for the product groups on the first invoice, and selects quantities for each product.

Can anyone provide tips for creating a solution like this? Here are some specific questions I have:

Will I need one or two (or three?) join tables? (Which table occurences will I need? What will the ERD look like?)

How do I generate the second invoice (containing individual products) from the first one (containing only product groups)?

Thank you!

No one else has this question

Outcomes

7 Replies

philmodjunk Jul 27, 2013 9:17 PM
Mark Correct
Correct Answer
Can you explain the purpose and function of the Product Groups?
Like • Show 0 Likes0
Actions
StylisticGambit Jul 28, 2013 8:19 AM
Mark Correct
Correct Answer
     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.
Like • Show 0 Likes0
Actions
GuyStevens Jul 28, 2013 1:54 AM
Mark Correct
Correct Answer
     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.
Like • Show 0 Likes0
Actions
philmodjunk Jul 28, 2013 6:46 AM
Mark Correct
Correct Answer
     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
Like • Show 0 Likes0
Actions
StylisticGambit Jul 28, 2013 8:31 AM
Mark Correct
Correct Answer
     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!
Like • Show 0 Likes0
Actions
philmodjunk Jul 29, 2013 9:24 AM
Mark Correct
Correct Answer
     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.)
Like • Show 0 Likes0
Actions
StylisticGambit Jul 29, 2013 7:34 PM
Mark Correct
Correct Answer
Thanks again for your help, Phil. I was able to implement a script like the one you suggested. Everything is working great. :)
Like • Show 0 Likes0
Actions

Need help with invoicing solution

Title

Post

Outcomes

Related Content

Recommended Content