4 Replies Latest reply on Jan 11, 2013 1:44 PM by cstanford101

    Help creating multiple invoices



      Help creating multiple invoices


           I am designing a database system for our business.  I am needing to create multiple types of invoices - I need Parts Invoices, Service Invoices, and Bills of Sale.  All three invoices will need to pull from the same customers and inventory tables.  I already have my customers, inventory, and parts invoices all done and the relationships all working (so that you can plug customer data into the invoice from the customers table, or create new customers into the table by inputting new info into the invoice) with a invoice line items table to deal with the many to many relationship.  But I'm having trouble creating the service and bill of sale invoices.  Does anyone have a good suggestion on how best this would work?  I need parts, service, and bills of sales to all have thier own tables/layouts (because even though they use the same customers and inventory they have some major differences between them) but I'm having difficulty dealing with the relationships since all three need to pull from the same contacts table and inventory table.  I want to be able to have a portal in my customers layout that will let me know all the invoices a customer has, and it needs to show that "this one was a parts, this one was a service, this one was a bill of sale, etc" so I can have better reporting.  Same with inventory, I will want a portal in each inventory item to show how many of each item has sold and on what type of invoice.

           Does anyone have a solution?  I feel like its a simple approach that my mind just can't wrap around.  I tried importing a copy of my invoices table/layout so that it would create a new table but that's not working, as it seems to be just pulling a copy of the invoice records.  Any suggestions on how to make this work would be very appreciated.  I am using FileMaker Pro 12 (not advanced just pro), currently on a windows computer but we will be using the database on both a windows and a mac as well as ipads/iphones.

        • 1. Re: Help creating multiple invoices

               The secret is in creating multiple table occurrences so that the same table can be part of more than one set of relationships. You can select a table occurrence "box" in Manage | Database | Relationships and click the duplicate button (two green plus signs). This does not create a duplicate table, just a new "box" for referencing an existing table.

               But I strongly recommend that you try keeping all three types of invoices in the same table. You can still have separate layouts for each type of invoice and can even set up different sets of relationships should that be necessary. There's really no major issue having a set of fields in a table that are left blank for some types of invoices and used to hold data on others. You can also link in a related table for invoice specific details if you prefer.  But keeping all three types of invoices in the same table give you reporting options difficult to produce if the data is not in the same table. An example would be a report listing data from all three types of invoices.

          • 2. Re: Help creating multiple invoices

                 The problem I'm facing with using different layouts with the same table is that all the records are in all the layouts, meaning ones I made under parts are also under service (just looking different).  Is there no way to fix that?  That will create a redudancy issue I think, and will make it difficult to search through them.  This may be a stupid question and an easy fix but when I duplicate the layout to continue to use the same table, it keeps all the records, and creating a new record in the new copy also creates a new record in the first layout.  Need a good way around this if one exists.

            • 3. Re: Help creating multiple invoices

                   There are a number of ways to resolve that with trigger controlled scripts.

                   A script can perform a find to only pull up records of the specified type each time the user accesses the layout.

                   A script can kick in each time the user returns to browse mode that constrains the found set to a specific invoice type. This way, when you perform a find, the script automatically limits the results of your find to just those of the correct invoice type.

                   If you base each layout on a different occurrence of the same table, each layout can have a different found set, sort order and current record even though they draw their data from the same data source table.

                   Or you can use OnRecordLoad to switch to the correct layout for the newly current record. This way, simply clicking through the invoices automatically switches to the correct layout for each invoice. (and you no longer have to play games with the found set to get them to match the layout). (Use the same occurrence for all three layouts if you use this trick.)


              • 4. Re: Help creating multiple invoices

                     I think I actually may have solved my issues using the table occurances (I had tried it but didn't make enough table occurances).  Here's hoping *crosses fingers* Thanks so much for the input, I appreciate it!  Pulling my hair out trying to figure this stuff out ;)