How to use a string found in an email subject to search a data table with

Hi,
I’m relatively new. I’m working on a bot that will read email subjects and if a specific term is found, then the bot needs to look up the term from a pre-populated data table, and output a string from a different field in the same row within that data table.

The data table contains 4 columns, all of which contain distinct strings, and cannot be null. The email ingestion sequence is already completed. I am using if/then’s and 'mailMessage.Subject.Contains(“”) to identify the term in the subject. Only 1 of the columns will be used for the output strings, but the other 3 columns can be used to lookup with.

To put this as a scenario: An email subject can contain any of the three terms “Sky”, “Blue Jay”, or “Turquoise”. If found, the bot must look up the found string in the data table. It must then output a string containing the word “blue”.

I’m thinking about assigning this subject’s term, if found, to a variable. Then using the variable to Get Row Item, and filtering down to the 4th column. Is this a correct approach? If so what would my assign VB code be?

Thank you for your help, I’m sincerely grateful! If you need me to clarify anything, I’d be happy to provide more specific details.

Hi
Welcome to UiPath community

Fine
Hope these steps would help you resolve this
—hope we have datatable named dt ready for lookup
—use a get outlook mail activity and get the output with a variable named out_mailmessage
—now use a for each loop and pass the above obtained variable as input
And change the type argument as system.net.mail.mailmessage
—inside the loop use a IF condition like this
mail.Subject.ToString.Contains(“Blue Jay”) OR mail.Subject.ToString.Contains(“Sky”) OR mail.Subject.ToString.Contains(“Turquiose”)
If this is true it will go to THEN apart where use a LOOKUP DATATABLE ACTIVITY and mention this in the property panel
Input = IF(mail.Subject.ToString.Contains(“Blue Jay”),”Blue Jay”,If(mail.Subject.ToString.Contains(“Sky”),”Sky”,”Turquiose”))
Datatable = dt
Lookup column name = your lookup column name
Our result = str_output
Target column name = “your target column name which will be the output value”

Kindly try this and let know for any queries or clarification
Cheers @Scott_Clark

Hi @Palaniyappan,

I apologize about the delay in response, and thank you so much for your help. So far everything has worked. If I want to do just 1 ‘if’ inside of the Lookup Column Name activity this works. However, I need to look up several different rows in the data table within the same if activity. So there could be 1011 lookup values, with 337 corresponding output values. Every 3 lookup values are related to each other, but not related to the others. How do I make an IF statement lookup for this?

To put frame this within the above scenario: Blue Jay, Sky, and Turquiose are all used as a lookup to receive an output of Blue. But within the same look up value IF statement, I also need to look up Stop Sign, Fire Truck, Apple which will return Red, and Grass, Leaves, Algae which will return Green. Blue Jay, Sky, Turquiose, Stop Sign, Fire Truck, Apple, Grass, Leaves, Algae are all mailMessage.Subject.Contains(“”) criteria.

Thank you!

May I know which condition you are talking about
This one

Or

Cheers @Scott_Clark

Hi @Palaniyappan

I’m referring to the second, “IF(mail.Subject.ToString.Contains(“Blue Jay”),”Blue Jay”,If(mail.Subject.ToString.Contains(“Sky”),”Sky”,”Turquiose”))”. which is the lookup value within the Lookup Data Table activity.

The IF/Then that is the parent to the look up data table contains up to 1000 other 'Or mail.Subject.ToString.Contains(“Fire Truck”) in it. So I need to be able to look up each of these 1000 inputs within the Lookup Value of the Lookup Data Table activity.

1 Like

Sorry still u have some queries
In this expression which value you mean like will be more to be compared with
And how those values are available that is of which datatype variable so that we can use them here while comparing
Cheers @Scott_Clark

@Palaniyappan

No worries, this is a tricky bot to build and I appreciate the help!

Would you mind clarifying your last comment? I’d be happy to provide any further information on variables, workflow structure, or If/Then hierarchy structure. I’m just not sure what exactly you are looking for. Thanks again!

-Scott

1 Like

Awesome
In this expression Blue Jay, Sky, and Turquiose if we’re value to be compared with then how these values are provided
Like what is the source
Is it coming from any variable and if so what is that variable type
Cheers @Scott_Clark

@Palaniyappan

Sure, so I’m using a Systems.Collections.Generic.List <System.Net.Mail.MailMessage> variable to pass the email. From there, I’m using a ‘for each mail’ with the above variable, and in the body I’m using the parent IF statements that contain ‘If mail.Subject.ToString.Contains(“Sky")’