Help with a Formula to convert numbers to text. 2

[kapaa]

Crystal 8 - SQL Server/SQL Database.

In my report I have a column call "category" and the data returned looks like the below:

Solution Category
How to fix a HP Laser 1-55-156
How to fix a HP DotM 1-55-156
How to fix a modem 1-55-167-293

What I tried to do was create a Formula Field to convert those numbers into a text value using this:

if {INDEX.RELATIONAL_ID} like '*-55*' then 'SDT' else
if {INDEX.RELATIONAL_ID} like '*-156*' then '>Printer' else
if {INDEX.RELATIONAL_ID} like '*-167*' then '>Modem' else
if {INDEX.RELATIONAL_ID} like '*-188*' then '>Scanner' else
etc. etc.

This is only half working, the new column is only converting the first number it finds, the -55 as shown below, when I was hoping it would return SDT>Printer or SDT>Modem>Motorola.

Solution Category Convert
How to fix a printer 1-55-156 SDT
How to fix a HP DotM 1-55-156 SDT
How to fix a modem 1-55-157 SDT

 

[synapsevampire]

Remove the STD portion or place it last:

if {INDEX.RELATIONAL_ID} like '*-156*' then '>Printer' else
if {INDEX.RELATIONAL_ID} like '*-167*' then '>Modem' else
if {INDEX.RELATIONAL_ID} like '*-188*' then '>Scanner' else
if {INDEX.RELATIONAL_ID} like '*-55*' then 'SDT'

-k

 

[hilfy]

If statements evaluate until they have a true condition and then they don't look at any further conditions.

What I think you need to do is break your category field down into separate pieces, evaluate the pieces, and then add them together to make your fill string. For example:

{@MiddleValue}
NumberVar startAt;
NumberVar endAt;
StringVar value;
startAt := Instr({INDEX.RELATIONAL_ID}, '-');
endAt := Instr(startAt + 1, {INDEX.RELATIONAL_ID}, '-');
value := substr({INDEX.RELATIONAL_ID}, startAt + 1, endAt - startAt - 1);

Switch(
value = '55', 'SDT',
value = '56', 'Some other value',
true, 'Default Value'
)

{@EndValue}
StringVar value;
value := mid({INDEX.RELATIONAL_ID}, InStrRev({INDEX.RELATIONAL_ID}, '-') + 1);

Switch(
value = '156', '>Printer',
value = '167', '>Modem',
value = '188', '>Scanner',
<etc...>
True, ''
)

If you can more than two possible entries, you'll have to add an additional formula for each one that you want to parse into a string.

-Dell



A computer only does what you actually told it to do - not what you thought you told it to do.

 

[synapsevampire]

Ahhh, maybe what you'retrying to say is that you want two values for each record?

An if dropsout after itfinds a hit, soit'snotgoing to do thingstwice(that would be horribly logically to implement anyway).

Better try posting example data and what you expect the output of that data to looklike,descriptions fail...

Also try:

if {INDEX.RELATIONAL_ID} like '*-156*' then '>Printer' else
if {INDEX.RELATIONAL_ID} like '*-167*' then '>Modem' else
if {INDEX.RELATIONAL_ID} like '*-188*' then '>Scanner' else
if {INDEX.RELATIONAL_ID} like '*-55*' then 'SDT' else
"Blah"


-k

 

[lbass]

Try:

stringvar array x := split({INDEX.RELATIONAL_ID},"-");
numbervar i := 0;
numbervar j := ubound(x);
stringvar y := "";

for i := 2 to j do(
y := y + (
select x
case "55" : "SDT"
case "156" : "Printer"
case "167" : "Modem"
case "293" : "Scanner"
)+ ">");
if len > 1 then
left(y,len-1)

-LB

 

[kapaa]

Thanks everyone, like Hilfy said, 'if statements' search until they find a true condition and then stop. I was hoping there might be a more robust statement other than 'if'.

Synapsevampire, the order of the if statements didn't make a difference, either way it will come to one of the first numbers in a string (eg 1-55-155-201) and replace with text but then ignores the rest. So it might replace 201 with NCR and that's all.

Hilfy, I tried your code and it didn't like {@MiddleValue}, {@EndValue}, says 'this field name not known' and substr on the line value:=substr... it says 'A number, currency amount, boolean, date .. string expected here'. I don't know enough coding to understand this making it hard to fine-tune and fix.

LBASS - I tried your formula as well, it worked but it returns "True" for everything.

 

[synapsevampire]

Yeah, you're starting to get an understanding of how programming, or at least IFs work.

Now if you would grasp the notion of supplying basic requirements we'd be done with this exercise.

-k

 

[lbass]

My formula would not return true, so I think you forgot the last clause.

-LB

 

[kapaa]

I think I have a work around for this. There apprears to be a pattern to the numbers this system is using which means I build "if - then" statments such as:

if '1-156' then Hardware>Printer else
if '1-156-201' then Hardware>Printer>HP else
if '1-156-204' then Hardware>Printer>NCR else
... and so on.

It will be just a little more work but it appears this will do the job of getting data exported from on system and merged to another. Right now I'm running it on development, production will happen in a month or two and this formula will save a lot of repeative work using find/replace in a text file.

 

[lbass]

Please retry my formula. You don't need to do that.

-LB