I don't think you can actually do what you want by using the Execute SQL script step. This script step (as far as I know), is used to be able to interact with other ODBC datasources, but not the current database itself.
To be able to script a query, you need first to have a layout based on the table occurence you want to search on. Then in the script you do this (this is a really basic example):
Go to Layout (choose the layout)
Enter find mode
set field (fieldname ; value you want to have)
The way the result will be displayed, really depends on how your layout is setup. If you want a list, you need the layout to be either in list view, or table view.
Now if you really want to use SQL commands, you can use the ExecuteSQL function (not script step) which will return a result in text format. For more information concerning this function, I recommend to have a look on http://www.filemakerhacks.com
They have a PDF reference document, and a lot of articles explaining what you can do with it
What I'm actually tring to do is create a unique description based on anwering a list questions with differnet answer.
So if I answer question 1 with 2nd option and answer question 2 with 4th option and answer question 3 with 9th option and so on
I then end up with a unique ID for each combionation of answers ie 02-04-09 and so on.
The answers drive a price calculation and then I want to automaticlly get either Filemaker or XL to create every unique ID with its price.
I will then be importing the ID and Price into an online tool. This means that all the price are calculated outsife of the online tool and then imported in and then when the same question are answer in the online tool it can display the appropriate price.
I can keep the rates used in the calculation in tables in Filemaker and kep them upto date
Do you think there a better way of doing this
Okay, so if I understand you well, you already have a price list with an ID for each price, and then the user answers to a list of questions and depending on his answers, he gets a specific price?
Execute SQL  - script step is for interaction with an ODBC source for INSERT, UPDATE, DELETE queries. Results are IN the source.
ExecutSQL() - function is for making queries to any valid Table Occurrence (as on the Relationship graph) with the SELECT statement. Results are returned to a TEXT field.
To SELECT with an ODBC source, the Import script step is used. Although the SELECT may also be used within the INSERT statement.
To NOT use any queries a valid ODBC source can be on the relationship graph and layouts based on the table(s) made. Standard FileMaker manual and scripted finds and other actions can be made. This is referred to as ESS.
I agree that the script as written is probably not valid. We need to know if Andrew B is using FileMaker or FileMaker with an ODBC source.
Thanks for all you help
I will put this info infront of my Colleage later and we will hopefully find the solution in all you have provided
If not I will be back tomorrow
In that case you don't even need to do a query. All you need is an auto-enter field or a calculated field that will generate an ID depending on the answers. If you then link that field (in the relationships) to the ID field of the price table (I assume you have one, and if not you definitely should create one).
Once this is done you can easily export the ID and the related price.
I attached a really basic example file
test.fmp12.zip 10.1 K
Great I will try this next
Just thought about what you said in last post
Does your answer mean that I will be able to create all the combinations of the ID's
Ulitmtley I want to run a script that will create all the ID's and calculate the prices
Then I will export ID and Price to online software
Check my example file,
I have 3 tables
- Test ( Question 1, Question 2, ID )
- Prices ( ID, Amount )
- Answers (ID, Answer )
In the Test table, the ID field is automatically calculated depending on the answer you give. As the ID is linked to the price table, you can automatically see the price without having to run any scripts.
Just looked at your example FM file
I see how the ID is created by answering the questions
That is spot on
Now I just need to create all combination of ID's automatically for 20 to 40 questions
Some qustion might have more than 10 answers
We could end up with up to 1 million answers for some of my products calculations
This is brillinat
thanks for all your help