Jump to content

Multiple Price Problem in FMProdSolutions Template

This topic is 7583 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!!

Link to comment
Share on other sites

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.


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.

Link to comment
Share on other sites

This topic is 7583 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.