7 Replies Latest reply on Jul 29, 2013 7:34 PM by StylisticGambit

    Need help with invoicing solution

    StylisticGambit

      Title

      Need help with invoicing solution

      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!

        • 1. Re: Need help with invoicing solution
          philmodjunk

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

          • 2. Re: Need help with invoicing solution
            StylisticGambit
                 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.
            • 3. Re: Need help with invoicing solution
              GuyStevens

                   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.

              • 4. Re: Need help with invoicing solution
                philmodjunk

                     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

                • 5. Re: Need help with invoicing solution
                  StylisticGambit
                       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!
                  • 6. Re: Need help with invoicing solution
                    philmodjunk

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

                    • 7. Re: Need help with invoicing solution
                      StylisticGambit

                           Thanks again for your help, Phil.  I was able to implement a script like the one you suggested.  Everything is working great.  :)