Incorrect math Alpha5v7 to V8

[Dave Mac Callum]

Re: Incorrect math Alpha5v7 to V8

Here's the filter expression that is built if you let the builder put it together for you. I've tested this in vers 8. Let us know how it does in vers 7.

Trade_Transactions->Date >= Var->g_start_date

Hi Tom,

Version 8 and Version 7 seem to perform much the same.

What i am looking for is a solution that performs as quickly as when i use filter Trade_Transactions->Date >= ctod("01/09/2007"). There is no what i call "scanning the tables" because that is what it seems o be doing when i use Trade_Transactions->Date >= Var->g_start_date. (When invoking "Debtors Enquiry - 6" button, the green "progress bar" at the bottom left hand of the monitor scans approx a dozen times before it prompts for Account name.) When using ctod("01/09/2007") these in no "scanning".

I need to have a variable for the operator to select a profile to view.

I tried to build a variable with the same structure (char "dd/mm/yyyy") . On Alpha Help i went to "Date and time functions" - Conversion functions.
I was looking for the reverse of ctod "Converts a character string containing date information to a Date value" which is "Converts Date value to a character string", so as to simulate ctod("01/09/2007") as closely as possible.
using a variable but there did not seem to be anything there.

It seems that the characteristics of the date filter (from a character string to a date) alter the performance of Debtors Enquiry completely.

Any ideas ?

[Tom Cone Jr]

Re: Incorrect math Alpha5v7 to V8

Sorry.

When you said "... and I don't know what it's doing!" I thought you meant the filter wasn't working with variable substitution. So, I only looked at building a filter expression that would be properly evaluated.

What you're seeing on the status bar is a visual indicator that Alpha is building a query list for the filtered subset of child records to display in your browse. The form opens with a filter in place to show only linked child records. You're now asking Alpha to filter the set into a smaller subset, those that meet the linking criteria of the set AND those that meet the filter criteria of your form, based on the value in your v_start_date value.

If time permits I'll rummage further. Maybe others will have ideas for you.

Later...

It seems that the characteristics of the date filter (from a character string to a date) alter the performance of Debtors Enquiry completely.

Completely? Come on Dave, that's a bit of an exaggeration. Alpha Five is doing what you ( and I ) have asked it to do. It's building a query to display only a subset of all the linked child table records. This takes a bit of time, so performance slows.

Change the filter for the Trade_Transactions table in your form layout to:

cdate(Trade_Transactions->Date) >=  cdate(Var->g_start_date)

Then study the list of index definitions you have in place already for the Trade Transactions table and review the help file topic "Lightning Query Optimization". Your homework, Dave, is to tell us why this filter expression causes the display of records in the browse object to display more quickly.

-- tom

[Dave Mac Callum]

Re: Incorrect math Alpha5v7 to V8

Sorry.

Later...

Completely? Come on Dave, that's a bit of an exaggeration. Alpha Five is doing what you ( and I ) have asked it to do. It's building a query to display only a subset of all the linked child table records. This takes a bit of time, so performance slows.

-- tom

Tom,

It seems as though the filter Date >= ctod("01/09/2007") does not build a query to display, as does the filter Date >= Var->g_start_date. It seems to go through a completely different procedure.

cdate(Trade_Transactions->Date) >= cdate(Var->g_start_date) is what I've been looking for.

Will get back to you with my "homework" findings to-morrow.

Thanks for the support.

[Dave Mac Callum]

Re: Incorrect math Alpha5v7 to V8

Sorry.

Change the filter for the Trade_Transactions table in your form layout to:

cdate(Trade_Transactions->Date) >=  cdate(Var->g_start_date)

Then study the list of index definitions you have in place already for the Trade Transactions table and review the help file topic "Lightning Query Optimization". Your homework, Dave, is to tell us why this filter expression causes the display of records in the browse object to display more quickly.

-- tom

It seems as though your filter "cdate(Trade_Transactions->Date) >= cdate(Var->g_start_date)" uses the Acc_Id index which exists as a Trade_Transaction index "(Acc_Id + cdate(date))", when filtering req data. When using the "Date >= Var->g_start_date" it has to build a query because it has no comparable index containing both Acc_id and date.

[Tom Cone Jr]

Re: Incorrect math Alpha5v7 to V8

Exactly!

The compound expression in the index matches the sort sequence Alpha would have to use to put the child table records in order first by Acc_id (needed for the set based form) and then by the character equivalent of the date (needed by the child table filter embedded in the layout) to further narrow the range of records displayed in the browse object. Lightning query optimization can use the permanent index to "jump" to the beginning of the desired range of filtered records immediately, and can fetch a range of matching records one after the other without having to build another instance of the table in memory and sort the records anew. Pretty nifty, huh?

-- tom