Jump to content

extracting content from field to match another table in another DB


This topic is 2932 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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 by Lee Smith
Added the BTW
Link to comment
Share on other sites

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.

  • Like 2
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2932 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.