1 2 Previous Next 25 Replies Latest reply on Jun 8, 2010 7:38 AM by alexandreriva

    Complex Pricing Structure



      Complex Pricing Structure


      I originally asked about the difference between Order and Orderline. I got a very helpful answer from AndreasT which I've been referring to.

      However, after trying to work out our pricing structure to work on the Orders Page through a portal, I find myself stuck again because of the complexity of the product and pricing options.

      Here's what we have: 111 possible prints available (some are same title but different size)

      these are sold either flat, shrinkwrapped, matted and shrinkwrapped, or fully framed (pricing for framing on each print is different based on type of mats and frame etc.)

      the prints are also available in art card form (all the same price) 

      we also sell three or four items without the framing issue,

      and we sell some consigned items 

      The trickiest part for me is arranging the portal which will allow all these possibilities.


      In the portal I'd have a column for quantity, one for product name, then description (so the customer knows which size print they've got for example), then... a drop down menu within the portal (if that's possible) to choose the type of 'packaging' (ie: either flat, framed, etc), then the appropriate price would have to be calculated on these multiple predicates!


      Perhaps I'm going about this the wrong way. I do have all the pricing options on each product record, but I'm still not sure how to arrange the order in the natural way one would use when filling out an invoice by hand. 


      We sell both Wholesale and Retail and some Consignment which I think can show up on the orders page but again the pricing is not straight forward; the wholesale prints are 50% retail, but the wholesale framing is -25% of the retail and Consignment is a whole different matter. 

      I have a field for customer type, so I think I can make the different calculations based on that field. 


      Could some one please steer me in the right direction? 

        • 1. Re: Complex Pricing Structure



          Thank you for your post.


          I don't know how you have your database set up.  Ideally, you would probably have a table for the prints, and a table for the different options for each print.  For example, in your "Prints" table, you would have a description of the print, photographer/artist, year, etc.   You would probably have an Print ID field that you can relate to other tables (like for different options or perhaps a sales order table).  In your Different options table, you would have the Print ID field, the type of product (flat, shrink wrapped, framed, etc.) along with a Price field.  This is probably needed because some prints may only be available matted and shrink wrapped, while others come framed, flat, shrink wrapped, and possibly others.


          If you have a portal into your Options, you can enter a Print ID, a description of the print displays (perhaps a picture, too), and then in the portal, all the different options appear for that Print ID.  You can then select an item from that list, which then brings across the price.


          You can then enter a quantity (which multiplies by the price) to get an extended price.  You can then use another calculation against the Wholesale price.


          If (Customer Status = "Wholesale", Total * .50, Total)


          Obviously, this is a simple case.  You will also need to check for framing (only 25% off) and Consignment.  I'll leave that to you.


          This should get you pointed in the right direction.  If you have any questions or need clarification, please let me know.



          FileMaker, Inc. 

          • 2. Re: Complex Pricing Structure

            Thank you TSGal,

            What I do have set up is a Products table and on each record of the table is the print price, framed print price, etc... all the options using calculation fields to do the math.

            You're suggesting that these options would work better in a separate table (maybe called Product Options). Am I right in thinking that the table would be 'blind' and the information from it would then be available through a portal in the Products table?

            In my Orders records then, I could have my portal to Orderline (which is linked to Products and would be also linked to Product Options) where I would have the columns including quantity, product etc and product options would also be able to be chosen through the Orders portal? That would be a double portal essentially wouldn't it? 


            If I'm understanding you correctly, I think I would have to recalculate the way we price the framing. At the moment it's based on all the different options multiplied by united inches (width plus height), the complication being that different prints may have two instead of three mats, or regular instead of UV glass, or the frame moulding (which is priced differently again) is different on different prints. Generally, once we've worked out a frame price we compare it to others and round it off appropriately. Pretty complex! That's why initially when starting up this database, I just added the individual price of framing, mat and shrinkwrap, and/or shrinkwrap, on each print's individual record, using a calculation field for all the possible results.


            So... am I understanding you correctly?


            • 3. Re: Complex Pricing Structure



              Thanks for the clarification.


              Sorry for the confusion.  I assumed you had two table set up.  If everything is one table, and everything seems to be working, then there is no reason to change it.


              If you have an Orders table, then you probably already have a link/relationship to the Prints table.


              One option is to have a checkbox for a discount on each item.  If it is a framing, then it would be 25% off.  For others, 50% off.  Consignment....  I don't think we touched on that.  :-)


              The reason why I had separate tables is that you may not have the same type of options for each print/photograph.  For example, you probably wouldn't have an option for shrink wrapped on an Ansel Adams photograph.  :-)


              If everything is equal, then I would leave well enough alone.


              Maybe the better question to ask now is "Is there something not working that you'd like to see work?"  Maybe that will get us pointed in a direction that would be helpful.



              FileMaker, Inc. 

              • 4. Re: Complex Pricing Structure

                As I am just setting this database up for the first time, having never worked with anything beyond spreadsheets before I believe I'm not quite understanding where I need to go.

                I have a table for products, Customers, Orders, Orderline (because of the many to many relationship otherwise between products and orders), Invoice and Invoiceline (those set up because they were reccomended as necessary by another forum member).

                I'm having trouble putting into practice, what I would do on paper. That is, to write up an invoice (this being a Customer's "order" as well) which includes customer name and adress, Order or Invoice #, date, and then how many of each item is being purchased, sub-total, taxes, shipping and Total. I've got the interaction between tables so that I can bring up customer information on an order. Its the product pricing that I can't figure out how to organize.


                My first understanding was that I needed an Orderline table which would contain the calculations needed  and the results of these would show in a portal in the order record where the final total and taxes could be shown.


                My question below stemmed from trying to create a usable layout for my "Order" records (which I thought could then become Invoices through a script/button for printout and sending as necessary), but I couldn't figure out what to create in Orderline in order to give a range of possibilities who's results would show up in orders. I guess I'm actually still having trouble defining the dividing line between Order and Orderline. I think the orderline is a table but you don't produce records on it. Is that right?


                I'm sure that part of my issue is conceptual. I need a visual example in order to understand what it is I need to build and my idea of old fashioned paperwork is not working for me.


                I apologize for this sounds vague, but I'm having trouble being clear as I don't know what I'm talking about! :smileysad:  



                • 5. Re: Complex Pricing Structure



                  It sounds like you've progressed quite a bit from your spreadsheet, so kudos to you.


                  I'm trying to think of this more of an invoice.  If I understand correctly, an "order" contains one or more "orderlines".  That is, a customer order may contains a framed print and a matted print.  Those two prints are orderlines that make up one order.


                  Here is a simple database file that may give you some ideas.


                  Create a table (which you may already have) INVENTORY with the following fields:


                  PRODUCT ID (Text)

                  Description (Text)

                  Price (Number)


                  Notice that PRODUCT ID is uppercase.  This will be a "key" field when relating to another table.


                  Enter the following data:


                  PRODUCT ID - Description - Price

                  1 - Product 1 - 1.00

                  2 - Product 2 - 2.00

                  3 - Product 3 - 3.00




                  Next, create the table ORDERLINE with the following fields:


                  ORDER ID (Text) 

                  PRODUCT ID (Text)

                  Quantity (Number)


                  Switch to the Relationships tab, and attach ORDERLINE table to the INVENTORY table via PRODUCT ID fields.  When the relationship is created, double-click on the icon connecting the two tables and be sure to check "Allow creation of records in this table via this relationship" on both sides (left and right).


                  Switch back to Fields tab, and create one additional field in ORDERLINE:


                  Extended Price (Calculation: Number) =  Quantity * INVENTORY:: Price


                  Do not enter any data.




                  Create a table ORDER with the following fields:


                  ORDER ID (Text)

                  Name (Text)


                  (Note: The Name field would actually be a link into your Customer database, but for this example, let's leave it as a text field)


                  Click the Relationships tab and connect the ORDER table to the ORDERLINE table using the ORDER ID field in both tables.  Again, double-click the icon connecting the two tables and check the options "Allow creation of records in this table via this relationship" on both sides (left and right).




                  Exit out of Manage Database and go into Layout Mode (View menu).  Select the layout for ORDER, and on the left side of the screen, click on the portal tool (just below the oval tool).  Draw a fairly wide box with at least four lines.  You will then be prompted for the table. Select ORDERLINE, and click OK.  You are then prompted for fields.  Select PRODUCT ID from ORDERLINE, select Description from INVENTORY, select Quantity from ORDERLINE, Price from INVENTORY, and Extended Price from ORDERLINE.  Click OK.


                  Go to Browse mode, and create a new record.  Enter a name "Kat4", and in the portal, enter "1" into the first field.  This looks up the information in INVENTORY, and the Description and Price will appear.  Enter a quantity of 2, and in the Extended Price, 2 will appear (2 * Price).  (You can always format the field to show currency).  In the next line, enter "2" into the PRODUCT ID, and the description and price appears.  Enter a quantity of 3, and Extended Price should display 6.


                  Add a field to "ORDER":


                  Total (Calculation: Number) = Sum (ORDERLINE::Extended Price)


                  You can then display the total of the order before discounts and taxes.




                  This should hopefully give you a good starting point and understanding.  If you have any questions or want clarification, please contact me.



                  FileMaker, Inc. 

                  • 6. Re: Complex Pricing Structure

                    Thank you so much TSGal,

                    I'm working on this right now. I know if I see how it works, I'll understand it alot better!


                    • 7. Re: Complex Pricing Structure

                      Thanks again TSGal,

                      That step by step process was just what I needed to understand what I need to be doing to create orders etc.

                      I followed your instructions and everything worked perfectly, so now I'm on my way with the orders in my database.




                      • 8. Re: Complex Pricing Structure

                        Greetings, I have been playing along with this because I have such a similar structure as Kat4 does, I am a spreadsheet guy writing a database for the first time... So I have the freedom to set this up anyway I want...  but the lack of knowledge to do it right.


                        The question that I am struggling with is how to write a case calculation to choose the right price. 


                        I have 2 "customer types" (Vendor and Customer) and two location types "State" (in state and out of state lets say Az) so I have 4 prices to choose from with each of my products. 

                        1. Vendor, Az or Vendor, not Az

                        2. Customer, Az or Customer, not Az

                        A vendor will never get a customer price and a customer can't get the vendor price. 


                        I have a product page set up with each of the prices on it. I Have a portal window, on my invoices page (thats where the orders are done) and it works with a single price lookup off of the product ID on the Products page, (and I have a line Items TO inserted between, for the many to many),  so I know the relationships are good.  


                        I want to put in a case calculation, but should it be a series of GET calculations or a series of IF calculations?


                        Thanks in advance... 


                        • 9. Re: Complex Pricing Structure



                          Thank you for your post.


                          There is a Case () function.  You could use it as follows:


                          Case ( Vendor = "Az" and Customer = "Az"; result1 ;

                               Vendor = "Az" ; result2 ;

                               Customer = "Az"; result3 ;

                               result4 )


                          That is, check to see if Vendor and Customer are in Arizona.  If so, first result.

                          Then, check to see if Vendor is in Arizona.  If so, we know Customer is not in Arizona because the first result would have been issued.

                          Next, check to see if Customer is in Arizona.  We know Vendor is not in Arizona because one of the first two results would have been issued.

                          Finally, if the first three results are false, the Vendor and Customer are not in Arizona following the same logic.


                          You could also do a series of If () functions, but the Case () function makes it easier to read.



                          FileMaker, Inc. 

                          • 10. Re: Complex Pricing Structure


                            Thanks for getting back to me, I have been struggling with this for a while so be gentle. :) oh and I am very new...


                            If we check to see if vendor and customer are in az then result 1... I don't know what result 1 would be.

                            I need to see if it is vendor and Az not vendor, Az AND customer, Az. a vendor and customer have separate pricing that never share...


                            The second question is.. Do I need to make it from the original TO to make it work? ie... here is what I wrote but it keeps returning the same results no matter what I have chosen...


                             This was written on my Product T.O. and I created a field called Price Check  it is in a portal on my INVOICE T.O.


                            Case(                                        */ these notes are not in my original calc. i put them here just to make it clear for you /*

                            (Contact::Cutsomer Type = "vendor") and (Contact::State_Province1 = "az");             */ this comes from my CONTACT TO /*

                            (Vendor Az Price);                                                                                            */ this is on the PRODUCT TO /*

                            (Contact::Cutsomer Type = "customer") and (Contacts::State_Province1 = "az");

                            (Retail AZ Price);

                            (Contact::Cutsomer Type = "vendor") and (Contact::State_Province1 ≠ "az");

                            (Vendor Non AZ Price);

                            (Retail Non AZ Price)) 


                            The reason I did this was because each size of product has its own 4 levels of pricing... so 4 prices for each product...  

                            thanks, for the time,

                            I hope I didn't confuse you too bad. 

                            • 11. Re: Complex Pricing Structure



                              Thank you for the clarification.


                              Sorry for the confusion on my part.  The way you wrote your original post led me to believe there were four pricing structures based upon the values given, and I tried to put them all into one calculation.  Your calculation is fine, but the third condition for state is not needed since it is already known it is out of Arizona.  That is:


                              Case ( Contact::Customer Type = "vendor" and Contact::State_Province1 = "az" ; Vendor Az Price ;

                                 Contact::Customer Type = "customer" and Contacts::State_Province1 = "az" ; Retail AZ Price

                                 Contact::Customer Type = "vendor" ; Vendor Non AZ Price ;

                                 Retail Non AZ Price )


                              You need to make sure you reference the correct table occurrence, because if you are in the second table occurrence, and you reference fields in the first table occurrence, you may not be on the same record and you will not see the results you expect to see.  Be specific.



                              FileMaker, Inc. 

                              • 12. Re: Complex Pricing Structure

                                Cool, Ok I fixed my calculation and it works but only kind of. It now only returns the in-state retail price for each product (which is the first choice on each product page).  


                                Does it make a difference that the Customer Type is a set of radio buttons that they check if its a vendor or customer should I change it to a drop down menu?


                                Let me know what info you need to help me on this one...



                                • 13. Re: Complex Pricing Structure

                                  I changed the customer type to a drop down and it didn't help..





                                  • 14. Re: Complex Pricing Structure



                                    Thank you for the additional information.


                                    No, radio buttons or drop down should not matter.  However, if there is a space at the end of one of the values, then that would cause an issue.


                                    For example, "Vendor" does not equal "Vendor " (notice the space).  Please check this first.


                                    To help provide a clue to what may be causing the problem, create four calculation fields, all resulting in Number type:


                                    CALC1 = Contact::Customer Type = "vendor"

                                    CALC2 = Contact::Customer Type = "vendor" and Contact::State_Province1 = "az" 

                                    CALC3 = Contact::Customer Type = "customer"

                                    CALC4 = Contact::Customer Type = "customer" and Contact::State_Province1 = "az"


                                    These will return 1 if true, 0 if false.  You can then compare against the data in the record.



                                    FileMaker, Inc.

                                    1 2 Previous Next