Need help with query_create()

[jbrooks99]

I am struggling to delete child records from a set. I wrote some inline xbasic executed from a button to perform the delete. Whenever I execute the code I get an error message reading �no such field�. Can anyone please assist. I am fairly new at Alpha and if there is a better way of doing this please let me know

dim tbl_a as p
dim tbl_b as p

tbl_a = table.get("sort order")
tbl_b = table.get("customer")

query_filter = "tbl_b.customer_id <> ''"
tbl_b.query_create()

query.filter = "tbl_a.customer_id ='" + tbl_b.customer_id + "'"
query.sort = ""

tbl_a.query_create()

tbl_a.delete_range()
tbl_a.close()
tbl_b.close()

[Stan Mathews]

Re: Need help with query_create()

You already have table pointers so the query.create() doesn't need references to them.

dim tbl_a as p
dim tbl_b as p

tbl_a = table.get("sort order")
tbl_b = table.get("customer")

query_filter = "customer_id <> ''"
tbl_b.query_create()

query.filter = "customer_id ='" + tbl_b.customer_id + "'"
query.[COLOR="Red"][B]order[/B][/COLOR] = ""

tbl_a.query_create()

tbl_a.delete_range()
tbl_a.close()
tbl_b.close()

Not sure what you are trying to accomplish.

The lines

query.filter = "customer_id ='" + tbl_b.customer_id + "'"
tbl_a.query_create()

say "filter the 'sort order' table for records matching the current value in the customer_id field in the customer table".

It would take some sort of loop to read the customer_id field in the customer table, in each record, filter the sort order table on that value, delete the records from the sort order table.

[Mike Wilson]

Re: Need help with query_create()

From what you describe, I would think this would work:

dim tbl_c as p   'child
dim tbl_p as p   'parent
dim vfilter as C

tbl_p = table.get("customer")       'get() requires the table to be already open

tbl_c = table.get("sort order")
vfilter = "customer_id ='" + tbl_p.customer_id + "'"    'you don't need pointer to the child table in the filter.
tbl_c.delete_range(vfilter)

topprent:browse2.refresh()            ' guessing on the child browse name

There is no need to close a table... you didn't open any, you got what was open.

[MikeC]

Re: Need help with query_create()

And one more possible problem if the initial script is going to be used at all is I believe

query_filter = "tbl_b.customer_id <> ''"
tbl_b.query_create()

should have a period not an underscore to work (query.filter)

query[SIZE=6][COLOR=Red].[/COLOR][/SIZE]filter = "tbl_b.customer_id <> ''"
tbl_b.query_create()

[jbrooks99]

Re: Need help with query_create()

Thank you Stan & Mike. New at xbasic and still getting the hang of it. I used Mike's solution and it works perfectly. Thank you for the explanation. I have a much better understanding.

[enstorms]

Re: Need help with query_create()

Mike, Mike and Stan,
I was under the impression you couldn't use tbl pointers within the filter and order expresions. From the help:

Note : The Query.Filter and Query.Order expressions cannot include references to the table pointer.

Am I missing something???(as usual.)
Ernie

[Stan Mathews]

Re: Need help with query_create()

If you mean the line

query.filter = "customer_id ='" + tbl_b.customer_id + "'"

tbl_b.customer_id is not technically in the filter expression.

In English this line reads

Take the current value in the customer_id field in the table referenced by the pointer tbl_b.
Quote that value.
Concatenate that quoted value with the text "customer_id =" to yield a result like

"customer_id = 'abc45' "

Assign that result to the query.filter variable.

I beleive the passage you quoted means you can't have

"tbl_a.customer_id = 'abc45' "

[enstorms]

Re: Need help with query_create()

Stan,
Thanks for the explanation. I just assumed that anything that used the "tbl_b" was the table pointer since it had been dimmed as p. So I am now assuming that that quote from the Help file means you can't use a reference to the table pointer on the "Left" side of the "=" sign. Is this correct?
Thanks.
Ernie

PS: I seem to be using querys a bunch now in xbasic coding. I'm currently having a problem syntaxing the query.order expression based on two numeric fields, "ap_year" and ap_sortno.
Have an index set up that uses: str(ap_year)+str(ap_sortno)
Can't figure out how to get this syntaxed into the query.order expression. Help would be greatly appreciated.

[Stan Mathews]

Re: Need help with query_create()

quote from the Help file means you can't use a reference to the table pointer on the "Left" side of the "=" sign

Don't think so. You can't use table pointers in the query.filter expression. I didn't. You can build the query.filter expression out of anything you want. I did.

Remember, the query.filter expression is the end result, not the steps used along the way. If I had tried/wound up with

"customer_id = 'tbl_b.customer_id' "

it would have failed.



query.order = "str(ap_year)+str(ap_sortno)"

However you would write the filter in a report definition or a form filter expression, take that and put quotes around it. Take the result and see if it makes sense....

Does it have internal quotes, use \ to escape them.
Does it refer to variables, break them out of the expression and make Alpha use their values.

Suppose myvar = "a"
the expression "somefield = var->myvar" will never recognize that you don't mean the literal text var->myvar instead of the value in the variable myvar.

the expression "somefield = "+quote(var->myvar) tells Alpha to concatenate the text "somefield = " with the text contained in the variable myvar

[Mike Wilson]

Re: Need help with query_create()

why is it not "ap_year + ap_sortn" ?

[Stan Mathews]

Re: Need help with query_create()

Wouldn't two numbers simply be added together?

2007 + 10 would sort the same as 2001 + 16

[Mike Wilson]

Re: Need help with query_create()

yup, and that is exactly what it did. I don't use numeric fields very much at all (as it shows). And I'm guessing that Ernie's two fields don't actually need to be numeric, either.

The "str(ap_year)+str(ap_sortno)" is working to order the table in my hands.

[enstorms]

Re: Need help with query_create()

I will swear on a stack of Bibles that that was what I had tried:
"str(ap_year)+str(ap_sortno)"
But kept getting some error about "ap_sortno" not found. So ventured off into space somewhere. Now I just copied and pasted that code from your post and it works. Had to have been a miss type on my part, I guess.
Really appreciate the help.
Ernie