11 Replies Latest reply on Aug 28, 2016 4:12 PM by philmodjunk

    Invoice for wholesale and retail


      I need an invoice that will be for both wholesale and retail sales.  I am currently using a starter solution for the template that has been altered for most of my needs. I know that I will need to add a field in the "Products" for an additional price. I'm just not sure how the best and simplest way to set up the invoice would be. I realize I can always have the retail price in "Products" and then discount it on the invoice for wholesale but I would like to avoid that. I'm using FM11 and not very proficient just yet.


      Grateful for any help.

        • 1. Re: Invoice for wholesale and retail



          one way to achieve that would be to have two prices lists, for example retail and wholesale, instead of having two fields on Products table. Now each customer would have a list price linked to him. Then when you invoice a customer, the price comes from the list price. This way, you may add new prices lists later without altering the Products table. There are many ways to implement that:


          • you may have one table per prices lists: you would need two fields, one holding the product id and the second one the price
          • you may have a single table with three fields: one for the product id, one for the price list id and another one for the price.
          • 2. Re: Invoice for wholesale and retail

            Not sure how to go from here.



            • 3. Re: Invoice for wholesale and retail

              Say that you add a text field, invoiceType to Invoices that reads either "Retail" or "Wholesale" for a given invoice. then you can modify the current unit price field in Line Items to use an auto-entered calculation such as;


              If ( Invoices::InvoiceType = "Retail" ; Related Products::RetailPrice ; Related Products::WholesalePrice )

              • 4. Re: Invoice for wholesale and retail

                I've been trying but unable to make either of these things work. It could just be I haven't used FM in quite some time and forgot the language or it's beyond me.


                What I need in products is:





                On the invoice I need to be able to have either the wholesale or retail price put in by whatever method is the best. The program is strong so I don't know what the best way would be. Avoiding checkboxes or something would be best but I have no idea where to look.


                I'm attaching a file maybe it will help?

                • 5. Re: Invoice for wholesale and retail

                  If I get a chance, I'll take a look later tonight unless someone beats me to it, but my previous post spelled out exactly what can be used to get the results you've requested.

                  • 6. Re: Invoice for wholesale and retail



                    I added a wholesale and retail field that work well, just don't know how to get it to work with the invoice as needed. Yes you did spell it out,  I just haven't been able to make it work. Very much appreciate your help.

                    • 7. Re: Invoice for wholesale and retail

                      Unfortunately, you've got two different people suggesting different approaches to do what you want. Both methods work, but what I find in your file--once I changed the file extension back to .fp7 like it needs to be, is not what I suggested. This approach can work, it just seems more complex and thus was an approach I was avoiding.


                      My suggestion was to add a new field to Invoices and to change a field in Lineitems. Invoices would gain a field where you select the invoice type and line item's unit price field would be redefined to check that field in order to determine whether to lookup (copy) a wholesale price or a retail price for the given item.


                      I find that you've added the text field, Invoice Type to the Invoices table. Next open Manage Database and go to Products to add a new field named WholeSalePrice . Then find Price in Line Items. Double click it and select the auto-enter tab. Clear the looked up value check and click the button for "calculated value". Enter this expression:


                      If ( Invoices::Invoice Type = "Retail" ; Related Products::Price ; Related Products::WholeSalePrice )


                      Finally, add Invoice Type to your Record Detail-Invoices layout and give it a value list with the values "Wholesale" and "Retail" so you can select the type. Be sure, at this point to select this type before adding line items so that the auto-enter calculation can enter the correct Price.


                      I'm not saying that the other advice was bad, it can work quite well. It just isn't what I recommended here. To use the other approach with separate price list tables, you'd need to add more relationships and probably at least two different copies for two different occurrences of each of the price list tables. One would link to products and the other would link to lineitems.

                      • 8. Re: Invoice for wholesale and retail

                        I have FM Pro 11 Advanced so I don't know this is a FM7 file unless I somehow copied an old file.


                        I think I've done everything as you wrote but I get this error.


                        I really do appreciate the time you have spent trying to help.

                        • 9. Re: Invoice for wholesale and retail

                          It looks like you missed this step

                          "Next open Manage Database and go to Products to add a new field named WholeSalePrice"


                          or maybe the spelling is not correct?

                          • 10. Re: Invoice for wholesale and retail



                            Would like to contact you, tried to send a message but it would not work.


                            Thanks in advance,





                            • 11. Re: Invoice for wholesale and retail

                              FileMaker 11 files (and versions 8, 9 and 10) all have a file extension of .fp7 as they all use the same file format. .fp11 does not exist as a valid file extension.


                              With the release of version 12, they had to change the file format and the current file extension for versions 12 - 15 is .fmp12.


                              You should be able to click the @ symbol at the top of this screen and send me a message from there.