7 Replies Latest reply on Nov 12, 2012 5:01 PM by comment

# Help with finding proper price based on entered information

Hello,

I am looking for some helpwith a database we are building. We have a table of products. Each product has 3 different price points based on volume. Each of the prices is a record in the table. When building an invoice, we ask for the quantity and size of the particular product being produced. We then use these figures to calculate the square footage of each particular product to be produced. Now, here is the issue, we would like the pricing to automatically be pulled from the table based on the square footage of product to be produced. We currently have the field set up as a calculation using a Case statement and a GetField function to evaluate the number of square feet needed. The problem is that I cannot figure out how to use the GetField function to choose the proper field for the proper product. The Product table has maybe 30 products in it. Each product has 3 price points, so that is about 90 records in the table. If there was only 1 product in the table with 3 different price points, I can get it to work. But, since all products are in 1 table, I am struggling to get it to choose the correct field from the correct record. ANy ideas that might help? I am working in FM V12.

• ###### 1. Re: Looking for some help

I think that you need to rethink your product table.

Without knowing more I would suggest that your product table have a record

for each product/price point then you can creat a relationship that will

pull in the correct price. Instead of 30 records with 3 prices each you

would have 90 records with 1 price each.

--

iPhone

Pete Minich

Direct/FaceTime/Text  - 781.223.8884

• ###### 2. Re: Looking for some help

Pete,

I do have 90 records with 1 price in each. I am having trouble figuring out how to get the Case (GetField) statement to choose the proper price based on the calculation of square footage. FOr instance, a salesman chooses a product (drop down), enters the quantity and sizes. This information is sued to figure out how much square footage is needed to be produced. Now, I use the Case (GetField) state ment to evaluate the amount of squarefootage (3 levels). But, once I process that statement, I do not think the statement knows which product was entered and therefore, which price point to pull. Does this make sense? I am not sure if I am going down the wrong road and maybe should be approaching it differently. Thanks in advance for ANY help.

• ###### 3. Re: Looking for some help

Maybe I should delete the table and rebuild it. I think I am going to try that. It makes sense (your response) and this is what IO was thinking when I was building it. Maybe my relationship is off or something. Please let me know if you can think of a different approach as well.

• ###### 4. Re: Looking for some help

rdgeib wrote:

I do have 90 records with 1 price in each. I am having trouble figuring out how to get the Case (GetField) statement to choose the proper price based on the calculation of square footage.

You should be using a lookup to choose the price, not a calculation. Otherwise your existing records will recalculate when you update your price list.

A lookup is easy to do once you have a relationship based on:

LineItems::ProductID = Prices::ProductID

AND

LineItems::Quantity ≥ Prices::FromQuantity

with the Prices records sorted by FromQuantity, ascending descending.

---

P.S. I suggest renaming the thread with something more descriptive.

Message was edited by: Michael Horak

• ###### 5. Re: Looking for some help

Exactly. You shouldn't be using a getfield. I would set each price record

with a min/max sq footage that it applies to. Then build your relationship

for the invoice line price based on the type of relationship described.

--

iPhone

Pete Minich

Direct/FaceTime/Text  - 781.223.8884

• ###### 6. Re: Looking for some help

Michael,

Thank you very much for your response. I am going to look into this.

• ###### 7. Re: Looking for some help

pminich wrote:

I would set each price record

with a min/max sq footage that it applies to.

Actually, the min boundary is quite sufficient, for example:

ProductID
FromQuantity
Price
123125.00
1235022.50
12310020.00

This way, any amount above 100 is provided for, and you don't need to match the min to the previous max.

Message was edited by: Michael Horak