5 Replies Latest reply on May 13, 2016 1:41 PM by BillisSaved

    Auto Populate Price


      I am racking my brain trying to figure out how to get this to work.  I can do it no problem in excel but being a newbie to Filemaker, not so much,. I need to have a price auto populate for a product based on a selected item in a drop down menu.  If you select any of the items under the category section of the drop down, i need the amount field to populate the price based on some sort of spreadsheet or database where i designate the price of each item. Please help!


        • 1. Re: Auto Populate Price

          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.

          • 2. Re: Auto Populate Price

            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

            • 3. Re: Auto Populate Price

              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.

              • 4. Re: Auto Populate Price

                here is last screen shot of Expenses Amount field defined

                • 5. Re: Auto Populate Price

                  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:


                  ExecuteSQL (

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


                  God bless,