1 2 Previous Next 19 Replies Latest reply on Aug 18, 2016 2:06 PM by web247

    How Can I Create Multiple types of invoices with large amounts of different information?

    web247

      Hi,

       

      I have 4 completely different invoice forms I need to create filemaker.

       

      I understand that it is better to have one invoice table, but I am concerned about the

      amount of information that is need on each invoice.

       

      I have thought about linking the invoice table to each different set of fields, as if the were

      an Invoice Line items table.  That way the I can show all invoices in one portal on the customer record.

       

      So my structure would be:

       

      Customers-->Invoices-> Dish machine Invoice

                                          -> Laundry Machine Invoice

                                          -> Housekeeping Invoice

                                           -> Invoice Line Items

       

      I would link each to the Invoice table with a separate primary key for each table.

       

      The problems is I need all of these to link to the Product table as well, and also to

      a work order table, for when parts are used to fix the machines (we don't charge for those).

       

      So I would put a portal in each Dish, Laundry or Housekeeping invoice for parts used, that

      would substract in from the Salesman's truck inventory as work order line item.

      And a portal for ordering more chemical that need to be linked to the Invoice Line items

       

      That way the Salesmen would not have to leave the Machine invoices to start a chemical order.

       

      So if I understand the process correctly I would have to create a different Products and Work Order

      table occurrence and link them to each Machine Invoice table by a primary key .  

       

      But how can I also link to the Invoice Line Items, to create a new invoice separate from the Machine Invoices?

       

      Should I put all Machine fields in the Invoice table instead of creating separate table for each?

      Would that be too many fields for one Invoice table, that would remain blank for most invoices?

       

      I am attaching PDFs of all the Lease Machine Invoices, so you can see how many fields are need for

      each invoice.

       

      Any advice anyone can give me would be appreciated.

       

      Thanks,

       

      Kandra

        • 1. Re: How Can I Create Multiple types of invoices with large amounts of different information?
          Mike_Mitchell

          Hello, Kandra.

           

          I would suggest that you turn your thinking to something more generic. Each of these invoice types has one or more types of items associated with it. For example, you have items of work, products, items to order, etc. Instead of thinking in terms of invoice types, instead, I would structure this so you have one invoice related to multiple types of line items. Something like this:

           

          Customers-->Invoices-> Product Line Items

                                              -> Work Line Items

                                              -> Parts Line Items

           

          Okay, so what does this do for you? Each type of invoice has one or more of these associated with it. Have a set of tables that defines the template for each invoice type. When you create a new invoice, use the templates to create the necessary child items for that type of invoice. You can then use a series of portals on the invoice data entry page to edit / update those items.

           

          HTH


          Mike

          • 2. Re: How Can I Create Multiple types of invoices with large amounts of different information?
            David Moyer

            Hi,

            I hope this is a starting point for you:

            The difficult answer (I'm skipping the detail) is that those are some labor-intensive "Templates".  The easy answer is ...

            In FM, those templates are "layouts".  You will need to start by creating four different layouts, one for each type of invoice, but all four based on the same Invoices table (properly, "table occurrence").

            • 3. Re: How Can I Create Multiple types of invoices with large amounts of different information?
              web247

              Hi David,

               

              Yes, I understand they all require different layouts. ( I know how much work I have to do)

               

              I guess my big question is, should all the fields for each layout be in the invoice table?

               

              Not all of them are used for each invoice (really none of them are repeated) does it matter in the long run

              if the Invoice Table has hundreds of fields that aren't all used each time an invoice is created?

              • 4. Re: How Can I Create Multiple types of invoices with large amounts of different information?
                web247

                Mike,

                 

                With your suggestion, how would I link to the Products table?

                 

                Wouldn't I have to create a different Products table occurrence for each  Line Item table?

                My parts, chemicals, dispensers, etc are in my Products table. 

                 

                Where would I put all of the fields that need to be on each invoice? In the Invoice table or

                are you saying one of the Line items table would hold all those fields?

                • 5. Re: How Can I Create Multiple types of invoices with large amounts of different information?
                  David Moyer

                  Ohhhh.

                  I didn't ponder on it long enough.

                   

                  I retract the rest of my answer for now, sorry.

                  • 6. Re: How Can I Create Multiple types of invoices with large amounts of different information?
                    David Moyer

                    Hi,

                    let me try that again.

                    this attached solution idea requires setting three global text fields with values that would designate an invoice section, like "Dryer" or "General".  Each of the three fields would "point" to a portal on your invoice layout.  Each of the portals is based on its own TO.  One would need to set the three global fields before viewing any individual invoices.

                    • 7. Re: How Can I Create Multiple types of invoices with large amounts of different information?
                      SteveMartino

                      To answer some OP questions, the line item fields go on the invoice-which will be the bulk of your fields on the invoice.  The invoice only fields would be stuff like Invoice date, Invoice number, payment terms and Total.  And a global field, (drop down, radio buttons) for invoice type.

                      I would think multiple TO's would be better so when picking services and parts you can take advantage of value lists, or lists that only show related possible services/parts.

                      You may also want to add an equipment table, which is something I use for HVAC, that lists a particular equipments' parts.  In my example, buy knowing in my customer base of equipment, how many of Part A I have in the field, and on the truck, I can manage my inventory better-not carry 50 Part 'A's when my customers in the field only have 7.

                      Customers-<Invoices-<LineItems>- Dish machine Parts/service

                                                                           >-Laundry Machine Parts/service

                                                                           >-Housekeeping Parts/service

                       

                      (Also connected to Customers)-<Equipment-<Service

                      • 8. Re: How Can I Create Multiple types of invoices with large amounts of different information?
                        web247

                        Thanks for the example David.

                         

                        I would not have thought of that, but I am not sure if it will work for this.

                        I would still need too may different fields per record.

                         

                        I am beginning to think I am making this more complicated than it needs to be.

                         

                        While this is a very complex set of forms, I really just need Boolean fields for most.

                        So unless someone tell me otherwise, I will just add all fields to to the Invoice table.

                        and use different layouts for each invoice type.

                         

                        Again my only concern is the amount of fields that will be in the invoice table.

                        Do you know how many fields you can have in a table before it causes an issue?

                        Especially if they are not all used for every invoice?

                        • 9. Re: How Can I Create Multiple types of invoices with large amounts of different information?
                          web247

                          Steve,

                           

                          Are you say instead of putting all the fields on the Invoice table, I should put them all in the Invoice Line Items table?

                           

                          Wouldn't that be too many fields per invoice, for regular invoices. 

                           

                          If I understand it correctly, every time a line is added to an items invoice, wouldn't each line have about

                          100 or more blank fields, even if I don't have them on the layout? 

                          For most invoices I only need a line item to have the item name, SKU, price etc.

                           

                          Not every invoice is for lease equipment.  Sometime the Salesmen are just ordering chemical, for non lease equipment accounts.

                           

                          So what table would I connect the products table to or are you showing 3 Product table occurrences?

                          One for each type of parts?

                          • 10. Re: How Can I Create Multiple types of invoices with large amounts of different information?
                            David Moyer

                            I don't know the limit for fields off of the top of my head, but I know it's nothing to be concerned about.

                            You might consider just adding a new table dedicated to reporting that can contain a bunch of clutter you don't want in your regular table.

                            For example, an InvoiceReportingTool table which contains a field for your invoice primary key, and then a bunch of anything else you want.  You relate the reporting table to the active table, creating temporary records for reporting, then deleting them.  (For example, find your invoices, import the invoice numbers into your reporting table, populate, print, delete.)

                            On top of that, you can try creating a single layout with all (empty) text and repeating text fields.  Then write a script that populates those fields programmatically, per invoice.  They can be global fields; or you can make individual records so you can have a record of the invoices. This technique allows so much flexibility.

                             

                            edited for grammar and clarity

                            • 11. Re: How Can I Create Multiple types of invoices with large amounts of different information?
                              web247

                              David you have been so helpful thank you .

                               

                              But I guess I am too much of a novice, some of that is over my head.

                               

                              I understand the Script of find your invoices, import the invoice numbers into your reporting table, populate, print, delete, but not why I need to do that when I can just print my invoice table as report.

                               

                              As for the other option I have never learned how to used repeating fields.

                               

                              But you have answered my question, blanks field don't matter.

                               

                              So I can put them on the Invoice table, then add a portal for parts used and chemicals ordered.

                              That way I can also filter the portals to only show items that apply to that machine.

                               

                              I think this will work best in the long run, and I will be able to see all invoices on the Customer record.

                               

                              Thank you again today for all your help.

                              • 12. Re: How Can I Create Multiple types of invoices with large amounts of different information?
                                David Moyer

                                you're welcome.

                                You can do almost anything in FM.  Some things are absurdly easy; and some things are difficult ($).

                                 

                                As an aside, speaking of anything:

                                Sounds silly, but I've used FM, in layout mode, to create and test patterns for brick-paved walkways.  And I've used it to make BB gun targets. And ...

                                • 13. Re: How Can I Create Multiple types of invoices with large amounts of different information?
                                  beverly

                                  me too. I wish it had a better way to draw angled lines and other objects, tho.

                                  • 14. Re: How Can I Create Multiple types of invoices with large amounts of different information?
                                    David Moyer

                                    I've heard of a three-sided object called, "a triangle".

                                    ?

                                    1 2 Previous Next