Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Multi-replace / replace between Post Reply Post New Topic
Author Message
brain
Newbie
Newbie


Joined: 13 Apr 2010
Online Status: Offline
Posts: 36
Quote brain Replybullet Topic: Multi-replace / replace between
    Posted: 28 Jun 2015 at 5:00pm
I have a field in my DB that contains concatenated data (PO# & temperature), like this:

5005219/-10, 5005220/0, 5005303/34

The user now wants to see it like this (one entry per line, omit the temperatures)

5005219
5005220
5005303

I can get as far as replacing the commas with line breaks and stripping out spaces using this formula:

Replace(Replace({<MyFieldName>},",",chr(13)+chr(10))," ","").

That gets me halfway there, with this result:

5005219/-10
5005220/0
5005303/34

Not bad, but how would I now replace the slash and unknown number of characters after each slash? Some temperatures are negative, some are zero, and some are positive, so the number of characters after each slash will be somewhere from 1 to 3, and all but the last instance of those would be followed by chr(13) once the above formula is applied.

So, ideally, I suppose I would take the entire formula above and
1) delete from slash to the character before chr(13). If there is a way to do this (i.e. delete multiple instances from a known character to the character before another known character), it would get rid of all except the last slash & temperature. Then that could be removed by 2) replacing the slash and all following characters (i.e. the final temperature).

IP IP Logged
niha
Newbie
Newbie
Avatar

Joined: 05 Jun 2015
Online Status: Offline
Posts: 28
Quote niha Replybullet Posted: 29 Jun 2015 at 10:49pm
The INSTR() function searches a string for another string. It takes two parameters, like this:
INSTR(string to search, what to search for)

From INSTR you will get start postion of '/' then
use this position in replace function of crystal report.

It will help you for your problem.

IP IP Logged
brain
Newbie
Newbie


Joined: 13 Apr 2010
Online Status: Offline
Posts: 36
Quote brain Replybullet Posted: 30 Jun 2015 at 3:22am
I am familiar with INSTR, but that helps only for the slash itself. As I explained, I need to replace not only a varying number of instances of the slash (/), but also a varying number of characters following the slash--from the slash to the next comma, except for the last one, in which case there is no comma following. I really need some sort of iterative process that can find the first slash, delete from there to the next comma, replacing the comma with a line feed/carriage return, then, when there are no more of those, replace the final slash and any characters following with null.

That is, I need to change this:

5005219/-10, 5005220/0, 5005303/34

to this

5005219
5005220
5005303

?

In the first line, I need to remove the slash and three following characters. In the next one, it is the slash and one following character. In the last line it is the slash and two following characters. But the numbers (-10, 0, 34) are just examples; there may be other numbers there.

I did come up with a solution I made a function and view in my DB that produced this:

5005219,5005220,5005303 (i.e. without the slash & temperatures)

instead of this

5005219/-10, 5005220/0, 5005303/34.

Then I was easily able to use this formula:

Replace({<MyFieldName>},",",chr(13)+chr(10))
IP IP Logged
niha
Newbie
Newbie
Avatar

Joined: 05 Jun 2015
Online Status: Offline
Posts: 28
Quote niha Replybullet Posted: 05 Jul 2015 at 11:37pm
field_name ='605 KL1 - Daniel Steve'

The syntax is {field_name} [11 to 23]

Result = {field_name} [11 to 23] -> Result = 'Daniel Steve'

Now you have to know how manny digit weillbe there before slash and modify this as per your requirment/
IP IP Logged
brain
Newbie
Newbie


Joined: 13 Apr 2010
Online Status: Offline
Posts: 36
Quote brain Replybullet Posted: 06 Jul 2015 at 2:58am
The number of digits before the slash will vary in every instance, as will the number of characters between the slash and the next comma and the number of entries (and therefore commas).

That is, as originally noted, I have to replace an unknown number of instances of an unknown number of characters at unknown positions. The constant is that I need to replace everything from each slash to each comma with a linefeed/carriage return, then when done with that, remove the last slash and everything following.

But, as noted, my solution was to come up with a function in my database that presents me with the data in a format that does not include the slash or the temperatures. Then it was easy to just replace each comma with a linefeed/carriage return.
IP IP Logged
mikebird
Groupie
Groupie
Avatar

Joined: 04 Mar 2008
Location: United Kingdom
Online Status: Offline
Posts: 70
Quote mikebird Replybullet Posted: 26 Oct 2016 at 11:28am

I want to add any more conditions to this formula. I copy the same statement with semicolon after the first
this parses. no errors. all works individually but putting the second command with different input string, to swap for appropriate string
this just gets ignored. maybe I need to use an evaluation time ..
before / after reading / writing?

just these two to test. There might be more. same input field, to swap to a better label. I tried each in different order. up/down. only one works, when syntax is matched.   right('1234') to be abcd √   right('4321') to be dcba

each runs fine for each case alone. how implement in one formula, if I need 8?

nice that the rest of string before right('xxxx') stays the same, too. just replacing with new label.

can I make this formula work for several cases? need a different syntax? or put this outside of a formula somewhere? I could append the formula text but a bit embarrassing. just a personal test example

use a filter? just changing string contents, global conditions. could do in T-SQL, but can't import this data. Only Crystal can handle it

maybe why it fails: when one happens, running other command, can't see the digits cos it has changed to letters. No! I'm wrong. I want exactly the same command to function with the other digits. In the result, one was done, the other shows the same, not relabelled. should try alternative syntax.

thanks

Edited by mikebird - 26 Oct 2016 at 12:19pm
Life = luck (+/-) and effort (+/-). If you have no luck & make no effort, then you are dead
IP IP Logged
mikebird
Groupie
Groupie
Avatar

Joined: 04 Mar 2008
Location: United Kingdom
Online Status: Offline
Posts: 70
Quote mikebird Replybullet Posted: 27 Oct 2016 at 8:01am

I solved it √ easy

ignoring the Replace function

I started from scratch with if {report field} = 'xxxxx' then 'more detail'
else if {report field} = 'otherwise' the 'different appropriate detail'
else {report field}

putting this formula anywhere .. crosstab, list... makes perfect

I will add more cases to neaten the report
whenever new data is updated
Life = luck (+/-) and effort (+/-). If you have no luck & make no effort, then you are dead
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.015 seconds.