Problem using variables in Field Lookups [Including Workaround]

Scriptcase 9 General
#1

Just spent several hours pulling my hair out (I don’t have much to start with) as to why when using a global variable in a ‘select field’ lookup, I was getting syntax errors in the generated PHP. Specifically a fatal error saying it had an unexpected ‘)’ on a line.

On examining the line in the generated code, it was apparent that the Scriptcase IDE generated incorrect PHP code if the entire lookup SELECT was a variable. (This MUST be a BUG!)

So, in summary …
Putting a full select in a lookup as native works fine (e.g. SELECT myID FROM MyProjects Where Project = ‘AAA’)
Substituting a variable for part of the select works( SELECT myID FROM MyProjects Where [P_Users]… in this case [P_Users] is a string “Project = ‘AAA’”
Substituting the entire select into a string [Lookup], makes the IDE generate incorrect code… in this case [Lookup] is a string “SELECT myID FROM MyProjects Where Project = ‘AAA’”
So my only workaround is option 2 above.

This might sound trivial, BUT… I have LOTS of similar lookups across a 200 app system. These lookups appear in multiple places on each individual app (main field, searches, groups, etc)… so to change a lookup is a massively inefficient (and very irritating) task, which occurs more often than you might think as a system evolves.

Normally you would do this in a ‘Proper’ data dictionary, but the Scriptcase data dictionary does not allow you to properly define a data element (In most DDs, you can create the lookup rules with the data element, because they are nearly always the same across ALL apps)

I’d planned on creating a pseudo dictionary with standardised lookup variables in a library so that I could have the lookup stored once as a string and then just use the variable names in all lookups. That way once the variables are setup in the relevant apps, you can change all occurrences of the lookup by simply changing the string in the library.

To make this ‘readable’ I wanted the WHOLE Select statement in a single place… as it happens(Due to the bug above), I cant do that… the partial string approach would work, but would make maintainability a nightmare.

I can only assume, the IDE code-generator looks for the SELECT and FROM bits of the lookup to parse something correctly. Since they don’t appear in the variable string untill it is used, it is causing the IDE to ‘tilt’

I hope this helps someone avoid the frustrations of my last few days.

Ian
PS… There is a similar problem with using variables for the connection string in sc_exec_sql($sql, ‘connection’)… connection cant be a string variable.

#2

SPLIT the SQL statement in 3 parts

OnScriptInit

[glo_lookup_select]="concat(PersName,'.',PersVoornaam)"; 
[glo_lookup_table]="tblPersoneelslijst"; 
[glo_lookup_where]=" IdPersoneelslijst = '276'";//with a FIXED value for all GRIDrows

or onRECORD if you use a field of the grid in the lookup

[glo_lookup_where]=" IdPersoneelslijst = '".{Inbrenger}."'";

The lookup looks like

SELECT [glo_lookup_select] 
FROM [glo_lookup_table]
WHERE [glo_lookup_where]

You can make an internal library to use in every application using several [glo_lookup_select]… if needed

Helped?

#3

Hi.

Thats almost exactly what I did :)… Its not a brilliant solution, but goes part way towards what I wanted to do. Maintainability could be a problem in the future is my main concern.

Still think Scriptcase should have this kind of capability in the data dictionary, but I’m not holding my breath!

Thanks

Ian

#4

You can use the langfiles aswell!