Passing Autosearch to additional queries

rbuser

I know, from prior experience that this should be working, but something
I'm doing is wrong, and not working.

Situation:
Delphi 6 / RB 10.04

Currently have 2 queries in this report (once I get this working, there
will be more). Both queries have no issues themselves.

Query 1 is a table called GENLEDG. This has 2 fields, Item and Balance.
The search section looks like:

(Field / Operator / Value / Autosearch )
GENLEDG.Date / Between / [blank] / Yes
begin
GENLEDG.Account / = / 431.000 / [blank]
OR
GENLEDG.Account / = / 421.000 / [blank]
OR
GENLEDG.Account / = / 521.000 / [blank]
end

Query 2 is a table called Warranty. This has one field, Labor Total.
The search section looks like:

Warranty.DatePosted / Between / 1/1/10,1/31/10 / [blank]
begin
Warranty.Paid / = / 4 / [blank]
OR
Warranty.Paid / = / 5 / [blank]
end
Warranty.VendorCode / = / BMW / [blank]

Now - the goal in this situation is to have the autosearch in query 1
passed into the dateposted search in query 2. The result I'm looking
for is the sum of certain accounts in the gl for a date range, and the
sum of BMW Warranty Labor for that same period. When I run a query
separately on the warranty table, for the month of april - i get a
result of $696 (so i know the query works) - there is no value in
January (no BMW warranties were done then).

Now - the code I am using is:

[code]
procedure ReportBeforeOpenDataPipelines;
var
lSQLBuilder: TdaSQLBuilder;
begin
lSQLBuilder := TdaSQLBuilder.Create(Warranty);

if (Report.AutoSearchFields[0].ShowAllValues) then
lSQLBuilder.SearchCriteria.Clear
else if (lSQLBuilder.SearchCriteria.Count = 0) then
lSQLBuilder.SearchCriteria.Add('GENLEDG', 'Date', 'Between',
Report.AutoSearchFields[0].SearchExpression)
else {update existing search criteria with the autosearch field value}
lSQLBuilder.SearchCriteria[0].Value :=
Report.AutoSearchFields[0].SearchExpression;

end;
[endcode]

I've verified that all the spelling matches up. I've tried this both
with Warranty having the DatePosted search and not. I can't figure out
what I'm doing wrong.

Any advice?!?

Thanks in Advance!

Chris Kiraly

nicocizik

Hi Christopher,

Take a look at the following article on applying an autosearch value to
multiple queries. I highly suggest getting this working correctly in Delphi
before moving it to RAP. This allows you to trace into the code and see
exactly what is happening much easier.

http://www.digital-metaphors.com/rbWiki/DADE/SQLBuilder/How_To...Apply_Single_AutoSearch_to_multiple_queries

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Actually I had discussed something similar with Nard Mosely a while back
(November of 07).

I'm using what I used then, which Nard helped me fix for using the
between in the autosearch dates.

The page you gave is the base of what I used in my code.

The issue I have is, i'm not sure because I have other search criteria
that is being used if that is causing an issue - but the date range is
not being applied to the second query.

nardmoseley

With RB 12 you can bind a report parameter to multiple queries without
writing any code.

1. Use the Report Designer to define a Report.Parameters[ ] item and
configure the DataType and optional AutoSearchSettings.

2. Use the Query Designer to define a Query search condition and bind it to
the report parameter.

The rbWiki example shows the older approach.


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

My problem is that we don't have RB12 - and won't be going to it due to
changes in our new system's platform.

But still need to do this for our current customers. So can it get
done? and how?

rbuser

Ok - This is funny (weird that is)

I stripped all of the search criteria out of warranty. so it will just
sum that field in the table - it comes to 40043.75. I tested it with
Just BMW warranties, and got $4080 in normal SQL, so that should be my
resulting number.

I added a blank label below the value on the report, and my
beforepipeline looks like this:

procedure ReportBeforeOpenDataPipelines;
var
lSQLBuilder: TdaSQLBuilder;
begin
lSQLBuilder := TdaSQLBuilder.Create(Warranty);

lSQLBuilder.SearchCriteria.Add( 'Warranty', 'Vendor Code', '=', 'BMW' );

Label5.Caption := lSQLBuilder.SQL.SQLText.Text;

end;


Label5 is my new label - on the report, label 5 prints the whole sql -
which shows the Warranty.Vendor Code = 'BMW' in the where clause (as
that is the only where criteria) - but the warranty query isn't running
with that. At first - it seems like it needs the ApplyUpdates procedure
run.

But, when I added lSQLBuilder.ApplyUpdates; below the label5.caption
assignment, I get the following error:

Error: ReportBeforeOpenDataPipelines, Line 11: Expected: '(' or '[', but
found 'ApplyUpdates' instead.

So it's like my criteria is being added, but after the query runs
already. Any ideas?

rbuser

Ah - problem solved -

Apparently the ApplyUpdates is useless - but the lSQLBuilder.Free is
important! Once I added that back in - she worked beautifully!

thanks again!!!