Building a Record Selection Formula based on Parameters

[RowenaB]

Hi, I need to write a formula for the record selection based on User Parameters. I have created a formula which builds a String which I want to add to an 'in' clause in the Record Selection.

The formula works perfectly if simply used as a field on the report - however as soon as I try to use it in the Record Selection Criteria it tells me that the string is too long. This is not true as the string (and I have tested it using Length) is not over 254 characters.

I also tried using the entire formula in the Record Selection and not having the actual formula on the report but that did not work either - it again told me that the string was too long.

Can someone please tell me what I am doing wrong.

Thanks


Formula to build String...........

BeforeReadingRecords ;
Global stringVar PeriodStr;

stringVar SMonth:= Left ({?FromPer},2);
stringVar SYear:= Right ({?FromPer},2);
stringVar EMonth:= Left ({?ToPer},2);
stringVar EYear:= Right ({?ToPer},2);
stringVar List ;
numberVar i;
numberVar x;
numberVar y;
stringVar yStr;
numberVar z;
stringVar zStr;


if SYear = EYear then

(
for i := CDbl (SMonth) to CDbl (EMonth) do
( y := i;
if y < 10 then
(
yStr := &quot;0&quot; & Left(CStr,Length(CStr)-3);
SMonth := yStr;
List := List & Chr(34) & SMonth & SYear & Chr(34) & &quot;,&quot; ;
y := y + 1
)
else
(
yStr := Left(CStr,Length(CStr)-3);
SMonth := yStr;
List := List & Chr(34) & SMonth & SYear & Chr(34) & &quot;,&quot; ;
y := y + 1
)
)
)
else
( for x := CDbl (SYear) to CDbl (EYear) do
(z := x;
if z < 10 then
(
zStr := &quot;0&quot; & Left(CStr(z),Length(CStr(z))-3);
SYear := zStr;
z := z + 1
)
else
(
zStr := Left(CStr,Length(CStr)-3);
SYear := zStr;
z := z + 1
);


for i := CDbl (SMonth) to 12 do
( y := i;
if y < 10 then
(
yStr := &quot;0&quot; & Left(CStr,Length(CStr)-3);
SMonth := yStr;
List := List & Chr(34) & SMonth & SYear & Chr(34) & &quot;,&quot; ;
y := y + 1
)
else
(
yStr := Left(CStr,Length(CStr)-3);
SMonth := yStr;
List := List & Chr(34) & SMonth & SYear & Chr(34) & &quot;,&quot; ;
y := y + 1
)
);

if y > 12 then
(z = z + 1;
if z < 10 then
(
zStr := &quot;0&quot; & Left(CStr(z),Length(CStr(z))-3);
SYear := zStr;
z := z + 1
)
else
(
zStr := Left(CStr,Length(CStr)-3);
SYear := zStr;
z := z + 1
)
);

for i := 1 to CDbl (EMonth) do
( y := i;
if y < 10 then
(
yStr := &quot;0&quot; & Left(CStr,Length(CStr)-3);
SMonth := yStr;
List := List & Chr(34) & SMonth & SYear & Chr(34) & &quot;,&quot; ;
y := y + 1
)
else
(
yStr := Left(CStr,Length(CStr)-3);
SMonth := yStr;
List := List & Chr(34) & SMonth & SYear & Chr(34) & &quot;,&quot; ;
y := y + 1
)
);

if SMonth = EMonth and SYear = EYear then exit for;

)
);

PeriodStr := TrimRight(Left(List,Length(List)-1));
CStr(TrimRight(Left(List,Length(List)-1)))

Results of formula as printed on report..................

&quot;0601&quot;,&quot;0701&quot;,&quot;0801&quot;,&quot;0901&quot;,&quot;1001&quot;,&quot;1101&quot;,&quot;1201&quot;,&quot;0102&quot;,&quot;0202&quot;,&quot;0302&quot;,&quot;0402&quot;,&quot;0502&quot;

In Record Selection Formula Editor I have the following .....................

Global stringVar PeriodStr;
{periodt.pe} in [PeriodStr]

 

[lyanch]

A couple problems.. the first being that just putting [PeriodStr] does not make an array of your periods.. it makes one long string that &quot;looks&quot; like an array.. but isn't.

I would just break it all out in the select formula itself..

First create a SQL expression for the first two chars and last two chars of your period.. you don't say what the DB is so I can't help you with the sytax but we will call them {%Month} and {%Year}

Then in your selection formula:
(
if Right({?FromPer},2) = Right({?ToPer},2) then
(
{%Year} = Right({?FromPer},2) AND
{%Month} in Left({?FromPer},2) to left({?ToPer,2})
)
else if Right({?FromPer},2) <> Right({?ToPer},2) then
(
(
{?Month} in Left({?FromPer},2) to '12' AND
{%Year} = Right({?FromPer},2)
)
OR
(
{?Month} in '01' to Left({?ToPer},2) AND
{%Year} = Right({?ToPer},2)
)
)
)
AND
if tonumber(Right({?ToPer},2)) - Tonumber(Right({?FromPer},2))> 1 then
(
{%Year} in totext(Tonumber(Right({?FromPer},2)) + 1, &quot;00&quot to totext(Tonumber(Right({?ToPer},2)) - 1, &quot;00&quot
)
else if not (tonumber(Right({?ToPer},2)) - Tonumber(Right({?FromPer},2))> 1)
(
true
)

And as I look at this I think i would convert everything to dates... but this should work as well.

Lisa

 

[RowenaB]

Hi,

Thanks for your response however I think we are talking at cross-purposes (I feel a bit bad about that as it looks like you have gone to a lot of effort to write the script). I am not worried about the info in my string - I know that it is correct and will work like it is. I don't think it has to be an array because if I typed out these numbers it would work and if I assign the PeriodStr with that exact info in the Record Selection formula it works. The only issue I have is with passing the global variable from the formula to the record selection formula. What I don't know is why it is a) telling me the string is over 254 characters long when it is not and b) why it will not pass the global variable into the Selection formula because it's apparently too long. It looks like it is reading the BeforeReadingRecords script before it tries to do the select but will not pass the variable over as it is too long. I need to know why it would think that the variable is too long when it is not and how I can correct this problem.

If it would pass the variable across properly my Selection formula would look like this (which does work)

{periodt.pe} in [&quot;0601&quot;,&quot;0701&quot;,&quot;0801&quot;,&quot;0901&quot;,&quot;1001&quot;,&quot;1101&quot;,&quot;1201&quot;,&quot;0102&quot;,&quot;0202&quot;,&quot;0302&quot;,&quot;0402&quot;,&quot;0502&quot;]

Thanks again.

 

[lyanch]

It is because it is a variable... you can't use variables in selection formula because they aren't evaluated soon enough.

Lisa

 

[synapsevampire]

Variables can be referenced in a record selection formula by placing them in a formula and referencing the formula in the record selection formula, however this will prevent SQL pass through to the database as the SQL generator doesn't like variables for some reason...

The database design is poorly conceived, I suggest you get a box of rusty nails, dunk them in rodent guana, rent a nail gun and go 'splain to your DBA that if she/he had placed the year first, then you could use a range against it intelligently.

So let's fix the data first:

Create a SQL Expression which has something like:

substring(periodt.pe,3,2)+substring(periodt.pe,1,2)

Now you have a column in your select in a usable format.

Change the prompt of the parameter to be YYMM and now the record selection formula is as simple as:

{%correctedpe} >= {?FromPer}
and
{%correctedpe} <= {?ToPer}

This assumes that you don't have dates prior to 2000.

-k

 

[RowenaB]

Synapsevampire,

You Guru you - what a marvellous idea - a bit embarrassed that I did not think of it myself. I will get right onto that.

I know the periods are all wrong - don't know what they where thinking but am hoping to get them changed around in the next conversion.

A bit disappointed that I have to kill all that code - it took a long time to get it to work!

Thanks everyone for the help.

 

[RowenaB]

Hi,

Just to let you know I found another way of doing it with the array (prompted by Lisa's comments) and it worked as well - thanks Lisa. I will use this as it is better because the users are sort of set in their ways (as you probably all know) and this way I will not have to change the format of the input parameter.

I changed my string layout to be comma delimited instead of &quot;,&quot; and put this in the Record Selection formula.

Global stringVar PeriodStr;
stringVar array Periods;
Periods:=Split(PeriodStr,&quot;,&quot;

{periodt.pe} in Periods