extracting content from field to match another table in another DB

[the_furious]

Hi All,

 

I have an imported field from excel labelled as "username"

e.g. username contains the following string: andy johnson (ajohn)

at times, some fields are as follows: tpage ()

 

"ajohn", "tpage" are actually unique login IDs found from another table in another database.

is there any method(s) to grab these login IDs for me to match my other table?

 

Thanks heaps!

[Lee Smith]

I’m not sure I’m understanding correctly, but maybe the Substitute Function will work for you. i.e.

 

Substitute ( YourField ; "andy johnson” ; “ajohn" )

 

BTW, why did you choose the Server Topic? is the server involved, or did you intend this in a different topic?

Edited November 18, 2014 by Lee Smith
Added the BTW

[comment]

e.g. username contains the following string: andy johnson (ajohn)

at times, some fields are as follows: tpage ()

 

"ajohn", "tpage" are actually unique login IDs found from another table in another database.

is there any method(s) to grab these login IDs for me to match my other table?

 

A more detailed explanation would be useful, IMHO. As it is, I can only guess you want something like =

Let ( [
start = Position ( username ; "(" ; 1 ; 1 ) + 1 ;
end = Position ( username ; ")" ; start ; 1 ) ;
id = Middle ( username ; start ; end - start )
] ;
Case ( IsEmpty ( id ) ; LeftWords ( username ; 1 ) ; id )
)

This tries to extract the portion of the text that is between parentheses. If the result is empty, then it returns the first word in the field.

[the_furious]

I’m not sure I’m understanding correctly, but maybe the Substitute Function will work for you. i.e.

 

Substitute ( YourField ; "andy johnson” ; “ajohn" )

 

BTW, why did you choose the Server Topic? is the server involved, or did you intend this in a different topic?

 

Hi,

 

Didn't realized I posted my query on the wrong place :X

 

A more detailed explanation would be useful, IMHO. As it is, I can only guess you want something like =

Let ( [
start = Position ( username ; "(" ; 1 ; 1 ) + 1 ;
end = Position ( username ; ")" ; start ; 1 ) ;
id = Middle ( username ; start ; end - start )
] ;
Case ( IsEmpty ( id ) ; LeftWords ( username ; 1 ) ; id )
)

This tries to extract the portion of the text that is between parentheses. If the result is empty, then it returns the first word in the field.

 

Sorry I didn't make myself clearer.

I need to compare this "username" field with "userID" field from another table.

if there is a match, it will then grab the relevant information (dept code, user name, etc) from the matching "userID" field.

[comment]

Sorry I didn't make myself clearer.

I need to compare this "username" field with "userID" field from another table.

if there is a match, it will then grab the relevant information (dept code, user name, etc) from the matching "userID" field.

 

This part I actually got. It's what exactly is (or can be) in the username field that's not entirely clear.

 

Anyway, try defining a calculation field (result is Text) with the above formula, and use it as the matchfield in a relationship to the other table, matching userID in the other table. If there is a match, you will be able to see the related data in fields from the other table that you place on your layout (in this table). Or in a portal, if you expect more than one match.

[the_furious]

hi!

 

thanks for the advice!

 

the calculation is working great!