| Author |
Message |
trba
Newbie
Joined: 15 Jan 2009
Online Status: Offline
Posts: 7
|
  
Topic: select specific text from field / row
Posted: 15 Jan 2009 at 2:44am |
Hi,
i have a report thats shows records from an oracle database,
i have a string field that is a long line of text with each section seperated by a comma.
i.e
deposit: date 01/01/2009 01:00:00, account 123456, Amount £100.00
i need to extract the 123456 and the £100.00 seperatley to be able to add it in below that line so that i can show the name to the account number by linking from a different tabel and to be able to toal the amount for all accounts.
i can filter but am unsure how to extract these sections from 1 string field.
thanks
|
 IP Logged |
|
|
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 15 Jan 2009 at 6:34am |
Shared Variables and formulae to the rescue.
Create 2 shared variables in a formula that parses the string. Something like:
shared numbervar account;
shared numbervar amount;
local numbervar start;
local numbervar comma;
start := instr({field}, "account");
start := instr({field}, " ", start) + 1;
comma := instr({field}, ",",start);
account := mid({field}, start, comma - start);
repeat for the amount.
then you can access the shared variables through 'simple' display formulae, like:
shared numbervar amount
Hope this helps
|
| IP Logged |
|
trba
Newbie
Joined: 15 Jan 2009
Online Status: Offline
Posts: 7
|
Posted: 16 Jan 2009 at 3:18am |
thanks lockwelle,
i have copied in the variable:
shared numbervar account;
shared numbervar amount;
local numbervar start;
local numbervar comma;
start := instr({field}, "account");
start := instr({field}, " ", start) + 1;
comma := instr({field}, ",",start);
account := mid({field}, start, comma - start);
and altered the {field} to the field with the text in
but when i run the check on the formulae it comes up on the last line
"a number is required here"
for: mid({field}, start, comma - start);
not sure what went wrong
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 16 Jan 2009 at 6:48am |
oops, nothing, mid returns a string, but account is defined as a number. alter the last line to be:
account := val(mid({field}, start, comma - start));
this should remove the error message...if the accounts are strings, just change the numbervar to stringvar. Crystal is just ensuring that the variable declarations match.
Hope this helps
|
| IP Logged |
|
trba
Newbie
Joined: 15 Jan 2009
Online Status: Offline
Posts: 7
|
Posted: 16 Jan 2009 at 8:41am |
thanks Lockwelle,
i'm still having a little trouble, i have amended to:
shared stringvar account;
shared stringvar amount;
local numbervar start;
local numbervar comma;
start := instr ({PROT_MESSAGE.PROTMESSAGETEXT}, "amount");
start := instr({PROT_MESSAGE.PROTMESSAGETEXT}, " ", start) + 1;
comma := instr({PROT_MESSAGE.PROTMESSAGETEXT}, ",",start);
amount := mid({PROT_MESSAGE.PROTMESSAGETEXT}, start, comma - start);
but i get the date section showing not the amount or account section.
if i change the local lines at the top i get a message saying the instr needs to be a string.
also if i use the val line it shows up 0 in the display box.
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 16 Jan 2009 at 11:29am |
OK, I am going to take a guess that it is not finding 'amount'. Crystal is case sensitive, so it should be 'Amount', or you can use tolower('amount'), that should help. Instr is a function that finds the occurrance of one string inside of another.
Hope this helps
|
| IP Logged |
|
trba
Newbie
Joined: 15 Jan 2009
Online Status: Offline
Posts: 7
|
Posted: 16 Jan 2009 at 1:27pm |
hi Lockwelle,
i think it had something to do with the 2nd start so i modified it to:
shared numbervar account;
local numbervar start;
local numbervar comma;
start := instr ({PROT_MESSAGE.PROTMESSAGETEXT}, "account")+ 8;
comma := instr({PROT_MESSAGE.PROTMESSAGETEXT}, ",");
account := val(mid({PROT_MESSAGE.PROTMESSAGETEXT}, start, comma ));
and it works, it also works for the amount, then all i did was alter the field to a number with currency and it puts it back as £.
thanks for your help!!
|
| IP Logged |
|
|
|
