Custom calculated prices in portal
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?
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.