If I understand your post correctly, you could use ExecuteSQL to accomplish this task, as long as the Tooltips table has a table occurrence (TO) in the current file.
ExecuteSQL ( "SELECT Tooltips.explanation FROM Tooltips WHERE Tooltips.xyz = 'sort by year'" ; "" ; "" )
Note that I'm doing this mostly in the blind and you might have to make some adjustments.
Don't you mean the function ExecuteSQL ( ) (as opposed to the script step Execute SQL) ?
Good example, though.
Yes! Function not Script Step...
My examples are using fields but you get the same result.
Thanks alot. That really helps. to Mike and PSI
Dear John, I just looked at it and works great.
How do I have to change it so it comes from an external file
ExecuteSQL ( "SELECT Tip FROM ToolTips WHERE Link=?" ; "" ; "" ; "NameFirst" )
That table "Tip" is in file "Settings"?
The different file is set up in External Data Sources and is called Setting.
Just put the Tip table on the graph in the main file and it will work. This is the same if you want to access global fields from another file.
And it works like stated. Sensational John Thanks a lot. Really this changes my whole perspective of FM.
As this opens a total new perspective I have an other situation for ExecuteSQL
These are the tables and their relationships: (arrow shows relationship)
Seeing on "Account" all its "Persons" is simple with Portals. (but FM can't have portals within portal)
so what I would like to see in a field on "Person" is:
a. in a field "All Phones on "Person" all its numbers, (a person can have 1, 2, 3 etc. numbers) " Cell 777-555-6666 Direct Phone 222-444-555 Home 333-777-6666
b. in a field "Cell" on "Person" which shows just his cell, so the ExecuteSQL has to depend on two tables ("Phone" and "Phone Purpose") and two variables ("Phone::Person ID" and "Phone Purpose::Person ID" )
How would the ExecuteSQL look for
a. ExecuteSQL( )
b. ExecuteSQL( )
Remember that ANY table/table occurrence referenced in an ExecuteSQL call must be on the relationship graph. However, any "relationship criteria" is ignored, so any query to a TO will seem as if the base table is called. So you need not have any relationship to the table/T.O., but it must be on the graph for the query.
Thanks I understand that. Its easy and FM is a really great product.
Have a great weekend
I simulate a portal in a portal with a field and relationship not SQL although it could work.
First of all SQL doesn't like spaces so you should remove spaces in field names. I can't tell what tables you really have based on the chart. I do hope phone purpose isn't a separate table from phone?
Assuming the PhoneID and Purpose ID are in the same table
The structure would be basically the same as the Tool Tips.
SELECT PurposeID, PhoneID
FROM the table?
WHERE PersonID=? ; ": " ; "¶" ; Persons::PersonID ) - don't quote me on the separators but i think it would work?
----------------if you have multiple criteria like isolating a single type.
WHERE PersonID=? and PurposeID=? ; ": " ; "" ; Person::PersonID ; "Cell" )
Then you can use fieldSeparator to and a colon and space between the PurposeID and Phone and a Carriage return or comma space to separate the rows.
I attached a copy of Beverly's treatise on SQL. this will provide you with all the info you need on SQL for FMP.
fm13_sql_reference.pdf 954.1 K
Thanks alot. I have now something to work with. We used in your company an CRM / ERP opensource called Opentaps. After 3 years with this product we are completly in mess, very poorly done, tons of bugs many languages etc etc. So I decided to go back to FM and write it myself.
Regarding your statement "I do hope phone purpose isn't a separate table from phone?" Yes each of these are sepearte tables - might mare.
But I have to convert them so ...
Thanks for pointing out the spaces for SQL.
Your info really helped alot and I think I can make it now. Against thanks - very helpful. Wishing you a great weekend.