where to put local variable declartions...

[sogc]

I am creating a formula to strip out a number from a URL. However, this URL takes on two formats and so I need to use an if statement to take two different actions.

This was my original formula before the if statement which works for the first format.

local stringVar url:= {VIEW_CUST_ReceiverResponseLinks_EMPC.LinkURL};
local numberVar testart := InStr (url,"te=",1);
local numberVar teend := InStr (url,"w=",1);
local stringvar templateid := mid(url,testart+3,teend - testart-4);

tonumber(templateid)

When I tried to incorporate the second format, I got into trouble. I have put local variable declarations inside the if statement because I did not want the report to calculate variables that would not get used.

Here's where my formula is now which is not working.

local numbervar formid :={VIEW_CUST_ReceiverResponseLinks_EMPC.FORMID}

if isnull(formid)

then
local stringVar url:= {VIEW_CUST_ReceiverResponseLinks_EMPC.LinkURL};
local numberVar testart := InStr (url,"te=",1);
local numberVar teend := InStr (url,"w=",1);
local stringvar templateid := mid(url,testart+3,teend - testart-4);

tonumber(templateid)

else formid;

I've tried the following format as well, by putting all variables up front, but I continue to get "The remaining text does not appear to be part fo the formula." Which includes everything after the first line.

local numbervar formid :={VIEW_CUST_ReceiverResponseLinks_EMPC.FORMID}
local stringVar url:= {VIEW_CUST_ReceiverResponseLinks_EMPC.LinkURL};
local numberVar testart := InStr (url,"te=",1);
local numberVar teend := InStr (url,"w=",1);
local stringvar templateid := mid(url,testart+3,teend - testart-4);

if isnull(formid)
then tonumber(templateid)
else formid;

Any suggestions?

 

[sogc]

When I write the formula like this (that is, remove the variable declaration for my formid field), I get no errors in the formula. Can someone explain to me why that is? I figured this out by trial and error, I'd like to understand the logic behind it.

local stringVar url:= {VIEW_CUST_ReceiverResponseLinks_EMPC.LinkURL};
local numberVar testart := InStr (url,"te=",1);
local numberVar teend := InStr (url,"w=",1);


local stringvar templateid := mid(url,testart+3,teend - testart-4);

if isnull({VIEW_CUST_ReceiverResponseLinks_EMPC.FORMID})
then tonumber(templateid)
else {VIEW_CUST_ReceiverResponseLinks_EMPC.FORMID};

 

[sogc]

When I ran the report, it tripped on the URL with the second format. So now I feel I need to write the formula like this to avoid this but I get the error, "The remaining text does not appear to be part fo the formula."

This occurs for the else statement. Any suggestions?

if isnull({VIEW_CUST_ReceiverResponseLinks_EMPC.FORMID})

then
local stringVar url:= {VIEW_CUST_ReceiverResponseLinks_EMPC.LinkURL};
local numberVar testart := InStr (url,"te=",1);
local numberVar teend := InStr (url,"w=",1);
local stringvar templateid := mid(url,testart+3,teend - testart-4);

tonumber(templateid)

else {VIEW_CUST_ReceiverResponseLinks_EMPC.FORMID};

 

[rosemaryl]

Dear Sogc,

One reason your first formula posted is not working, minimially, is because you forgot the statement separator at the end of the first line.

local numbervar formid :={VIEW_CUST_ReceiverResponseLinks_EMPC.FORMID}[red];[/red]

if isnull(formid)

then

local stringVar url:= {VIEW_CUST_ReceiverResponseLinks_EMPC.LinkURL};
local numberVar testart := InStr (url,"te=",1);
local numberVar teend := InStr (url,"w=",1);
local stringvar templateid := mid(url,testart+3,teend - testart-4);

tonumber(templateid)

else formid;

However, while giving lots of information on what you have tried, you have not told us:

database
crversion
content of fields in question
expected output

I am using CR 8.5 in this example and you cannot use a variable with isnull(), I get a field name is required here.

However, by doing the following to your formula it works fine with my data:

if isnull({Table.FieldName})

then
(
local stringVar url:= 'te=0712342w=1234567890';
local numberVar testart := InStr (1,url,"te=");
local numberVar teend := InStr (1,url,"w=");
local stringvar templateid := mid(url,testart+3,teend - testart-4);

tonumber(templateid))

else {Table.FieldName};

Note, instr(,,) takes the following arguments in cr 8.5: Start (which if one doesn't need to be declared so could be left out in your formula), data, string to search).

So, I made that change. And, finally note that the local variables and everything related to the then is enclosed in parens, otherwise Crystal gets confused as to "what's going on and where am I?"

Hope that helps, if not try posting the information I noted at the beginning of my post.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.