Using a Query to Fill a Detail Block

[Dan Jinks]

Chapter 22. Using Data-Aware Forms in SIMPOL Using Special Features Using a Query to Fill a Detail Block In the programmer’s guide I ran across the above topic. This seems to be what I want for my Notes detailblock, a subset of the full table, based on some WHERE clause. That is at least the approach I am currently using. This may be a better fit for what I am trying to achieve. However, will performance suffer using SQL92? There are only about a thousand records in this table, Another approach is just create a blank temporary table, fill it with the subset of data from the main notes table (using some code I write which cycles through the main Notes table using the proper index) and make the subset table the control source for the detail block. I might [erfer that one. Here is the detail block form I’m referring to: http://www.jinksinc.com/Downloads/NotesDetailBlock.png -Dan

[Michael]

On 14/07/2011 21:15, djinks wrote:
> Chapter 22. Using Data-Aware Forms in SIMPOL Using Special Features
> Using a Query to Fill a Detail Block
>
> In the programmer's guide I ran across the above topic. This seems
> to be what I want for my Notes detailblock, a subset of the full
> table, based on some WHERE clause. That is at least the approach I am
> currently using. This may be a better fit for what I am trying to
> achieve. However, will performance suffer using SQL92? There are only
> about a thousand records in this table,
>
> Another approach is just create a blank temporary table, fill it
> with the subset of data from the main notes table (using some code I
> write which cycles through the main Notes table using the proper
> index) and make the subset table the control source for the detail
> block. I might [erfer that one.
>
> Here is the detail block form I'm referring to:
> http://www.jinksinc.com/Downloads/NotesDetailBlock.png -Dan

Actually, it may be quite efficient to use the SQL92. I haven't found it
to be slow at all.

Ciao, Neil

[Dan Jinks]

Is there an example of a proper query?

I'm not getting any errors, but not data appears in the detailblock.
Here is my code:
if .instr(formfilename,"unlinked") > 0
db =@ appw.form.detailblocks.getfirst()
if db !@= .nul
wh = '[CONXNOTE.]NoteCategory = "SOAP"'
// wh = ""
db.setparams(usequery = .true, whereclause = wh)
db.runquery()
end if
end if

I also used an empty string for the where clause, but no change in the
results.

-Dan

>> with the subset of data from the main notes table (using some code I
>> write which cycles through the main Notes table using the proper
>> index) and make the subset table the control source for the detail
>> block. I might [erfer that one.
>>
>> Here is the detail block form I'm referring to:
>> http://www.jinksinc.com/Downloads/NotesDetailBlock.png -Dan
>
> Actually, it may be quite efficient to use the SQL92. I haven't found it
> to be slow at all.
>
> Ciao, Neil
>

[Michael]

On 15/07/2011 20:02, djinks wrote:
> Is there an example of a proper query?
>
> I'm not getting any errors, but not data appears in the detailblock.
> Here is my code:
> if .instr(formfilename,"unlinked") > 0
> db =@ appw.form.detailblocks.getfirst()
> if db !@= .nul
> wh = '[CONXNOTE.]NoteCategory = "SOAP"'
> // wh = ""
> db.setparams(usequery = .true, whereclause = wh)
> db.runquery()
> end if
> end if

I see the problem. The syntax diagram is probably misleading. The square
brackets denote an optional parameter. In other words, if the field
names are unique, you don't need the table name, so it is optional. Your
where clause could look like this:

wh = "CONXNOTE.NoteCategory = 'SOAP'"

or like this:

wh = "NoteCategory = 'SOAP'"

Please note the use of single quotes inside the string. SQL expects
strings to be delimited by single quote characters. I also assume that
the detail block mastertable is the same table as is referenced in the
where clause.

Ciao, Neil

> I also used an empty string for the where clause, but no change in
> the results.
>
> -Dan
>
>>> with the subset of data from the main notes table (using some
>>> code I write which cycles through the main Notes table using the
>>> proper index) and make the subset table the control source for
>>> the detail block. I might [erfer that one.
>>>
>>> Here is the detail block form I'm referring to:
>>> http://www.jinksinc.com/Downloads/NotesDetailBlock.png -Dan
>>
>> Actually, it may be quite efficient to use the SQL92. I haven't
>> found it to be slow at all.
>>
>> Ciao, Neil
>>

[Dan Jinks]

Great. Thanks for the help.
-Dan

On 7/18/2011 5:10 PM, Neil Robinson wrote:
> On 15/07/2011 20:02, djinks wrote:
>> Is there an example of a proper query?
>>
>> I'm not getting any errors, but not data appears in the detailblock.
>> Here is my code:
>> if .instr(formfilename,"unlinked")> 0
>> db =@ appw.form.detailblocks.getfirst()
>> if db !@= .nul
>> wh = '[CONXNOTE.]NoteCategory = "SOAP"'
>> // wh = ""
>> db.setparams(usequery = .true, whereclause = wh)
>> db.runquery()
>> end if
>> end if
>
> I see the problem. The syntax diagram is probably misleading. The square
> brackets denote an optional parameter. In other words, if the field
> names are unique, you don't need the table name, so it is optional. Your
> where clause could look like this:
>
> wh = "CONXNOTE.NoteCategory = 'SOAP'"
>
> or like this:
>
> wh = "NoteCategory = 'SOAP'"
>
> Please note the use of single quotes inside the string. SQL expects
> strings to be delimited by single quote characters. I also assume that
> the detail block mastertable is the same table as is referenced in the
> where clause.
>
> Ciao, Neil
>
>> I also used an empty string for the where clause, but no change in
>> the results.
>>
>> -Dan
>>
>>>> with the subset of data from the main notes table (using some
>>>> code I write which cycles through the main Notes table using the
>>>> proper index) and make the subset table the control source for
>>>> the detail block. I might [erfer that one.
>>>>
>>>> Here is the detail block form I'm referring to:
>>>> http://www.jinksinc.com/Downloads/NotesDetailBlock.png -Dan
>>>
>>> Actually, it may be quite efficient to use the SQL92. I haven't
>>> found it to be slow at all.
>>>
>>> Ciao, Neil
>>>
>

[Author]

Posts