Help on Query, Please

[Daniel Weiss]

Any idea why the first script with out using the �records_get()� method, works fine, finding the records and showing them. But the second script (with the same query) always returns 0 for recs_found and pops up the Msg_box.

1)

tbl = table.current(2)
query.description = "last price"
query.order = "flattenquery(INVERT(invoice_header->date))"
query.filter = "invoice_items->CUST_ID='" + CUST.text + "'.and.invoice_items->item_code='" + item.text + "'"
query.options = "M"
tbl.query_create()
Company.Show()
Browse1.Show()
Browse1.Activate()
Browse1.Resynch()
end

-------------------------------------------------------
2)
tbl = table.current(2)
query.description = "last price"
query.order = "flattenquery(INVERT(invoice_header->date))"
query.filter = "invoice_items->CUST_ID='" + CUST.text + "'.and.invoice_items->item_code='" + item.text + "'"
query.options = "M"
i = tbl.query_create()
recs_found = i.records_get()
if recs_found = 0
Note = "Item code entered not found for this Customer, check spelling and try again."
Ui_msg_box("EZ Link",note,ui_ok+UI_INFORMATION_SYMBOL)
Company.Hide()
Browse1.Hide()
Item.Activate()
else
Company.Show()
Browse1.Show()
Browse1.Activate()
Browse1.Resynch()
end if
end

Where is my mistake?
Thanks
Dan

[Stan Mathews]

RE: Help on Query, Please

Probably not the problem but I don't find "M" as a valid query option string, only "N", "D", and "U". Maybe I have old documentation?

[Daniel Weiss]

RE: Help on Query, Please

Thanks Stan Mathews

This is on readme403 doc

New Options for .Query_Create Method
Alpha Five now has two new option flags for the .query_create() method. These options are set with the query.options variable. The options are:

query.options = "M"
If this option is specified, Alpha Five will build a new query list even though a) an existing query list exists, and b) an existing Index exists (both of which, Alpha Five could have used rather than running a new query).

query.options = "X"
If this option is specified, Alpha Five will use an index even if an existing query exists (which Alpha Five would otherwise have used in preference to the index)

If no options are specified, then when you run a query, Alpha Five does the following:
a) if the same query has been previously run, use that query list.
b) if not, but if an index that satisfies the query criteria exists, use the index.
c) if a previously run query does not exist, and if no index exists, run a new query.

Previously, Alpha Five only offered one option, �N�, which caused Alpha Five to run a query, rather than selecting an index, if an index would have satisfied the query criteria.

If you want to trade off speed for certainty that your applications will run correctly, you should consider using the �M� option in your Xbasic code. (In most applications the speed difference will probably not even be noticeable.) This ensures that whenever you use the query_create() method, Alpha Five is creating a new query from scratch, and not relying on a previously run query, or an index, that could be corrupt.

For example:

Tbl = table.open(�customer�)
Query.filter = �state = �ma��
Query.order = �lastname�
Query.options = �M�
Indx = tbl.query_create()

[Tom Cone Jr]

RE: Help on Query, Please

Dan, my guess is that the query is not working in the first script, it just looks as though it is.

I was surprised to see flattenquery() in the query.order statement. Shouldn't it be in the query.filter statement instead? I don't see any reason to flatten the query.order since you base the order on values in the parent table only. right?

On the other hand, the query.filter statement uses fields from the child table, so flattenquery() is indicated.

-- tom

[Daniel Weiss]

RE: Help on Query, Please

Thanks Tom
I thought about that before and tried it without the flattenquery() but it made no difference, the reason why I tried using the flattenquery() the Date field is part of the invoice header table and not the invoice items table, but it really made no difference ether way. The first script works while the second returns 0 records always

Thanks
Dan

[Peter.Wayne]

RE: Help on Query, Please

I'm surprised that either script works. When you use the table.current(2) notation, you are restricting the records to those seen in the set. You really should be getting 0 records each time.
The flattenquery() should be used in the query.filter, not
query.order, and should be applied to the parent table, not to the child table.

[Daniel Weiss]

RE: Help on Query, Please

Thanks Dr Wayne

I think I need to give some more details;

My goal is;
To find the last paid price (latest invoice Date) of a given Item, for a selected (or current) Customer.
I�d prefer to show the Item for the selected customer in a browse by Invoice Date in descending order.

In the Invoicing set, I linked the Items table to the Invoice Header table by INVOICE_NO+CUST_ID, so each item could be attached to its customer without using the invoice set.



To achieve the above I made this set � Customer > (one to many) Invoice Items > (one to one) Invoice Header�, and on the form for the set there is a browse showing the Items, (primary table = Invoice Items).

When the user enters an Item in a variable filed, it fires the script from above;

My first try was the following script, but it only the parent Customer table I could not get the browse to show any records.

tbl=table.current()
query.description = "last price"
query.order = "invoice_header->date"
query.filter = "flattenquery(CUSTOMER->CUST_ID='" + CUST.text + "'.and.invoice_items->item_code='" + item.text + "')"
query.options = "M"
tbl.query_create()
Browse1.Resynch()

Would you have any suggestions for me?

Please see attached file
Thank s
Dan

[Tom Cone Jr]

RE: Help on Query, Please

Dan, as a rule when working with a form based on a set, I avoid scripts which manipulate the current view of any table (especially child tables) independently of the form, itself. I'm not smart enough to keep track of which view of the records is currently showing on screen, and as a design issue I prefer to let Alpha Five update all the links as I navigate through the records.

So, instead of defining a query and running it against one or more of the child tables in the set upon which your form is based, I'd open another instance of the desired tables in my script, and run the query against that instance instead of the view in effect through the form. If the query returns more than one record, I'd have my script call another form or listbox to display the results. When finished, I'd have my script close that instance of the tables, returning you back to where you were when the script was called in the first place. The ability to open and manipulate multiple instances of your tables is a major difference between Alpha Four and Alpha Five, and one that I would use in your situation.

Good luck.

-- tom