10 Replies Latest reply on Oct 22, 2013 1:41 PM by petropix

    Can line items be made unique entities?




      I'm working in FMPro 12 Starter Solution 'INVOICES'.


      I need to correlate and calculate information from a number of purchases on an invoice. For example, say there are a total of 6 items listed on the invoice: A,B,C,D,E and F. How do I fish out products 'B' and 'E' and enter their corresponding field data into another field for calculation or look-up? Products B and E might be of the same category or product type, but each requires the print out of a different result. Also, products B and E would need a sub-total $ result/summary.


      Any help with this greatly appreciated.



        • 1. Re: Can line items be made unique entities?

          Your explemation is too abstract, can you be more specific about what you try to achieve?

          • 2. Re: Can line items be made unique entities?

            Okay. Bit complicated. The products involved are 'wine making kits' -- different brands, varieties,  each requiring a different 'brew time.' I need to print out a carboy label for each brand/variety purchased, and the label will comprise a tear-off section on the invoice (for in-store processing) -- ie. the label will contain processing information for a   4, 6, or 8 week period.


            If a customer buys only one kit, it's easy to auto enter/link the information to the carboy label portion of the invoice. However, if more than one kit is bought, how do I enter the particulars of each wine kit to the corresponding label? Each invoice will be two pages to accomodate the labels. The labels will also need to contain 'Customer Name,' Invoice # and Wine Kit name, of course. I've successfully created an invoice/label relationship as such. However, I'm stuck when there's more than one item on the invoice.


            So here might be a typical invoice/purchase:


            Line Item 1 -- Brand X -- 4 week label

            Line Item 2 -- Brand Y -- 6 week label

            Line Item 3 -- Brand Z -- 8 week label

            Line Item 4 -- Generic retail product - no label needed

            Line Item 5 -- Another product - no label


            Hope this makes more sense



            • 3. Re: Can line items be made unique entities?

              I have done something similar here for label printing, wherby I run a looping script that creates records in another table, one record for each label, then the records are easily printed using the label format layouts in FM. Once I print the labels, I have no need to keep those newly created records, so I delete all records from that table before I create the records. It's not sophisticated, but it works simply and fairly quickly, as the table that I use to print labels does not contain many fields...


              I hope this helps??

              • 4. Re: Can line items be made unique entities?

                Thanks for the input. I should clarify that by "label" I actually mean a custom designed form (about a quarter page in size) that contains an itemized list. So not sure if FM label format would work here-- in any case, the idea is to make the 'label' part and parcel of the invoice, and that's simply a layout/design job. But I'm still in the dark on how to link each Line Item (purchased products) to the relevant label..



                • 5. Re: Can line items be made unique entities?

                  What if you created a layout in the items table that includes subsummry parts, so you can group items once sorted?? Would this help you?

                  • 6. Re: Can line items be made unique entities?

                    It still kind of depends on the details, but....


                    How about a table of Labels (with # Weeks, Start Date, Customer ID plus other necessary fields).  Each line item that needed a label ( ie not Generic retail product) would be related to a Label based those three fields.


                    Labels would calculate their instructions by looking to their related Line Items and pulling the stored instructions. Though it's also possible you may want a Products table to do some of that lifting.

                    • 7. Re: Can line items be made unique entities?

                      Agreed. The information you want to retrieve would be in the Product table, not the invoice line items. Then you can design a printed invoice with the associated data and images from the product table.


                      You can even choose to email PDFs of that layout, or construct an email from the related fields.

                      • 8. Re: Can line items be made unique entities?

                        Hey, thanks! This makes sense. I'm going to try it out and report back. Still foggy about how to relate/link all this...  


                        Also, I need to be able to sort out the the Wine Kits from the Generic retail products and make calculations based on that. For instance, a discount (based on an accumulated number of purchases -- loyalty points) would apply to particular Wine Kits, but NOT on retail products.


                        The plot thickens..



                        • 9. Re: Can line items be made unique entities?

                          Well, that's another issue to be handled in a different way. You need a discount field in your Line Items table.

                          • 10. Re: Can line items be made unique entities?

                            Yeah, appreciated. Is there anyway I can get back to you, or someone else one-to-one if I get stuck on the label issue? The loyalty discount points is a  monster in-waiting, but I can't get  to it until I solve the label thing..