Parameters and SearchOperator
Hi
RB11.04 Ent.
I am wanting to use two parameters over 4 dataviews but i need the
AutoSearchSettings.SearchOperator to vary depending on the dataview.
RB11.04 forces the same search operator on each parameter. This is causing
me a problem.
My difficulty is best illustrated with an example. I have two tables: Stock
and Movements. Stock has just 2 fields: StockNo and Description. Movement
has 5 fields: MovementNo, Date, StockNo, InOut, Qty. I want to show the
stock position at 2 dates (my parameters) and the movements In and Out
between the dates. I have 5 dataviews (2 to 5 are linked to 1) as follows:
1. StockList
SELECT Stock.StockNo, Stock.Description FROM Stock Stock
2. OpenStockPosition
SELECT Movement.StockNo, SUM(Movement.StockQty) AS OpenPosition
FROM Movement Movement
WHERE ( Movement."Date" < '2008-01-01' ) //Parameter StartDate
GROUP BY Movement.StockNo
3. StockIn
SELECT Movement.StockNo, SUM(Movement.StockQty) AS StockIn
FROM Movement Movement
WHERE ( Movement."Date" >= '2008-01-01' ) //Parameter StartDate
AND ( Movement."Date" <= '2008-12-31' ) //Parameter EndDate
AND ( Movement.InOut = 'IN' )
GROUP BY Movement.StockNo
4. StockOut
SELECT Movement.StockNo, SUM(Movement.StockQty) AS StockOut
FROM Movement Movement
WHERE ( Movement."Date" >= '2008-01-01' ) //Parameter StartDate
AND ( Movement."Date" <= '2008-12-31' ) //Parameter EndDate
AND ( Movement.InOut = 'OUT' )
GROUP BY Movement.StockNo
5. StockClose
SELECT Movement.StockNo, SUM(Movement.StockQty) AS StockClose
FROM Movement Movement
WHERE ( Movement."Date" <= '2008-12-31' ) //Parameter EndDate
GROUP BY Movement.StockNo
The problem is with Dataviews 2 and 3/4 where I want the search operator to
be < in 2 and >= in 3/4.
I have got around the problem by using 3 parameters where two of them are
the same StartDate but with differring Search Operators. This works but is
confusing to the end user and will to lead to errors if the user forgets to
make both dates the same. If I could control the search operator for each
dataview then the problem would be resolved. I am however conscious that if
this was possible then it would also be necessary to have more control of
the wording displayed in the autosearch dialog.
Any suggestions?
Regards
Tim Murfitt
RB11.04 Ent.
I am wanting to use two parameters over 4 dataviews but i need the
AutoSearchSettings.SearchOperator to vary depending on the dataview.
RB11.04 forces the same search operator on each parameter. This is causing
me a problem.
My difficulty is best illustrated with an example. I have two tables: Stock
and Movements. Stock has just 2 fields: StockNo and Description. Movement
has 5 fields: MovementNo, Date, StockNo, InOut, Qty. I want to show the
stock position at 2 dates (my parameters) and the movements In and Out
between the dates. I have 5 dataviews (2 to 5 are linked to 1) as follows:
1. StockList
SELECT Stock.StockNo, Stock.Description FROM Stock Stock
2. OpenStockPosition
SELECT Movement.StockNo, SUM(Movement.StockQty) AS OpenPosition
FROM Movement Movement
WHERE ( Movement."Date" < '2008-01-01' ) //Parameter StartDate
GROUP BY Movement.StockNo
3. StockIn
SELECT Movement.StockNo, SUM(Movement.StockQty) AS StockIn
FROM Movement Movement
WHERE ( Movement."Date" >= '2008-01-01' ) //Parameter StartDate
AND ( Movement."Date" <= '2008-12-31' ) //Parameter EndDate
AND ( Movement.InOut = 'IN' )
GROUP BY Movement.StockNo
4. StockOut
SELECT Movement.StockNo, SUM(Movement.StockQty) AS StockOut
FROM Movement Movement
WHERE ( Movement."Date" >= '2008-01-01' ) //Parameter StartDate
AND ( Movement."Date" <= '2008-12-31' ) //Parameter EndDate
AND ( Movement.InOut = 'OUT' )
GROUP BY Movement.StockNo
5. StockClose
SELECT Movement.StockNo, SUM(Movement.StockQty) AS StockClose
FROM Movement Movement
WHERE ( Movement."Date" <= '2008-12-31' ) //Parameter EndDate
GROUP BY Movement.StockNo
The problem is with Dataviews 2 and 3/4 where I want the search operator to
be < in 2 and >= in 3/4.
I have got around the problem by using 3 parameters where two of them are
the same StartDate but with differring Search Operators. This works but is
confusing to the end user and will to lead to errors if the user forgets to
make both dates the same. If I could control the search operator for each
dataview then the problem would be resolved. I am however conscious that if
this was possible then it would also be necessary to have more control of
the wording displayed in the autosearch dialog.
Any suggestions?
Regards
Tim Murfitt
This discussion has been closed.
Comments
One option would be to create the two AutoSearch parameters without any
criteria in your datasets. Once these values are entered you can manually
create the search criteria associated with them using the TdaSQLBuilder
(TdaSQLCriteriaList.AddParamSearch). This would then simply ask for the
StartDate and EndDate in the AutoSearch dialog making it a bit easier on the
user.
The other option would be to create the third parameter in code and base
it's value on the StartDate value. Then you can add the new search criteria
to the second dataset using the TdaSQLBuilder routine
(TdaSQLCriteriaList.AddAutoSearchWithParam).
As far as rewording the output of the AutoSearch dialog, you will need to
create a custom dialog to meet your needs.
--
Regards,
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com
Nard added this ability in RB 11.01; however it does appear to be no longer
working. This explains some issue we've been having with reports since we
updated to RB 11.04 not working as expected.
Scott
The scenerio below is still working. You can use the same Parameter and have
the AutoSearchSettings.SearchOperator vary for each usage of the parameter
as was fixed in RB 11.01).
Sorry for the confusion.
Scott