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.
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.
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:
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.
Is there anyway you can provide an illustration for my example?
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.
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.
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.
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