Look up Name in two database and display if found in any

[Anonymous]

hi Expert 

I am trying to do a lookup in multiple database and if found  in any info than display the name 

Client id acrros the databases for the customer is same. 

Like below: 

if i enter the client id in text  1 than it will look the value in both the database, if record match than display name in the client Name box 2 and if no records found than it will display no result found. 

please help

[v-yueyun-msft]

Hi , @Anonymous 

I create the test data by Button-OnSelect:

ClearCollect(database1, {'Client Id':1,Name:"Sam"}, {'Client Id':2,Name:"Ram"}, {'Client Id':3,Name:"DamUpdate"}, {'Client Id':4,Name:"Tam"});
ClearCollect(database2, {'Client Id':1,Name:"Sam"}, {'Client Id':2,Name:"Ram"}, {'Client Id':3,Name:"Dam"}, {'Client Id':4,Name:"Tam"},{'Client Id':6,Name:"Bam"},{'Client Id':7,Name:"Fam"})

We can use code in the TextInput2-Default:

If(TextInput1.Text<>"" ||TextInput1.Text=Blank() , 
If( LookUp(database1,'Client Id'=Value( TextInput1.Text)).Name=Blank() ||LookUp(database2,'Client Id'=Value( TextInput1.Text)).Name=Blank() ,"no result found." ,
If( LookUp(database1,'Client Id'=Value( TextInput1.Text)).Name = LookUp(database2,'Client Id'=Value( TextInput1.Text)).Name , LookUp(database1,'Client Id'=Value( TextInput1.Text)).Name
, "no result found.") ))

Best Regards,

Yueyun Zhang

[Anonymous]

I am confused with your code as in last you are trying to match the records between two table. I dont want to have, it will try to check the value in two database and display if found in any 

If(TextInput1_11.Text<>"" ||TextInput1_11.Text=Blank() , 
If( LookUp(CLIENT_INFO,Source_Client_ID= TextInput1_11.Text).Client_DOB=Blank() ||LookUp('gci.tbl_client_policy_pii_raw','Source Client ID'=Value( TextInput1_11.Text)).'Date of Birth'=Blank() ,"no result found." ,
If( LookUp(CLIENT_INFO,Source_Client_ID= TextInput1_11.Text).Client_DOB = LookUp('gci.tbl_client_policy_pii_raw','Source Client ID'=Value( TextInput1_11.Text)).'Date of Birth', LookUp(CLIENT_INFO,Source_Client_ID= TextInput1_11.Text).Client_DOB
, "no result found.") ))

for the cleint name i was using the following code 

If(
    TextInput1_11.Text in CLIENT_INFO.Source_Client_ID,    /* <-- Check the entered ClientId is valid (existed in your SQL Table already) */
    LookUp(CLIENT_INFO, TextInput1_11.Text in Source_Client_ID,Client_Name),"No Records Found"
)||If(
    TextInput1_11.Text in 'gci.tbl_client_policy_pii_raw'.'Source Client ID',    /* <-- Check the entered ClientId is valid (existed in your SQL Table already) */
    LookUp('gci.tbl_client_policy_pii_raw', TextInput1_11.Text in 'Source Client ID','Client Full Name'
),"No Records Found")

but its giving false result

[v-yueyun-msft]

Hi , @Anonymous 

Thanks for your quick response! Sorry for i may misunderstand your need , you can try to use this code :

If(TextInput1.Text<>"" ||TextInput1.Text=Blank(),
If( LookUp(database1,'Client Id'=Value( TextInput1.Text)).Name<>Blank() || LookUp(database2,'Client Id'=Value( TextInput1.Text)).Name<>Blank() ,Coalesce( LookUp(database1,'Client Id'=Value( TextInput1.Text)).Name,LookUp(database2,'Client Id'=Value( TextInput1.Text)).Name),
 "no result"))

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you.

Best Regards,

Yueyun Zhang

[Anonymous]

i am trying to build an app to create as case and  while creating the case  it will check if the record for the customer ever exist in the past. so before creating it will search the Name based on the client id in two database. if any of the record matches in either database it will fetch the client information and poupulate in respective field. 

for example: 

i have a client id as 7. 

no it will look the client id in both the database and if it matches than it will display the cleint name else it will display "No result found" 

[v-yueyun-msft]

Hi , @Anonymous 
Yes, in my above code , it is looking for the Name if contains in the one of your database:

If(TextInput1.Text<>"" ||TextInput1.Text=Blank(),
If( LookUp(database1,'Client Id'=Value( TextInput1.Text)).Name<>Blank() || LookUp(database2,'Client Id'=Value( TextInput1.Text)).Name<>Blank() ,Coalesce( LookUp(database1,'Client Id'=Value( TextInput1.Text)).Name,LookUp(database2,'Client Id'=Value( TextInput1.Text)).Name),
 "no result"))

If I misunderstand what you mean, you can describe your needs and your expected outcomes in detail so that we can better help you.

Best Regards,

Yueyun Zhang

[Anonymous]

its not fetching the date value, error is in yellow highlighted field and it says 

i used the following code and even tried using value option. it says Value "no result cannot be converted to number

If(TextInput1_11.Text<>"" ||TextInput1_11.Text=Blank(),
If( LookUp(CLIENT_INFO,Source_Client_ID= TextInput1_11.Text).Client_DOB<>Blank() || LookUp('gci.tbl_client_policy_pii_raw','Source Client ID'= TextInput1_11.Text).'Date of Birth'<>Blank() ,Coalesce( LookUp(CLIENT_INFO,Source_Client_ID= TextInput1_11.Text).Client_DOB,LookUp('gci.tbl_client_policy_pii_raw','Source Client ID'= TextInput1_11.Text).'Date of Birth'),
 "no result"))

 however if i use the follwing code than there is no error. 

If(TextInput1_11.Text<>"" ||TextInput1_11.Text=Blank(),
If( LookUp(CLIENT_INFO,Source_Client_ID= TextInput1_11.Text).Client_DOB<>Blank() || LookUp('gci.tbl_client_policy_pii_raw','Source Client ID'= TextInput1_11.Text).'Date of Birth'<>Blank() ,Coalesce( LookUp(CLIENT_INFO,Source_Client_ID= TextInput1_11.Text).Client_DOB,LookUp('gci.tbl_client_policy_pii_raw','Source Client ID'= TextInput1_11.Text).'Date of Birth'),
 Value("")))

[v-yueyun-msft]

Hi , @Anonymous 

Can you try to use this code in your side ?

If(TextInput1_11.Text<>"" ||TextInput1_11.Text=Blank(),
If( LookUp(CLIENT_INFO,Source_Client_ID= TextInput1_11.Text).Client_DOB<>Blank() || LookUp('gci.tbl_client_policy_pii_raw','Source Client ID'= TextInput1_11.Text).'Date of Birth'<>Blank() ,Coalesce( LookUp(CLIENT_INFO,Source_Client_ID= TextInput1_11.Text).Client_DOB,LookUp('gci.tbl_client_policy_pii_raw','Source Client ID'= TextInput1_11.Text).'Date of Birth'),
 Blank()))

For this error code , it seems your Clirnt_DOB column is a Date type , so we can not return the different type result in the If() function. 

And what is your ClientID column Type , if it is a number instead of the Text , you need to add the Value():

Value( TextInput1.Text)

Best Regards,

Yueyun Zhang

[Anonymous]

the data type is date for DOB and client id is alphanumeric

[v-yueyun-msft · Accepted solution]

Hi , @Anonymous 

Yes , you can try to use this code :

If( LookUp(CLIENT_INFO,Source_Client_ID= TextInput1_11.Text).Client_DOB<>Blank() || LookUp('gci.tbl_client_policy_pii_raw','Source Client ID'= TextInput1_11.Text).'Date of Birth'<>Blank() ,Coalesce( LookUp(CLIENT_INFO,Source_Client_ID= TextInput1_11.Text).Client_DOB,LookUp('gci.tbl_client_policy_pii_raw','Source Client ID'= TextInput1_11.Text).'Date of Birth'),
 Blank())

Best Regards,

Yueyun Zhang