How would I use a value (persons name) that is in a view forms field from a SQL source to lookup email address of that person in office365users

[aalexand]

Currently I have a view form that is bringing in data from on Prem SQL.  One of the fields in this form is bringing in the contract_administrator from the table.  With this information, I want to take that name, look it up in the Office365Users connection and return that persons email address.  

The use case for this if it helps:  We want the sales rep to be able to easily click an email link that will enable them to submit an email to the contract administrator if anything is incorrect with the data that is stored.  

Below is what I have been trying, not sure if I am on the right track or not. 

SQL Database: IDM.ETL_PricingFlatFile_Unpivot
Gallery Name: galleryDrugs
Contract Administrator: contract_administrator
Form: contract_administrator_DataCard2

          DataCardKey30
          DataCardValue30

If(

     !IsBlank(galleryDrugs.Selected.contract_administrator),

     LookUp(

          Office365Users.SearchUserV2( {searchTerm: Text(galleryDrugs.Selected.contract_administrator)}

          ),

          true

     ).Mail,

     ""

)

[DJ_Jamba]

Are you certain the field inside Contract Manager Card is a text field and not a drop down/combo displaying in View mode?

You may already have the email address

[aalexand]

Yes, the file that it is pulling in is a flat file with a column for contract administrator.  The column is a nvarchar(25) data type.  

[DJ_Jamba · Accepted solution]

Could you try:

If(
    !IsBlank(galleryDrugs.Selected.contract_administrator),
    With(
        {
            varLookup: Office365Users.SearchUserV2({searchTerm: galleryDrugs.Selected.contract_administrator, top:999})
        },
        If(
            CountRows(varLookup.value) > 0,
            First(varLookup.value).Mail
        )
    )
)

[aalexand]

Amazing!  Worked perfectly!  It is a lot to ask more of you but I am really trying and interested in learning.  If you do have the time and can explain what you did that I was missing, and like what this code is doing I would truly appreciate it.  Thank you so much for the help!

[DJ_Jamba]

The Office365Users conector returns its results in a table called value.

So you cannot directly access the Mail property from the results because a) it's a table b) each row in the table has a record

So First(Office365 Connector Results) - gives us one record. No different to First(col_Customers) for a collection.
The once we have a record we can access the properties i.e. Mail

The code could be better though because it's risky performing a lookup with Display Name. There could be more than one John Smith etc..

A better way would be something like this:

If(
    !IsBlank(galleryDrugs.Selected.contract_administrator),
    With(
        {
            varLookup: Office365Users.SearchUserV2({searchTerm: galleryDrugs.Selected.contract_administrator, top:999})
        },
        If(
            CountRows(varLookup.value) = 0, // no match found
            Notify("No email found for selected person",NotificationType.Error,0),
            CountRows(varLookup.value) = 1, // 1 match found
            First(varLookup.value).Mail,
            Notify("Caution! Mutliple records found for selected person");
            Concat(FirstN(varLookup.value, 5),Mail,Char(10)) //multiple matches found but we will display the first 5 in the label (set the label to auto height)
        )
    )
)

[aalexand]

Thank you so much for all the help!

[DJ_Jamba]

You're welcome!
d(-_-)b