Filtering Child Records a Conundrum

[Bob Whitaker]

We have a multi layer one to many set and one form shows the parent record and all the relevant child records in various embedded browses. We then wanted to filter some of the child records so only records containing certain values were shown. Using the query genie on the form produced the desired result. We then tried to save the query so it could be attached to a button.When the button is pressed we get an error message "Invalid Filter or Order Expression".
Further investigation revealed the following comment in some old documentation which read "In a form or a browse, Alpha Five does not allow you to include fields from a one to many child table in a query"
So my question is simply why if it works perfectly from the query genie cannot it be made to work as saved query and does not the fact that it does work from the genie contradict the above statement.

Bob

[Davidk]

Re: Filtering Child Records a Conundrum

Hey Bob,

The question, as posted, is kinda tough. Could you post the query statement giving you the error or, better yet, post a sample database so we can figure out what you need.

[Bob Whitaker]

Re: Filtering Child Records a Conundrum

I have attached a zip which depicts the problems we are having.
If you open the form TESTFORM01 what we are trying to achieve is to allow the user to enter the initials of a person as appears in the WHO column of the tasks browse and have only the records with those initials appear.
If you click on the query genie in the toolbar and set the level of query to TASKS (rather than the default CrossLevel) you can then quite satisfactorily query the initials field and get the desired result.
If you then save the query and try to run it from the button on the form you get the error message.
The crux of my question is why does it work from the query genie but not from the query it generates ?
Thanks

PS it seems there are a number of similar threads running at the moment all of which relate to querying child records and maybe this is something the Alpha Developers would like to clear up.


Bob

[Davidk]

Re: Filtering Child Records a Conundrum

I do think there are some funny things going on with form filters... maybe it's just that I don't understand their "flow" but some things just don't work the way I would expect them to.

For example... in the attached, updated version, of your database I placed a new button labelled reset task filter which sets a variable to "" and then I tried to issue the Show All query command. This doesn't work. However, using the send keys command to send Ctrl-W does work. To me, this doesn't make sense.

Anyway, there's a variable shown on the form now in which you can type the initials you want to filter. Then click the filter button. To clear the filter and reset the browse click the reset button.

You can run the filter directly after entering initials in the variable text box if you wish - you don't need the button.

The attached message is what I got after I tried to edit your saved query. Even after re-saving the query it still didn't work.

Hope it helps.

[Bob Whitaker]

Re: Filtering Child Records a Conundrum

Hey David - many thanks for that - it was an approach that had eluded me. At the outset you would think doing this sort of filtering would be very straightforward, alas not.
Once again many thanks.

Bob

[G Gabriel]

Re: Filtering Child Records a Conundrum

The crux of my question is why does it work from the query genie but not from the query it generates ?

Because it's a bug.
Tweakable, but a bug nonetheless.

Besides using a variable to filter, don't forget about reverse set.

[martinwcole]

Re: Filtering Child Records a Conundrum

Bob, here is a script example - the first child is "tickets" in "browse1"

on error goto errors
parentform:tables:tickets.filter_expression="Ticket_number='17469'"
parentform:tables:tickets.query()
browse1.fetch_first()
end
errors:
err_msg = error_text_get(error_code_get())
line = error_line_number_get()
script = error_script_get()
ui_msg_box("Error", err_msg+" Error occurred at line "+alltrim(str(line,4,0))+ " in script: "+script)
end

[martinwcole]

Re: Filtering Child Records a Conundrum

heres another, using a variable

dim shared temp as c
temp="17469"
on error goto errors
parentform:tables:tickets.filter_expression="Ticket_number=" + quote(var->temp)
parentform:tables:tickets.query()
browse1.fetch_first()
end
errors:
err_msg = error_text_get(error_code_get())
line = error_line_number_get()
script = error_script_get()
ui_msg_box("Error", err_msg+" Error occurred at line "+alltrim(str(line,4,0))+ " in script: "+script)
end