Multiple Price Problem in FMProdSolutions Template

[MerlinWannaBe]

I cannot see how to script or calculate the result I need. Any help is greatly appreciated. I'm new to this depth within FMP. I'm using a copy of the "FileMAker Solution Framework" provided at FM site for Sales of Inventory via its Invoicing logic, etc.

I have added a "Select field" in Customer Record, values = blank,1,2,3,4,5,6. Let's say the"Select field" = 3. When adding a line item to an Invoice, using a drop-down list attached to the Product ID field in the line, I want the "Price" field to pull or look up the price (in the Products File) from the "Price3" field. If =4 use "Price4" field, etc. I had hoped it could be done using the "Calculated Value" feature in the Field Options of the Line Item "Price" field.

I've tried all I can think of. The books I have are no help. HELP!! HELP!!

[Fenton]

I don't have the FileMaker Solutions open, but I think I can give some general advice that will help; though you may not like it.

Whenever you find yourself creating fields like Price1, Price2, Price3, etc., stop. You are leaving the boundaries of sound relational design. It may appear to save time now, but you'll usually pay later.

They simply did not add this functionality to the template, but you can, if you do it correctly.

In the case above, rather than having those multiple fields, you could create another simple line items file, Product_Prices. It would have ProductID, Price, and whatever you are using for your Select field.

Concatenate (put together) ProductID & " " & Select as a text result calculation field. Do the same in your other line items file, where you're choosing products.

Now a match from that file, using the Product|Select field will get the correct price.

Ex.:

ProductID is P002, Select is 1.

P002 1 =::P002 1

These multiple prices could be viewed in a little portal in Products, based on ProductID relationship, allow creation of related records, making it easy to add/modify prices.

[Lee Smith]

Hi Merlin,

You might want to take a look at this discussion about the integrity of the Framework invoice file:

Click here.

And replace it with the one provided by Helpful Harry.

HTH

Lee

[MerlinWannaBe]

Thank you sooooooo much for this heads up. Hard to be new and learn from corrupt files! FM Forums is an amazing resource!

Thanks again!

[MerlinWannaBe]

Fenton,

I see your point.

Thanks for the advice.

I'll give it a shot!!