13 Replies Latest reply on Jun 13, 2017 4:07 PM by micky888

    Multiple prices each product



      now i have these following tables:





      •Sale line item

      *i'm not sure that all tables i have are all necessary or not

      -each product has many unit of measure (UOM).

      -each UOM has its own price

      -i would like to have many prices on each UOM (retail, wholesale,...)

      For example


      Unit: piece


      UOM: 1piece = 1piece

      retail price: $10

      whole sale: $8

      special price: $6


      UOM: 1pack = 6pieces

      retail price: $50

      whole sale: $48

      special price: $46


      UOM: 1dozen = 12pieces

      retail price: $90

      whole sale: $88

      special price: $86



      I would like to assign types of price to each customer


      I tried many ways to relate my tables together but i found nothing work


      Any helps or suggestions would be appropriate


        • 1. Re: Multiple prices each product

          You need not have UOM differences specify different prices, though you can if you want to. You can specify a unit prices and then also specify a calculation that converts a single price into different unit prices depending on the UOM specified by the user when filling out the order. This would greatly reduce the number of prices that you have to maintain and update for a given product. That might, in fact, simply limit your prices for a given product to two:


          One retail and one wholesale price.


          Some basic design work: Most sales or ordering systems start from this basic set of tables and relationships. Your names may differ but the basic structure is nearly always the same:


          Customers----<Invoices----<LineItems (sometimes called invoice data)>-----Products


          ---< is my notation for one to many with an auto-entered serial or UUID on the "one" side of each such relationship.


          Do you have such a basic structure in place?


          From there, you can put a field in Customers to designate a customer as either "retail" or "wholesale" that can then be auto-entered into a field in Invoices each time you select a customer for an invoices. (and which you can then change if exceptions need to be made in specific cases). Most of the heavy lifting would be in LineItems where you'd select a product, select a UOM, and enter a Qty. Auto-enter calculations can then use the Wholesale/Retail field in Invoices, the Qty and UOM in LineItems and the wholesale and retail base unit prices in Products to calculate the line item cost.

          1 of 1 people found this helpful
          • 2. Re: Multiple prices each product

            Keep in mind that prices (should) fluctuate, and maintaining 9 different prices per product can be a nightmare, depending on how many products you have. Did you think about implementing a per-client discount ?


            And if I buy 19 ProductA, will you bill me


            - 19 X piece, or

            - 3 packs and 1 piece, or

            - one dozen, one pack and one piece ?

            1 of 1 people found this helpful
            • 3. Re: Multiple prices each product

              I have one solution that is both. Discount on product and per customer. This can happen in eCommerce (web) solutions as well.


              Sent from miPhone

              • 4. Re: Multiple prices each product

                I have no doubt about that, Bev


                My question was whether it can be simplified or not - it's something I ask all my clients that bring a dream on the table and utter "implement it" to me.


                Usually people want this and that, to which I simply say "don't forget that for each piece of information you want to see, somebody's going to have to input it, is it really an important piece of info ? Is it easy to have ? Is it really worth it ?"


                Recent example: client wants to see the reason why a patient didn't show up.


                I told him:


                - In your agenda you have right now one million appointments (it's a clinic with many doctors).

                - I will have to create another table to put failed appointments data into (create an extra field in the app table is nonsense)

                - Your desk people will not know why the patient did not show up until it does not show up. They have to call and ask, then enter that data.

                - You will be using that data only once, it's not relevant for the future (we already show how many failed appointments a patient has)


                etc etc.


                Client ended by saying, "ok, you're right, drop it".

                1 of 1 people found this helpful
                • 5. Re: Multiple prices each product

                  Assuming that you have a table for appointments:


                  Why is it nonsense to add a note field for failed appointment?


                  Doesn't the table include other info about the appointment?

                  • 6. Re: Multiple prices each product

                    One idea might be to use mutiple linking fields for your relationship.


                    Product ID



                    In both tables.


                    Now you can create relationships based on the uom and that uom can be whatever is needed for the product id.


                    Instead of linking just by product idea, try a test TO creating the links with the two fields.

                    • 7. Re: Multiple prices each product

                      it's nonsense because when you have 0.001% of records with an info in a field, while the rest have no info, and the table has 1 mil records, you don't add a field, you add a separate table with app UUID and Note. I call it hygiene.


                      The app table has a numeric field 0/1 , App_Missed

                      • 8. Re: Multiple prices each product

                        Consider the following calculation set up as an auto-enter calculation in the line items table:


                        Let ( UnitCost = If ( Invoice::Type = "Wholesale" ; Products::UnitWholesale ; Products::UnitRetail ) ;

                        Case ( Unit = "LB" ; UnitCost ;

                                    Unit = "oz" ; UnitCost/16 ;

                                   Unit = "Ton" ; UnitCost * 2000 )

                               ) // Let


                        This handles all issues in the original post with only two unit price fields in the Products table, one for WholeSale, one for Retail.

                        1 of 1 people found this helpful
                        • 9. Re: Multiple prices each product

                          or "I can do that, time is money, you know..."


                          • 10. Re: Multiple prices each product

                            that's sound good thank you for that.

                            actually I need it to be like this (maximum 5 prices fixed)

                            *from the top part of my photo (product data entry), I would like to be able to add as many as sale unit as i want by setting them up (the bottom of my photo)

                            *but i dont really know how join relationship between my product table and my unit table, i tried many ways but it still wont work


                            • 11. Re: Multiple prices each product

                              Careful, you need one set up for calculating line item costs for an invoice and a different one for managing units of measure possible for a given product. I would not put unit prices for each product's UOM into a table unless absolutely necessary. If there are no price discounts for certain UOMs or price discounts can be defined via a formula, the method that I outline can be modified to use a UOM table.


                              Let ( UnitCost = If ( Invoice::Type = "Wholesale" ; Products::UnitWholesale ; Products::UnitRetail ) ;

                                      UnitCost * UOMTable::ConversionFactor )


                              ConversionFactor then stores values such as:

                              unit      Factor

                              Lbs         1

                              oz           0.0625

                              Ton         2000


                              The relationships for line item cost calclations would be:

                              LineItems::UOM = UOMTable::UOM


                              The relationships for Products would be more complex as you have a many to many relationship. Each product can have many UOM's and each UOM can be used for many products.



                              UOMTable::UOM = UOMProduct::UOM

                              Products::__pkProductID = UOMProduct::_fkProductID


                              To get and manage a list of UOM's for a given product, you'd put a portal to UOMProduct on your Products layout. UOMProduct::UOM can be formatted with a value list of UOM's so that you can select a given unit of measure for a given product.


                              Note however, that if you choose not to use UOM's to calculate unit prices, you can add a field to UOMProduct for that price and then you can use this relationship to get the UOM based price from UOMPRoduct instead of the Products table:


                              LineItems::UOM = UOMPRoduct::UOM AND
                              LineItems::_fkProductID = UOMProduct::_fkProductID


                              The main reason that I am recommending that you not put individual prices in for each UOM, is that this represents a log of data entry labor with an increased chance of data entry errors creeping into your solution.

                              1 of 1 people found this helpful
                              • 12. Re: Multiple prices each product

                                Since you are using barcode, consider the 3D barcode which allows up to 4000 or 8000 characters. Read this and get a lot of text in return. The container for a barcode will return the info in the barcode for you.


                                2017-06-01 03.22.49.jpg

                                Insert this barcode into a container field and use as a calculated text field


                                GetContainerAttribute ( Barcode::Barcode Container; "barcodetext")


                                Or use an iPhone app that reads barcodes such as QR Reader or CNS Barcode (great for FileMaker)

                                1 of 1 people found this helpful
                                • 13. Re: Multiple prices each product

                                  Thank you for recommendations and lead me to the new way which seems better. Sometimes I forget to think about the consequences