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

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

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.

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?

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

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

Are the negative number records needed?

mlmcc

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

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

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

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

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

Thank you for all the help on this!

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.

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
   ""

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
 https://www.experts-exchange.com/questions/27788420/Array-to-break-1000-elements.html

mlmcc