8 Replies Latest reply on Sep 18, 2013 9:35 AM by petropix

    How to create invoice from multiple 'product' tables?




      I'm new to FM-Pro, but have a reasonably good grasp of the interface/lay-out/tables/fields and relationships. Rather enchanted with over-all intuitive feel of the program. But I'm stuck :-).


      I'm working with Starter Solution template: INVOICES.


      The invoice creation lay-out (invoice detail) draws from the portal 'Invoice Data' which subsequently draws from the master table Products. (Line Item) All is fine. However, I've got three other distinct 'PRODUCT' tables which I want to tie into this layout. I've tried duplicating the 'Invoice Data' portal , adding relevant fields from other Product table, and making the relationship. However, the calculation (sub-total/Grand total etc. breaks down -- does not even corrolate/show.


      In other words, Can I have "Line Items" representing different tables (products/items) and do a single invoice calculation accordingly? Or, how does one relate different PRODUCT tables to a single invoice creation lay-out?


      Sorry if this sounds convoluted.


      Help anyone?





        • 1. Re: How to create invoice from multiple 'product' tables?

          You can use the Lookup () Function to lookup from various table.


          Assuming your link is on the SKU or item code set the fiels Price, description etc. to auto-enter a cacluated value something like...



          Case (


          not IsEmpty ( lineitems|Product1::SKU ); Lookup ( lineitems|Product1::description );

          not IsEmpty ( lineitems|Product2::SKU ); Lookup ( lineitems|Product2::description );

          not IsEmpty ( lineitems|Product3::SKU ); Lookup ( lineitems|Product3::description ))


          Make Sense?


          John Morina

          Pueblo Systems, Inc.

          • 2. Re: How to create invoice from multiple 'product' tables?

            Thanks, John -- sort of makes sense. But I need to get a handle on the terminology here. There's no 'Lines Item' table in the INVOICES Starter Solution as far as I can see. "Line Item" is defined via portal showing fields from 'Invoice Data' : <Item, Item Number, Quantity, Tax, Unit Price, Discount, Amount>  The invoice sub and grand total are calculated on this line. Accordingly, do I need to make a separate 'Invoice Data' (Line Items :-)  for each Product/Inventory table? And how then are the totals calculated?


            In any case, doing a bit of reading elsewhere on this Forum. Clearly I'm not the only one with this challenge. Had a hunch there's a fundamental choice to be made on how to structure the task:  a single Inventory Table with a 'Type' field to differentiate the product lines and then using various filters/script triggers to make it work, or the above separate tables/portal approach.


            Complicated either way.


            Any suggestions?




            • 3. Re: How to create invoice from multiple 'product' tables?



              I opened the Invoices starter solution. Yes the invoice line items is called Invoice Data. Its the same thing and more commonly called line items.


              I do have a question as to why you need 3 separate product tables. I ask because it also creates an issue with the value list used for the item field. That aside I added another product table to Invoices and set the lookup to auto-enter a the calculation I suggested for Part Number, Unit Price and taxable. I also setup a temporary value list trick. Never did it this way before but i needed something quick. If you change or add any items to the product tables just click the button on the invoice detail layout "Update Item VL"



              • 4. Re: How to create invoice from multiple 'product' tables?

                Many thanks, John. I think elegant, and at no real estate cost  


                We've got three different produtcs, hence I thought three tables (i.e. cars, toasters and bathing suits) -- each has different attributes/fields, of course. The challenge is, we need a value list drop-down for each Product/Item on the invoice lay-out -- i.e. Ford >Fiesta> 2 Door> -- or at least TWO values for each item  in the drop-down. I can see that without additional sub-menu dropdowns, this wouldn't be possible with your example. So now I'm thinking merging everything into ONE Product table and trying a filtered portal approach for each Line Item - effectively 3 portals, say two lines each-- but that's expensive real estate..


                My head is swimming..



                1 of 1 people found this helpful
                • 5. Re: How to create invoice from multiple 'product' tables?

                  This is hard to describe in an email but hopefully this will help?


                  Yes you only need one product table.  The drop down list you describe don't apply to Line items, it would be used to find the product SKU.


                  Product Lookup : Each product should have a Unique product code or SKU. That code will be used to lookup pricing and description etc. from the product table to invoice line items. Each product would have at a least...  SKU - Name - Description - Price. Other fields might be Brand/Color/Size there are a lot of varations but they are all looked up from the SKU.


                  You can also caterorize the products something like...

                  Category = (cars, toasters, bathing suits etc.).  then Brand - Style - Color - Side...whatever else to further classify the item.


                  Now if you know the SKU you could type it in and the product will be entered. If you don't know the SKU you have to find it..


                  Finding the SKU - This is where the filtering comes in but not of the line items. One option is to open a product list layout in a new window next to the invoice. This list is where you would filter the products to find the SKU. Put a button on the row so all the user has to do is click the button to add the product to the invoice.


                  I hope this makes sense...it's getting late and I'm getting cross eyed...



                  1 of 1 people found this helpful
                  • 6. Re: How to create invoice from multiple 'product' tables?

                    Yes. VERY helpful, John. Thanks! But if you'll bear with me for just a bit more... In my Product table I assign a field with Product or Item ID -- the number we get from the manufacturor/package. I also assign a unique Serial # with FM  for each record/item in the table. So, which one is the SKU?  Or is it generated entirely from something else? Alpha-numeric?



                    • 7. Re: How to create invoice from multiple 'product' tables?



                      The sku is a separate field from the serial # (and don't use # signs in the field definations) and should be alpha-numeric.


                      I put a serial number field (Primary Key) in every table to uniquely identify each record. This field is used for relational integrity. A SKU while unique can change. I had a client decide to change all the sku's. This can reek havoc on sales reports if you are relying on the sku.


                      You can create your own SKU or use the Manufacture SKU the only caveat is it has to be unique. This could be a problem if you are stocking different product lines. There could be duplicates.

                      You could just use the manufacture sku as a reference, possibly for ordering? although a lot of distributors have their own codes for the same reason.



                      • 8. Re: How to create invoice from multiple 'product' tables?

                        Great, John. This clarifies so much. I'm sticking with the Product ID serial # for now. Got lucky and my invoicing lay-out is now  working fine. Threw all the inventories into the single Products table and created a drop-down with multiple 'values' in the >Item< Line Item on the invoice (brand & model). No need for additional moves/windows. The predictive text  "search" function works like a charm. I was worried I'd have to scroll thru thousands of items before I got to the bathing suits    Not so.


                        Have a great day!