6 Replies Latest reply on Oct 30, 2008 6:28 PM by Bart57266

    Custom calculated prices in portal



      Custom calculated prices in portal


      Question 1:


      How do I make portal records display different prices for each user, based on that user's field choices in the parent table?


      I have two tables named Quotes and Prices.

      Prices contains rates per square inch for certain metal signs we sell. For example, some field contents would be:

      Brass, 100-199 sq.in., rate is $1.00 per sq.in.
      Brass, 200-299 sq.in., rate is $.90 per sq.in.
      Brass, 300-399 sq.in., rate is $.80 per sq.in.

      Aluminum, 100-199 sq.in., rate is $.80 per sq.in.
      Aluminum, 200-299 sq.in., rate is $.70 per sq.in.
      Aluminum, 300-399 sq.in., rate is $.60 per sq.in.

      Quotes has fields for Height and Width, plus a calc field for SqInTotal.
      Prices has fields for Metal, SqInMin, SqInMax, Rate, etc.

      If my users need to quote a 10x15 sign, a total of 150 sq.in., I need FMP to display the following records, possibly in a portal to Prices:

      Brass $150.00 (which is 150 sq.in. x $1.00)
      Aluminum $120.00 (which is 150 sq.in. x $.80)

      The Prices records remain the same at all times. They are really just a listing of items we have available for sale. The relationship between these tables is based on Quotes::SqInTotal being >= the smaller sq.in. number in Prices, and <= the larger one.

      My problem: One user may be quoting 10x15 while another is quoting 10x14, so they are actually viewing the exact same Prices records. The total cost of each sign will be different, however. How and where do I place the calc field that is to display custom prices for each user?


      Question 2:


      Is there a better way to set this up? I was just using a portal to Prices in order to display a constanly changing list of results that are based on what the user enters on the Quotes layout. There are other Quotes fields that affect the list, but I was trying to keep this simple in the forum. For example, there will actually be multiple choices of each metal because of finishing options such as satin, polished, etc. I am setting it up to display all finishes unless the user checks one. When one is checked, the list shrinks down to only the matching Prices items.


      Any suggestions for setting this up will be appreciated, so thank you in advance.

        • 1. Re: Custom calculated prices in portal



          Thank you for your post.


          There are a number of different ways to set this up, but I'll try to give you that is easiest to understand.


          It sounds like you have your Quotes and Prices tables hooked up appropriately, so no need to review this.


          Once you determine the Square Inch total, you need to compare it to the minimum and maximum of the appropriate record.


          Using your example, let's assume you enter "Brass" into quotes.  If you have the portal set up properly so that the Brass records are sorted by SqInMin, then you should have the correct order.  What we want to do is evaluate each occurrence to see if the value falls in between the ranges.  Actually, we only need to check the upper range, because earlier records will exclude the lower ranges.


          GetNthRecord function returns the Nth record of a related set of records.


          Try the calculation:


          Case (SqInTotal <= GetNthRecord ( Prices::SqInMax ; 1 ); GetNthRecord ( Prices::Rate ; 1 ) * SqInTotal;

             SqInTotal <= GetNthRecord ( Prices::SqInMax ; 2 ); GetNthRecord ( Prices::Rate ; 2 ) * SqInTotal;

             GetNthRecord ( Prices::Rate ; 3 ) * SqInTotal) 


          This takes the SqInTotal in Quotes and compares it to the first SqInMax in Prices.  If the SqInTotal is less than the maximum, then we use the rate associated with that related record.  If not, we compare to the second maximum, and if less than or equal to, we get the second rate.  Since you only have three options, the last option defaults to the value in the third related record.  If you have more related records, you will need to expand the calculation accordingly.


          If you need clarification for any of the above steps, or if you run into any difficulty, please let me know.



          FileMaker, Inc. 

          • 2. Re: Custom calculated prices in portal

            Thank you for your help.


            I do have it choosing the proper square inch range already. The relationship is based on two fields in Prices:


            AND SqInTotal<=SqInMax


            This pops the correct Prices records right up in the portal.


            My problem is with the final price calculation. We multiply SqInTotal by the Rate to get the total sign price. However, I realized that I can't calculate this in a Prices field called TotalPrice because many other users may be viewing the exact same Prices records for slightly different sign sizes. Global variables won't work either, I don't think, because they cannot be unique for each record.


            Unless someone has a way to show a user-specific value in each record, I may have to create an individual Prices record for every possible size of sign. Then it can store the TotalPrice for each size, which is fixed regardless of which users view it. Because of a few other price-sensitive specs not mentioned in my original post, this will mean 36,000 records for each metal, or 252,000 records for all of the metals we offer. We have multiple suppliers as well...


            4 Suppliers

            7 Metals

            2 Types of inscriptions

            4 Thicknesses

            3 Finishes

            1500 Sizes


            These things can get complicated real fast!


            Again, thank you for your help.


            • 3. Re: Custom calculated prices in portal

              Hi Bart57266,


              I think you have the ability to make it work right now.  If my understanding is correct:


              - In the Quotes Table you have a record that shows the total area of the sign

              - That Quote Table record correctly displays the range of unit prices from the Price Table, in a portal

              - You have a range of prices (in your example) because you have not yet specified the material

              - You could modify the relationship to also include the customer's final material choice after they have assessed their options, and then there would only be one value of Unit Price that it would find - so no need for a portal after that point.

              - The Unit Price field displays in the portal because you have chosen it via: RelationshipName::UnitPrice

              - You can make that calculation now: in the Quotes Table, create a calculation which is that related field x  the Area.  That will be the correct Total Price (ie: RelationshipName::UnitPrice * Area )

              - I don't understand why you are worried that someone else may be viewing the same record in the Price Table - all the Quotes Records can use the Price Table records simultaneously


              However if I have missed a point about why that would make a difference, you could 'copy' the correct Unit Price it finds across into the Quotes Table, by creating a Unit Price field in the Quotes Table and making it a LookUp value, using exactly the same relationship.  It will copy the Unit Price value it finds into that field in the Quotes Table, and you can use it in calculations from there.


              That has a characteristic that might be good or bad, depending on how you want the file to work.  Once the Unit Price is automatically copied over to the Quotes Table it will not change.  That is bad, if you would want each OLD quote to update every time you change your Prices.  (That would be an unlikely thing for you to want.)  It will be a good thing if you want to keep 'frozen' a record of what price you quoted at the time each Quote Record was created.


              Hope this is a help,


              • 4. Re: Custom calculated prices in portal

                It would be really nice for my users to see the total price of each item in the portal list. Sometimes they are talking on the phone with a customer, and need to see how much the sign will cost in Bronze, Brass, Aluminum, etc.


                But how can I calculate prices for both User A and also User B who may be accessing the same Prices records?

                • 5. Re: Custom calculated prices in portal

                  Can I assume that there will be a limited number of options at that stage?  Like, maybe 6 different materials?



                  If that's the case, then it is easy to create the fields for the 6 possible options (either as 6 fields or as one field with 6 Repeating Values).  If you do that twice you could have, side-by-side, a column of 6 'Materials' and beside that a column of 6 'Unit Prices'.  Then beside that, a column of 6 'Total Prices' as the multiplication of the Area * Unit Price.


                  Basically, you want to copy the 6 (say) rows of the portal and replicate them temporarily on the layout, and do the Total Price calculation on them there.


                  It is easy to create a script that:

                  - Goes to the first row of the portal

                  - Captures the first Material as a $Variable ($Material, say)

                  - Captures the first Price as a $Variable  ($Price, say)

                  - Goes to the first repetition of the Material field and sets the captured Material value there

                  - Goes to the first repetition of the Price field and sets the captured Price value there

                  - Loops round the script, going to the next Portal Row, the next repetition of each  'captured' field, etc


                  You will then have created an array on the layout which is a trapped 'copy' of the view of the portal, except with the extended calculation of the Total Price.


                  If you sort the portal in ascending Unit Price, for example, you would also get the copied list by ascending Total Price.


                  This may not be a very elegant way to create an array; in some ways it is a very similar problem to the 'Search Performance' problem earlier.  I'd welcome a neater solution if anyone has one - I recognise, for instance, that this solution works well only as long as there are a limited number of potential options.




                  • 6. Re: Custom calculated prices in portal

                    Thank you Sorbsbuster.


                    Just for fun, I am going to create those 200k plus records with a simple script that has several nested loops. Then the performance will be tested to make sure it is fast enough. Hopefully FMP will surprise me.


                    Those records won't be storing much info at all, so overall file size should not be an issue.


                    I'll let you know how it goes.