SeedCode Support Forum

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

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

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.