Hi Scott,
Normally there is no 'code' it's all done with subclasses.
Sometimes I have to disable a trigger, fill all the triggers, then repopulate the list field.
eg
// designed to work like a filemaker lookup fields
// if there is more than one field required for the lookup
// I normally disable the trigger
EditFieldFrom.safetyCatch = false
EditFieldTo.safetyCatch = False
EditFieldFrom.trigger // the trigger does not work if the safetyCatch is on
EditFieldFrom.safetyCatch = true
EditFieldto.safetyCatch = true
Steps.
1. Define the query, normally with MS Access, sometimes with pgAdminIII
2. Convert the query to a parametised query and store in recordsets.xml
(which mayl be changed the queries.xml before I release the current version )
3. add a reference to listboxes.xml.
4. add a subclassed listbox to any window, name it the same as the reference in listboxes.xml
5. add required subclassed editfields to any window, name the controlField to match the name of a parameter in recordset.xml
5. bind the two together (Thanks to Charles Yeoman for his binding examples)
Compile, Run, Use ...
The opensource version I released that is related, though normally somewhat behind what I use is
http://realopen.org/projects/kjtlframework/
which showed adodb syntax through the use of wrapper classes
Registered Balance-Infosystems.Com developers/users get to see the latest versions of the kjtFrameworks ( now called balanced ) code base as they become available.
recordset.xml snippet which I store all my queries.
Currently supports postgres, adodb and some support for straight text files, adding other connections types would reasonably trivial.
I use the adodb package for mysql, dataflex, ms access including queries and ms excel. The postgres classes are subclassed from the excellent pg4sql classes by Marc Bastin.
http://aliacta.com/products/pgsql4rb.htm
The following snippet is emailed automatically to managers each morning as a tab delimited.xls file (which magically opens with excel without the import dialog) showing work recorded for the shop, using the barcode package, for the previous day.
Note the :from and :to within the sql
<Recordset
name="selectDailyWorkReport"
address="scsiier.balance-infosystems.com:5432"
type="postgres"
database="balance"
field-delimiter="#td#"
record-delimiter="#tr#"
sql="
SELECT
divisions_table.id_division_varchar as Division ,
stages_table.id_stage_varchar as Stage ,
resources_tbl.description_vc as Resource ,
to_char( work_tbl.start_dti, 'YYYY-MM-DD') as date,
work_tbl.id_job_vc as job ,
staff_tbl.staff_vc as staff,
activities_tbl.description_vc AS activity ,
to_char(work_tbl.start_dti,'HH:MI AM') as start ,
to_char(work_tbl.stop_dti,'HH:MI AM') as stop,
work_tbl.hours_nl as Hours ,
work_tbl.minutes_nl as Minutes
FROM
work_tbl
INNER JOIN staff_tbl ON work_tbl.id_staff_nl = staff_tbl.id_staff_nl
INNER JOIN resources_tbl ON work_tbl.id_resource_nl = resources_tbl.id_resource_nl
INNER JOIN activities_tbl ON work_tbl.id_activity_nl = activities_tbl.id_activity_nl
INNER JOIN stages_table on activities_tbl.id_stage_nl = stages_table.id_stage_integer
INNER JOIN divisions_table on stages_table.id_division_integer = divisions_table.id_division_integer
WHERE
work_tbl.start_dti>=':from'
AND work_tbl.start_dti<':to'
ORDER BY
divisions_table.sort_integer,
stages_table.sort_integer,
resources_tbl.id_resource_vc,
work_tbl.start_dti ;
"
field-names-row="1"
fieldtypesrow="2"
trailingrows="1"
/>
two listboxes.xml snippets which among other defines the query that is linked to the listbox.
<listbox name="dailyWorkReportListbox"
query="selectDailyWorkReport"
record-delimiter="#tr#"
field-delimiter="#td#"
query-on-open="no"
scroll-bar-horizontal="yes"
column-alignments=",,,,,,,,,right,right"
/>
<listbox name="jobWorkListBox"
query="selectJobWork"
default="yes"
column-widths="0%,0%,12%,0%,3%,10%,0%,0%,10%,5%,5%,0%,13%,5%,8%,5%,5%,0%,5%,5%,5%,5%,0%"
column-headings="job,idstaff,staff,idstage,department,division,stage,idresource,resource,per_hour,per_instance,idactivity,activity,rate,started,hour,min,stopped,hour,min,hours,mins,idWork"
column-alignments="left,left,left,left,left,left,left,left,left,right,right,left,left,right,left,right,right,left,right,right,right,right,right"
column-alignment-offsets="0"
record-delimiter="#tr#"
field-delimiter="#td#"
left="5mm"
top="10mm"
width="100mm"
height="100mm"
query-on-open="no"
scroll-bar-horizontal="yes"
report="Job Work"
/>
note, does _not_ need professional version for adodb or postgres (if using pgsql4rb)