Conversion Formula (Crystal Reports Formula)

[Former Member]

how can I write a formula to do the following?

if {ez_configured_line_item.Configured_line_item_no} has the part number "vesm-1010-hd" then display this text "19 Shape"

[abhilash_kumar · Accepted solution]

Hi Jarrod,

That'll be:

if {ez_configured_line_item.Configured_line_item_no} = "vesm-1010-hd" then

"19 Shape"

If the value "vesm-1010-hd" is part of a larger string that the field holds, then you'll need to search through the string . So, the formula will be:

if instr({ez_configured_line_item.Configured_line_item_no}, "vesm-1010-hd") > 0 then

"19 Shape"

-Abhilash

[Former Member · Accepted solution]

When I check the formula for errors it says "VESM-1010-HD" A number is required here. that's when I use the first formula.

Although I wonder if I should be using the second formula. the program that provides the data to crystal is passing a long string of values that will contain the "VESM" option somewhere in it.

I tried the second one and it says:

Not enough arguments have been given to this function and it highlights this section:

instr({ez_configured_line_item.Configured_line_item_no}, "vesm-1010-hd")

[abhilash_kumar · Accepted solution]

That's strange. What is the datatype of the field {ez_configured_line_item.Configured_line_item_no}?

If you look at the error message the 1st formula throws, it suggests that the field is a number.

-Abhilash

[Former Member · Accepted solution]

Let me dig around a little. I must be pulling the wrong field.

[Former Member · Accepted solution]

Is there a limitation on how long a formula can be?

[JWiseman · Accepted solution]

hi Jarrod,

64k is the output limit of a formula which i believe translates into 32,000 + characters. if your formula output is too large the error that you receive will reflect that limit being reached. in your case though you're just doing an instring on a memo field which would not be bound by that limit.

or is there more to your formula than the code above?

cheers,

jamie

[Former Member · Accepted solution]

Thanks Jamie,

Along that same line of questioning. IF I have a string that is passed to me and I need to create a formula look for a variable that starts with VE and display the description for that variable. How would I do that?

here's an example of what im getting as a string:

AERA-1007:1,AIPG-1000:1,GLAD-0001:1,FBDD-0006-AX1:1,DRUM-0001-AX1:2,BSYS-0001:1,NOSE-0003:1,UCUP-1001:1,LITE-0001:1,WIRE-0002:1,FFDR-1001:1,RFDR-1002:1,LWHC-1000:0,PPCV-1000:1,PRDL-1002:1,RDOC-1000:1,

StartAction='Run New',USStockTruck='Stock Truck',USWheelbase=187,USRearAxleWeight=7000,USFrontAxleWeight=9500,USFifthWheelHeight=48.5,USModel='',USCapacity='',USDomicile='',USDuty='',USGVWR='',USHoppers='',USAxleConfiguration='CT',USAxleAddCount='None',USWidth='96',USApplication='',USLadderManhole2='None',USLadderManhole3='None',USLadderManhole4='None',USLadderManhole5='',USFrameFront='',USWalkwayTread='Adhesive',USManhole1='None',USType='',USVesselMaterial='Mill',USVesselInternalWeld='Class I',USLowerHopperCones='WO45',USHopperAccessDoor='None',USVesselInternalBaffles='Standard',USVesselInternalBaffles23='Standard',USVesselInternalBaffles34='Standard',USVesselInternalBaffles45='Standard',USManholeHardware1='',USManholeHingeDirection1='',USLadderManhole1='None',USLadderOffsetRear='None',USLadderSide='None',USLadderTwoTier='None',USManhole2='None',USManholeHardware2='',USManholeHingeDirection2='',USFrameRear='',USChassisAluminumFrontFrameCovers='None',USWalkwaySides='None',USFallProtection='None',USFallProtectionLocation='None'

[JWiseman · Accepted solution]

hey Jarrod,

i didn't see anything in there that started with VE but did see a bunch of what i thought were variables (and please correct me if i'm wrong) that started with USVe so i went with that...here's a formula that you could use that displays all instances of a certain token of your field:

stringvar f:= "AERA-1007:1,AIPG-1000:1,GLAD-0001:1,FBDD-0006-AX1:1,DRUM-0001-AX1:2,BSYS-0001:1,NOSE-0003:1,UCUP-1001:1,LITE-0001:1,WIRE-0002:1,FFDR-1001:1,RFDR-1002:1,LWHC-1000:0,PPCV-1000:1,PRDL-1002:1,RDOC-1000:1,"+
"StartAction='Run New',USStockTruck='Stock Truck',USWheelbase=187,USRearAxleWeight=7000,USFrontAxleWeight=9500,USFifthWheelHeight=48.5,USModel='',USCapacity='',USDomicile='',USDuty='',USGVWR='',USHoppers='',USAxleConfiguration='CT',USAxleAddCount='None',USWidth='96',USApplication='',USLadderManhole2='None',USLadderManhole3='None',USLadderManhole4='None',USLadderManhole5='',USFrameFront='',USWalkwayTread='Adhesive',USManhole1='None',USType='',USVesselMaterial='Mill',USVesselInternalWeld='Class I',USLowerHopperCones='WO45',USHopperAccessDoor='None',USVesselInternalBaffles='Standard',USVesselInternalBaffles23='Standard',USVesselInternalBaffles34='Standard',USVesselInternalBaffles45='Standard',USManholeHardware1='',USManholeHingeDirection1='',USLadderManhole1='None',USLadderOffsetRear='None',USLadderSide='None',USLadderTwoTier='None',USManhole2='None',USManholeHardware2='',USManholeHingeDirection2='',USFrameRear='',USChassisAluminumFrontFrameCovers='None',USWalkwaySides='None',USFallProtection='None',USFallProtectionLocation='None'";

stringvar t:= 'USVe'; // this is the token that the following control structure will search for

stringvar array af:= split(f,','); // your text field is split into an array, using the comma as a separator
numbervar u:= ubound(af); // the number of comma separated pieces of info in your field

numbervar i:= 0; stringvar o:= ''; // some variables are initiated
while i < u do
(
i:= i + 1;
if af[i] startswith t
    then o:= o + af[i] + chr(10);
);
o

you can substitute the "f" variable for your database field. the results look like this...

USVesselMaterial='Mill'

USVesselInternalWeld='Class I'

USVesselInternalBaffles='Standard'

USVesselInternalBaffles23='Standard'

USVesselInternalBaffles34='Standard'

USVesselInternalBaffles45='Standard'

i hope that this helps,

jamie

[Former Member · Accepted solution]

Jamie,

Does this formula take into account that the string changes every time? I could send over the full string that actually has the VE data in it. I really appreciate the help. I would prefer to email it, rather than post it if possible. My email address should be in my profile if you want to shoot me a note.

[JWiseman · Accepted solution]

hey Jarrod, when you substitute the "f" stringvar for your field then it will certainly take into account the changing text.

i.e. change the above syntax to start with something like

        stringvar f:= {yourtable.yourstringfield};

        // you don't need to change the syntax below
        stringvar t:= 'USVe'; // this is the token that the following control structure will search for

stringvar array af:= split(f,','); // your text field is split into an array, using the comma as a separator
numbervar u:= ubound(af); // the number of comma separated pieces of info in your field

numbervar i:= 0; stringvar o:= ''; // some variables are initiated
while i < u do
(
i:= i + 1;
if af[i] startswith t
    then o:= o + af[i] + chr(10);
);
o

[Former Member · Accepted solution]

Jamie, Do you have any advise or suggestion for training on how to do these?

[Former Member · Accepted solution]

Jamie, Please see my new post. I didn't think it was fair to you to keep working on a post that was marked answered.