9 Replies Latest reply on Mar 30, 2011 7:10 AM by JayHall

    How to connect the two Invoices and Purchases Starter Solutions together



      How to connect the two Invoices and Purchases Starter Solutions together


      Hello Filemaker Friends,

      I was wondering if there is anyone who could help me get my head around connecting these two starter solutions Invoices and Purchases together into one solution.

      I was able to bring in the tables from the Purchase Orders Starter Solution into the Invoices Starter Solution successfully.  So now I have Invoices with Line items and Purchase Orders Table with PO Item Table.

      In a sense it seems like I should have to normalize the whole database since in theory I have two instances of "Line Items"

      Q.  Is it better to normalize the database to having both the Invoices Table and the Purchase Order Table to relate to "one Line Items table?"

      Here is a screen shot of my current database I modified the Invoices Starter Solution to include Purchase Orders (see screen shot)

      My main objective is integration of the two starter solutions to be able to keep track of my purchases and invoices but referencing or selecting line items that contain products.

      Please note I did not modify the relationships in the Invoices to Line Items this is from the original starter solution.

      Anybody's help would be very much appreciated.  Thanks.


        • 1. Re: How to connect the two Invoices and Purchases Starter Solutions together

          Merging the two Line Items tables doesn't really "normalize" your data. Normalizing data means altering your table structure so that the same data does not need to be stored in two different tables. Since invoices line items table represents items being removed from inventory and the PO line items represent items being added to inventory, there's no real duplication to normalize here. You certainly can use one Line Items table for both. I prefer that approach as I can use the resulting table to present all inventory changes in a "bookkeeping Ledger" format where the PO Items show the quantity added in a "debit" column with the Invoiced items showing their qty sold in a "credit" column and a running balance field to show the increase and decrease of inventory levels for each inventory item.

          If you decide to go ahead and merge the two tables, you'll need to add every field in PO Line Items not found in Line Items, including the fk_PO_ID field. Each Line Item record should have either a PO ID, an Invoice ID, but not both. This change will not make a visible change on your graph, however, it's just that the Table occurrence box where you have an X will now refer to the same data source table as Line Items. I wouldn't link it like you show with the red arrow, because then your have two different primary key fields, PO ID and Invoice ID linking to the same foreign key field in Line Items and this could combine Invoice LineItems with PO LineItems in the same portal if you get a PO record and an Invoice record with the same ID number.

          • 2. Re: How to connect the two Invoices and Purchases Starter Solutions together

            Please see new image upload (I guess I cannot add two screen shot images so I over wrote the last one.)

            Actually the bookkeeping idea is what I was shooting for to have one Line Items Table to add and subtract inventory levels.  But I am not clear on how to achieve this exactly, I mean the adding and subtracting of inventory items or the credit vs. debit approach when my base starter solution was the Invoices Starter Solution.  I don't have an Inventory Table just a report that came with the starter solution.  Isn't the inventory in this solution currently handled by the Products table adding and subtracting of product items?

            I understand you point on merging the two tables and adding the fields missing from PO Line Items (thanks!).

            Another question I have is if I were to combine the two tables Line Items and PO Line Items should I use "k_ID LineItem" for the main primary key ID in the Line Items Table? Because if I delete the relationship between the tables: Invoices and Line Items and relink the relationship Invoice ID -> k_ID_LineItems I will mess up and break the Invoices Layout, right?

            Also I am assuming that a new relationship from Purchase Orders to Line Items via the same relationship PO ID -> k_ID_LineItems.

            Another thing is my Currency table.  I deal in many currencies and found a post in the forum of how to achieve this and it works great.  But I only need it for making purchases.  So I should add this to Line Items Table too, correct?

            Can you please advise me if I am on the right track now?  If you can make a diagram to correct mine that would be helpful.  Thank you.

            • 3. Re: How to connect the two Invoices and Purchases Starter Solutions together

              Please check my last post again. When you merge the tables, do not change the relationships shown in your screen shot. Instead double click PO Line Items and select the newly merged Line Items table. You still will need separate fields and separate links for Line Items generated from the PO and line Items generated from the Invoice. Line Items from a PO will have a number in fk_POID from the PO table. LineItems from an invoice will have a number in Invoice_ID that comes from the Invoice table. Line Items that document a change in inventory for other reasons (Shrinkage, disposal of obsolete items...) would not have a value in either field. No record would have a value in both fields.

              The boxes on this relationship graph, BTW, are called table occurrences. You can have any number of table occurrences that all refer to the same data source table. This allows you to set up as many different relationships as you need between any two data source tables in your database.

              You may want to read this thread to learn more about table occurrences and how they are used throughout FileMaker: 

              Tutorial: What are Table Occurrences?

              I'd keep separate Qty fields for the two types of line item records. I'd define a calculation cBal as PO_Qty - In_Qty. Then I can define a running balance Summary field to compute the total of cBal so I can create a layout based on LineItems where I can sort to group the records by ProductId and then display the inventory levels as running totals for each product ID.

              I'm not sure exactly how you plan to use the currency table. To convert purchases to a common currency? You would not add this to the LineItems table, but you would have a relationship linking it to a currency field defined in invoices so that you can access the correct values for the specified currency for that invoice.

              • 4. Re: How to connect the two Invoices and Purchases Starter Solutions together

                Is there anyway you can provide an illustration for my example?

                • 5. Re: How to connect the two Invoices and Purchases Starter Solutions together

                  Take a look at this demo file. It's simplified, doesn't have all the fields a real system would use, but should demonstrate how the line items table can serve as the same table for POs, Invoices and all other transactions that will increase or reduce your inventory.


                  • 6. Re: How to connect the two Invoices and Purchases Starter Solutions together

                    Thank you so much!  That example makes it all clear now. Cheers! Cool  

                    • 7. Re: How to connect the two Invoices and Purchases Starter Solutions together

                      I was wondering if there is another alternative?  For example if I were to keep the two totally different "Line Items Type" tables one for Invoices and one for Purchases (like I did in my first screen shot) could I handle the Inventory Table IN and OUT activities in a different Inventory table?  I have included a link to a new screen shot of my newly labeled diagram for your review.  Would this be a bad idea or a wrong way of formatting my database?

                      By the way the Currency table is used to calculate landed costs as demonstrated in this post: Multiple Currency Conversion

                      Obviously I am looking for many solutions here.  But I am shooting for the best practices solution or something you would normally see in accounting type software.  Any further advice on this would be great.

                      Screen Shot History: Screen Shot for Post #1  Screen Shot for Post #2 (current in this post)

                      • 8. Re: How to connect the two Invoices and Purchases Starter Solutions together

                        One problem with screen shots of Manage | Database | Relationships | is that it isn't always obvious which table occurrence boxes point to the same data source table.

                        It's certainly possible to set things up that way, but when you need to work with the inventory, you appear to have two tables, one for inventory Out and one for Inventory In. It seems an overly complex approach and looks to be more difficult when you sit down to check how well your inventory levels are being managed.

                        I once worked with a Supply Chain Manager and learned a few things doing so. One was that it could be useful to look at the inventory levels to look for two key items as orders where filled and new product was received:

                        Items with a consistently large balance. Such items represent a place where you may have tied up more operating capital in inventory that you really need. You can't just decide from the levels, but it can draw attention to specific items where you might be able to lower the re-order points for that product.

                        Items with zero or near zero balances. Items where you are selling out or nearly selling out of a product represent a risk of lost sales when you can't supply what a customer wants to order. Thus, these items may need higher re-order points.

                        and so forth.

                        These kinds of evaluations may be hard to do if you can't pull up all inventory changes in a single view.

                        • 9. Re: How to connect the two Invoices and Purchases Starter Solutions together

                          Well I have adopted your recommendations and have set up my database to include one Line Items Table with two data sources "Line Items Invoice" pointing to "Products Invoice" data source and "Line Items PO" pointing to "Products PO" I also have the inventory balances thing working as well.  This was a big help in terms of understanding how to integrate two starter solutions with Filemaker Pro.

                          Thanks for all your help!


                          P.S.  Here is a link to my final screen shot: Final Database Diagram