Well, you mentioned the solution
You just need to create another table with your items, linked by an item code the item name. Then setup a relationship between your line item and your lookup item tables.
THere are many examples of this floating around. There should be a similar setup in one of the template files provided by Filemaker.
If your starting from ground zero with Filemaker, take a look at the training materials here on the forum, or Lynda.com. You can get a free trial on Lynda.com and they have some good Filemaker training on the site.
Thank you for the feedback. Do you know of any videos that might show how to do this? I have watched several videos about setting up relationships where one drop down relies on the next one but i cant figure out how to get the one filed to just calculate. I will try your method and see if i can figure it out and also take a look at the website mentioned. Thank you
Looks like you are starting from and playing with modifying one of the Starter Solutions, Expenses...cool. From your question I'm assuming you want to swap out Category for you field Product ... (1) create a new table for Products with fields for ProductName and Price, populate with some data, and relate it to the portal Data | Expenses. check attachment. (2) Change the drop down for Category (eg.Product) to be a Value List from that new Product table. check attachment. (3) Change the field Expenses Amount to be a lookup on Auto Enter of the price in your Products table based on the Product selected . check attachment. Because field is a number field with a auto calc at time of creation, you can manually change price if needed and the price will not change if the price changes later in the Product table (price increase) unless you re-choose the product in the portal.
Good afternoon whcl2016,
I hope your day is going well. Without being familiar with the schema of your solution I can't use the proper names of your tables and fields, but you should be able to use ExecuteSQL() as an auto-enter calculation for the Amount field displayed in your portal. Here is an example:
"SELECT \"enter the name of your amount field here\"
FROM \"enter the name of the table that contains the amount field here\"
WHERE \"enter the name of the category field here\"=?";
""; ""; Name of the table the portal is based on::name of the field that contains the selected category)
Hope this is helpful. Have a great weekend!