Help – Return Signal Value from Multiple Value Table

von | Feb. 21, 2017 | FM Blog

I am coming to FileMaker for the first time in more years then I have toes. I have gone through the Lynda.com videos, spent hours on youtube, and search several forums. I can’t wrap my head around how to solve the following problem.

I am putting together a quoting tool in FM. Currently, I have a very built out version in Excel, and I am struggling converting it to FM. The principle issue revolves around the cost of products and the multiple price levels for each product.

Current Solution

Essentially the pricing tables have six fields SKU, NAME, PRICE1, PRICE2, PRICE3, PRICE4. In the current Excel model, the quoting tool gets the PRICE based on a value stored in the customer table. For example, in the customer table, there is are fields CustomerName(pk) and price level. Then on the quoting page, I enter CustomerName(fk) and using a vlookup a value is returned of PRICE1, PRICE2, PRICE3, or PRICE4 into the tariff field. In another field using a case statement, a numeric value is returned based on the value in the tariff field. This numeric value is then used as the column count in a separate vlookup to return the correct value. Once the quote form is completed using a macro the data is snapshotted to a historical quote table. Because there are no formulas carrying over the data to the historical quote table, if the values in the pricing table change the historical quote table doesn’t.

Current Data Structure for Pricing Table

SKUNAMEPRICE1PRICE2PRICE3PRICE4
111Widget$1$2$3$4
222Thing$2$5$8$10

Goal
1. Have FM select the correct price based on price level information stored in the customer table.
2. Have a record of historical quotes that will not change when the pricing table is updated.
3. Make the number of pricing tables easy to scale in the future. Either make each pricing level its own table or limit the amount of script coding.

I am not sure it I am dealing with a structural problem caused by my excel layout or if I am just not understanding how to structure the keys and maybe filters.

Any help you can provide would be deeply appreciated.

Powered by WPeMatico