Form Logic: Execute SQL Select Statement

[Tim Harvey]

Is there a way to Execute an SQL Select statement in the Form Logic control and assign that to a Control ID?

I want to Select multiple fields from a single record in a different session and assign these to text field Control Id in this form. I understand that if the select statement returned multiple records this would be an issue. What I want to do is to have a message box that can display a summary of certain information from a different session.

Something like:
Select 'Unit ID:',Session1.Field2,'Equipment:',Session1.Field3 from Session1 where Session1.Field1='[*0*]'

I could create a bunch of extra fields in the current session and do a lookup for each of the fields but that would double the data stored in this session.

Is it possible to use a Select statement in an ExecuteSQL in a Form Logic event? Is there any documentation on this?

 

[Tim Harvey]

After some more test I found that you can do this but there are limitations.
Set Action to: ExecuteSQL
Set Action Control to the ID of the text field you want to write to: For example: 200
Action Value: The Select Statement
example: Select Session4.Field3+Session4.Field4+Session4.Field5+Session4.Field6+Session4.Field7+Session4.Field8 FROM Session4 Where Session4.Field1='[*0*]'

It will only return one field but you can concatenate fields and strings together with the + symbol. I couldn't figure out a way to add a line feed or carriage return. CHR(10)+CHR(13) didn't work nor did adding '\r'

 

[Dan Peluso]

Hi Tim,

The idea of outputting the result of custom SQL does have the limitation you described. Your workaround is very creative. I would also add that if you then want to distribute this concatenated string to other fields you could use the parsing calc functions to do this (LEFT, MID, RIGHT). In this way, you actually can get multiple fields of data populated with one custom SQL call. Sort of a twist and turn to get there but seems like it would be effective.