it can not be done with ExecuteSQL.
The table has to have at least one row for eSQL to return anything.
Since a global field can be accessed from the context of any layout and any table occurrence in your file, why would you need to do so using ExecuteSQL?
The field may be empty, the table may not have 0 records.
WHERE globalField IS NULL
of course you get nothing!
Sent from miPhone
Thanks for the quick response everyone.
philmodjunk -- you might be onto something.
I'm using ExecuteSQL because of the fact that this is part of a script that I would like to run onFirstWindowOpen.
It will be querying against many different tables looking for the value within a field that will always be named the same, and so would like to avoid having to navigate to different layouts if possible. It's a Global Calculation field, and will contain some table-specific options that I will use elsewhere throughout the system. These options will still pertain to the table even if no records have been created in it yet, and so I would like to still have access to this field.
Can you perhaps recommend an alternative?
I haven't had much luck with either just yet...but I'm sure there's a way.
Global fields can be accessed from any context.
so Set Variable ( $v; table::global_field ) will work in any script as long as table::global_field exists and IsEmpty ( table::global_field ) = False.
While this is true, I've been finding that programmatically specifying the field name throws a wrench into things.
So, I will need to do something like:
Evaluate ( $tableName & "::tableOptions" ) <-- For whatever reason I'm just getting the fieldName as a result (I'm probably doing something wrong here)
GetField ( $tableName & "::tableOptions" ) <-- Tells me field is not available (guessing this is because there are no records)
Since globals can be retrieved from anywhere: are you not getting the correct value by just asking for the field contents? Shouldn't have to Evaluate() or use GetField(). Unless you trying to do this in a abstract way based on a generic field name for a list of TOs?
What is in the calculation?
Thanks for chiming in.
The field name itself is constant, but the table is indeed dynamic (I'm gathering the contents of all of these fields from multiple tables), so I don't know how else to grab the field's contents without using Evaluate or GetField.
The field contains a set of name/value pairs in JSONObject format that define the options for that table.
If you ask for the field directly and not through any construct, do you then get the proper contents?
Currently, I'm on a layout based on a table with 0 records, and added a new watch variable to my data viewer.
In its calculation, I simply double-clicked the field's name from the list of fields on the left, and it instantly showed me the value of this Global calc
1 of 1 people found this helpful
Ran the following tests with an empty table field and a value entered into a global field in that table:
GetField ( GetfieldName ( table::globalField ) )
Evaluate ( getfieldName ( Table::globalField ) )
Let ( $TableOName = "table::" ;
GetField ( $TableOName & "globalfield" )
Let ( $TableOName = "table::" ;
Evaluate ( $TableOName & "globalfield" )
They all returned the correct value with no errors.
Note that global calculation field can be a big gnarly in how they evaluate and I did not try a global calculation field.
I got it to work using your 4th option (which I must've fat-fingered when I originally tried it).
Option #1 and 2 aren't doable for me because I cannot hardcode the field.
Option #3 still returns a "?" and says <Field Missing> when using FM16's updated Data Viewer.
I've got one that works though, and I can move forward with this.
Thanks everyone for all your help!
#1 and #2 were not intended as suggestions, just ways to be absolutely sure that the named reference is valid for testing purposes. That said, GetFieldName is often a very good way to avoid specifically naming a field's table occurrence and field name as quoted text--which locks you into a specific name that cannot then be changed in the future. The typical way to use GetFieldName is to use it to pass the reference as text in a script parameter to a script. And yes, I know this isn't what you need here, but am posting that info for educational purposes for others that might read this discussion.
One trick to get field names for such a process without quoting them explicitly would be to put all of these global fields onto a utility layout (they are global fields after all) and use the fieldnames function to get a list of field names from the layout to loop through with your code. That then allows table, field and table occurrence name changes, but locks you to a layout--so it's kind of a "fielder's choice" as to which is the more flexible option.