SQLExplorer: Use globals as Search Values

General Information about upcoming products, upgrades, etc.
Posts: 2
Joined: Fri Sep 25, 2015 3:39 am
PostPosted: Fri Sep 25, 2015 8:16 am
Sum_Paychecks.png
Sum_Paychecks.png (54.05 KiB) Viewed 13447 times
Is it possible to use global fields defined in a base table as the value in a search criteria?

I'm trying to use a global ( g_P1Start ) as the value in a search that is expecting a date.

g_P1Start is a global with a date result.

I've tried entering "\g_P1Start\" but this also generate an error message, "This does not appear to be a valid date value."
SeedCode Staff
SeedCode Staff
Posts: 2764
Joined: Thu Nov 20, 2003 11:01 am
PostPosted: Fri Sep 25, 2015 12:20 pm
Right. You can't use fields like that IN the SQL Explorer interface. Instead, enter some example criteria in the interface and get your query working. Once it is, you'll copt the query as a FileMaker calculation and paste it into your file: when you do, you can replace the hand-entered criteria with the global field which will hold your criteria.

For example I might write a query and test it with "M" as the criteria. When I copy the query from SQL explorer it looks like this:

Code: Select all
// ------------  BEGIN EXECUTESQL BLOCK ------------ 

Let ( [

ReturnSub = "\n" ; // We need to swap out carriage returns in your results with a different character, so specify the character here.  \n is the default.
SQLResult = ExecuteSQL (

// ------------  BEGIN QUERY ------------

"SELECT a.\"CompanyName\", a.\"CompanyPhone\", a.\"CompanyUrl\", a.\"Modification_Date\", a.\"CompanyNotes\"
FROM \"Companies\" a
WHERE a.\"CompanyName\" > ?
ORDER BY a.\"CompanyName\" ASC" ;

// ------------  END QUERY ------ ------

// ------------  BEGIN FIELD AND ROW SEPARATORS ------------

"    " ; "|*|" ;

// ------------  END FIELD AND ROW SEPARATORS ------------

// ------------  BEGIN ARGUMENTS ------------ 
// ------------  These arguments are pulled from the values you entered when running your query.  You can substitute your own values, fields or variables here. One argument per ? in your query, in query order, separated with semicolons. ------------

"M"

// ------------  END ARGUMENTS ------------ 

 ) ] ;

// ------------  BEGIN CARRIAGE RETURN SUBSTITUTIONS ------------ 

 Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )

// ------------  END CARRIAGE RETURN SUBSTITUTIONS ------------ 

)

// Compliments of SeedCode… Cheers!

// ------------  END EXECUTESQL BLOCK ------------ 


But then when I get to my file I'll paste this in and replace "M" with my global field, like this:

Code: Select all
// ------------  BEGIN EXECUTESQL BLOCK ------------ 

Let ( [

ReturnSub = "\n" ; // We need to swap out carriage returns in your results with a different character, so specify the character here.  \n is the default.
SQLResult = ExecuteSQL (

// ------------  BEGIN QUERY ------------

"SELECT a.\"CompanyName\", a.\"CompanyPhone\", a.\"CompanyUrl\", a.\"Modification_Date\", a.\"CompanyNotes\"
FROM \"Companies\" a
WHERE a.\"CompanyName\" > ?
ORDER BY a.\"CompanyName\" ASC" ;

// ------------  END QUERY ------ ------

// ------------  BEGIN FIELD AND ROW SEPARATORS ------------

"    " ; "|*|" ;

// ------------  END FIELD AND ROW SEPARATORS ------------

// ------------  BEGIN ARGUMENTS ------------ 
// ------------  These arguments are pulled from the values you entered when running your query.  You can substitute your own values, fields or variables here. One argument per ? in your query, in query order, separated with semicolons. ------------

SomeTable::SomeGlobalField

// ------------  END ARGUMENTS ------------ 

 ) ] ;

// ------------  BEGIN CARRIAGE RETURN SUBSTITUTIONS ------------ 

 Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )

// ------------  END CARRIAGE RETURN SUBSTITUTIONS ------------ 

)

// Compliments of SeedCode… Cheers!

// ------------  END EXECUTESQL BLOCK ------------ 


Hope that helps,

John
John Sindelar
SeedCode
Posts: 2
Joined: Fri Sep 25, 2015 3:39 am
PostPosted: Fri Sep 25, 2015 1:14 pm
Thank you.

Didn't realize that I could insert field names referencing them in FileMaker format (TO::fieldname). Actually, thought you made a type and tried all sorts of ways to enter a. fieldname. Nothing worked until I followed your instructions literally. Lo and behold, it worked! :)

Thanks very much.

Return to FileMaker Products (General)

Who is online

Users browsing this forum: No registered users and 2 guests

cron
(855) SEEDCODE
[email protected]
Follow us: