instr help 1

[dcjames]

Our database has a memo field called description (example below)
"Smith Joe
User Object Details
Lan Id:
Location: example
Employee Number: 1111111
Manager: 2222222
Group:
Division Code updated successfully in OIM
User Info after processing user in OIM
Smith Joe
User Object Details
Lan Id: aaaaaa
Location: example
Employee Number: 1111111
Manager: bbbbbb
Group: Full-Time
DIVISION_CODE attribute updated successfully"

I need to be able to extract the value's of Lan ID and Employee Number. I'm currently using the following
Formula for Lan ID
//whileprintingrecords;
//numbervar v_start;
//numbervar v_end;

//v_start := instr({Sheet1_.Description},"Lan Id:");
//v_end := instr(mid({Sheet1_.Description}, v_start + 14),"");

//mid({Sheet1_.Description},v_start,(14 + v_end))

Formula for Employee Number
whileprintingrecords;
numbervar v_start;
numbervar v_end;

v_start := instr({Sheet1_.Description},"Employee Number:");
//v_end := instr(mid({Sheet1_.Description}, v_start + 24)," ");
v_end := instr(mid({Sheet1_.Description}, 18,8)," ");

if {Sheet1_.Description} like ["*Employee Number:*"] then mid({Sheet1_.Description},v_start,(24 + v_end)) else ""

If anyone has a better way of doing this I would appreciate any suggestions.

One problem I run into periodically is "Start position is less than 1 or not an integer"

 

[hilfy]

You're getting this error because sometimes the memo field doesn't contain the data you're looking for. I would make a simple change to your formula like this:

whileprintingrecords;
numbervar v_start;
numbervar v_end;

v_start := instr({Sheet1_.Description},"Employee Number:");
if v_start >= 1 then v_end := instr(mid({Sheet1_.Description}, v_start + 24)," ");

if v_start >= 1 then mid({Sheet1_.Description},v_start,(24 + v_end)) else ""

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[dcjames]

Thanks Hilfy this worked perfectly!

 

[dcjames]

I'm not sure how to modify this formula to look for the text "Employee Number:" or "Lan Id:" and the data following up to the next space. As this is a memo field I find that in some cases the next set of values are being returned.

Any help is greatly appreciated

 

[hilfy]

Is the data ALWAYS in the order that you show in your original post or could the entries be in random order?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[dcjames]

They are typically in this order

 

[hilfy]

Try something like this:

whileprintingrecords;
numbervar v_start;
numbervar v_end := 0;
stringvar v_work;

v_start := instr({Sheet1_.Description},"Employee Number:");

//get the string starting AFTER "Employee Number:", trimming any leading spaces
if v_start >= 1 then
(
v_work := ltrim(mid({Sheet1_.Description}, v_start + 16));
v_end := InStr(v_work, chr(13) + chr(10)); //look for CR/LF first...
if InStr(v_work, " ") < v_end then v_end := InStr(v_work, " ")
)
if v_end >= 1 then left(v_work,v_end) else ""

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[dcjames]

Dell,

Thank you for the quick response. When using your updated formula I get the following for the last line (if v_end>=1 then left(v_work,v_end) else"" "The Remaining Text does not appear to be part of the formula".

 

[hilfy]

I forgot to include the semi-colon at the end of the "v_end :=..." statement.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[dcjames]

Thank you Dell