• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 983
  • Last Modified:

Crystal Reports 2008, In a formula, how do I replace multiple strings with a database field?

Hello Experts-

I have a database string that appears as follows:
The <%7%> jumped over the <%moon%> and laughed at the <%5%>.

I have a database table that contains values as follows:
ID      Value
1      Cat
2      Pig
3      Horse
4      Monkey
5      Dog
6      Chicken
7      Cow

I am trying to figure out in a Crystal Reports formula, how to swap out only numeric values that land inbetween the “<%” and “%>” tags.  Any characters that land inbetween the two tags should be left alone.

The final result above would look as follows:
The <%Cow%> jumped over the <%moon%> and laughed at the <%Dog%>.

Any Help would be appreciated.
0
LukeSteele
Asked:
LukeSteele
  • 8
  • 6
  • 2
1 Solution
 
mlmccCommented:
How big a table is it?

Here is an idea

Create a report to build an array from the table
In the report add a formula to the report header
WhilePrintingRecords;
Shared StringVar Array WordList;
""

In the detail section add a formula
WhilePrintingRecords;
Shared StringVar Array WordList;
If {IDField} > UBound(WordList) then
    ReDim Preserve WordList[{IDField}];
WordList[{IDField}] := {ValueField};
""

In the main report add a formul to the report header;
WhilePrintingRecords;
Shared StringVar Array WordList;
""

Right click the report header in the left margin
Click INSERT SECTION BELOW

Insert the report that builds the array into the new section

In the main report detail section add a formula
WhilePrintingRecords;
Shared StringVar Array WordList;
Local NumberVar Index;
Local StringVar strCurrentSentence;

strCurrentSentence := {DBString};
For Index := 1 to UBound(WordList) do
    strCurrentSentence  := Replace(strCurrentSentence,"%" & CStr(Index,0,"") &  "%","%" & WordList[Index] & "%");

strCurrentSentence  

mlmcc
0
 
LukeSteeleAuthor Commented:
Thank you for the reply back.

The table at the moment has 484 records.

However, I am already inside a subreport so adding another report on top of that will not work.  All of this would have to happen within the subreport.

I did try using some of the code listed above and I am running into 2 issues with the 2nd piece of code:

WhilePrintingRecords;
Shared StringVar Array WordList;
If {IDField} > UBound(WordList) then
    ReDim Preserve WordList[{IDField}];
WordList[{IDField}] := {ValueField};
""

I first was receiving an "Else" missing error so I moved the ReDim Preserve WordList[{IDField}]; code up above the If statement.

The second issue I was receiving was array list must be between integers 1 to 1000.  I looked into the details of the database table and a few of the ID columns do have negative numbers.  I cannot remove those negative numbers as they are system delivered fields.

Any other thoughts?

Thank you.
0
 
LukeSteeleAuthor Commented:
I guess another question would be, could I potentially build the array in a previous subreport and then bring that array into the following subreport?
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
mlmccCommented:
You could build it in another subreport.

How large (small?) are the negative numbers?

How large can the Id field be?  Can it be larger than 1000?

mlmcc
0
 
LukeSteeleAuthor Commented:
Currently the negative numbers go back to -7.  The ID field could potentially break 1000, but it will take a while to get over 1000 values.  It took us 2 years to get up to 484 values.

Thanks-
Luke
0
 
mlmccCommented:
Are the negative number records needed?

mlmcc
0
 
LukeSteeleAuthor Commented:
Yes they are.  I could use a SQL command instead that could go through and add 10 to every ID value.  That would just have to be incorporated into the other portions of the formula when the number gets extracted out of the string.

Luke
0
 
mlmccCommented:
No.  Next week you'll find a -11

Check this report

mlmcc
ChangeWords.rpt
0
 
LukeSteeleAuthor Commented:
It looks like that report worked.

I had to carry the DeclareList into the 2nd subreport to bring the array in.  Everything is working except for one piece, in the WordList formula, I actually have a data id of 0 so I need to qualify against ID 0, could I alter the Negative number portion below:

Else If {DATA_FIELDS.ID} <= 0 then
0
 
mlmccCommented:
Not directly.  0 is an invalid array index so you will have to ADD 1 to each number

Else If {DATA_FIELDS.ID} <= 0 then
Use  -{DATA_FIELDS.ID} + 1 as the index

mlmcc
0
 
James0628Commented:
This is untested, of course, but, looking at mlmcc's report:

 I think I would change the first if-then in @BuildWordList in the subreport to:

If {WordTable.ID} >= 0 then
(
   If {WordTable.ID} + 1 > UBound(WordList) then
   (
      ReDim Preserve WordList[{WordTable.ID} + 1];
      WordList[{WordTable.ID} + 1] := {WordTable.Word};
   )
   Else
      WordList[{WordTable.ID} + 1] := {WordTable.Word};
)


 Basically, change the positive test to >= 0, and add 1 to the positive ID's (0 becomes 1, 1 becomes 2, and so on).

 Then change the second for loop in @FixSentence in the main report to:

For Index := 1 to UBound(WordList) do
    strCurrentSentence  := Replace(strCurrentSentence, "%" & CStr(Index - 1,0,"") & "%" , WordList[Index]& "%");


 Just subtract 1 from Index in the string search (so for array element 1, you look for %0%, and so on).

 James
0
 
James0628Commented:
You probably already know this, but I just noticed that the second for loop in @FixSentence in mlmcc's report seems to be missing a "%".  The for loop I posted should be:

For Index := 1 to UBound(WordList) do
    strCurrentSentence  := Replace(strCurrentSentence, "%" & CStr(Index - 1,0,"") & "%" , "%" & WordList[Index] & "%");


 James
0
 
LukeSteeleAuthor Commented:
Thank you for all the help on this!
0
 
LukeSteeleAuthor Commented:
James, I did catch the missing percent sign, thanks for pointing that out though.  mlmcc, thank you for everything on this, I've been trying for quite some time to get this working.
0
 
LukeSteeleAuthor Commented:
Hi mlmcc-

So it took us a lot quicker to break 1000 data fields now.  This solution before is now failing because the array is breaking 1000.  Is there some extra code to add into the array to make it break 1000?

This is the code that worked with data fields 1 - 1000 and negative numbers:

WhilePrintingRecords;
Shared StringVar Array WordList;
Shared StringVar Array NegativeWordList;
Local Numbervar NegativeId;

If {DATA_FIELDS.ID} > 0 then
(
   If {DATA_FIELDS.ID} > UBound(WordList) then
   (
      ReDim Preserve WordList[{DATA_FIELDS.ID}];
      WordList[{DATA_FIELDS.ID}] := {DATA_FIELDS.NAME};
   )
   Else
      WordList[{DATA_FIELDS.ID}] := {DATA_FIELDS.NAME};
)
Else If {DATA_FIELDS.ID} <= 0 then
(
   NegativeId := -{DATA_FIELDS.ID} + 1;
   If NegativeId > UBound(NegativeWordList) then
   (
      ReDim Preserve NegativeWordList[NegativeId];
      NegativeWordList[NegativeId] := {DATA_FIELDS.NAME};
   )
   Else
      NegativeWordList[NegativeId] := {DATA_FIELDS.NAME};
)
ELse
   ""
0
 
mlmccCommented:
The 1000 limit seems to be hardcoded into Crystal.  Other limits have registry values so they can be reset.

For example the default is 1000 items in a parameter list.  There is a registry setting that can be used to set the value much higher.

If such a setting exists nobody has published one.

See the comments in your new question
 http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_27788420.html

mlmcc
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 8
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now