Customer Engagement & Dynamics CRM Forum

Expand all | Collapse all

Cloud Flow user lookup issue

  • 1.  Cloud Flow user lookup issue

    TOP CONTRIBUTOR
    Posted Apr 14, 2021 11:25 AM
    Hello -

    I have built a cloud flow using the CDS current environment connector.  My flow triggers from a custom entity when a field value changes and ends with delivering an email containing information about the record from the custom entity and some of it's related records.  I have several steps where I'm using the list records action and then creating an HTM table form the list and I have all of that working fine when I add the outputs to my email.

    My only issue is that, for one HTM table, I want to include a username from a lookup field in a child record.  As you might guess, the table shows the user GUID instead of the user name.  I have tried everything I've found in posts to make this work and I just can't get it to show the user name instead of the GUID.  I have a fetchxml query in my list records action and used the fetchxml builder in xrm toolbox and successfully expended by query to include fields from the users table, but the only way I was able to see the user's fullname field was if I let my HTML table use automatic columns and then I have a lot of extra non-user-friendly columns in my resulting HTML table.

    I've read other posts that make it seem like I should be able to specify the related user table and pull the name from there directly in my HTML custom columns, but I haven't been able to get that to work either.

    Any ideas on how to make this work for real?

    Thanks!
    Jill

    ------------------------------
    Jill Vazquez
    Technical Specialist - CRM
    Bioventus, LLC
    Durham NC
    ------------------------------


  • 2.  RE: Cloud Flow user lookup issue

    GOLD CONTRIBUTOR
    Posted Apr 15, 2021 11:01 AM
    Hi @Jill Vazquez

    I've been able to get this to work with a number of Flows.  The way that I've done it is used the "Get Record" Action against the User table as a step (I think it's called Get Rows now in the Dataverse) and used the GUID from the previous steps to retrieve the User that I wanted.  From there, you should be able to use any unique fields from your retrieved user record into your output table.

    I hope that helps.

    Eric

    ​​

    ------------------------------
    Eric Veneziano
    Sales System Analyst
    Minitab, Inc.
    State College PA
    ------------------------------



  • 3.  RE: Cloud Flow user lookup issue

    TOP CONTRIBUTOR
    Posted Apr 15, 2021 12:01 PM
    Thanks @Eric Veneziano - I did try that as well.  After my get records step which retrieves my related records, I added a get records by ID step to retrieve the user record each of my previously retrieved records and an "apply to each" was automatically created when I did that.  From there, I couldn't figure out how to build the table because in the table's from field​​, I only had the option to use the apply to each step as my source but then none of the fields from my get records steps seemed to be available to put in the table.

    ------------------------------
    Jill Vazquez
    Technical Specialist - CRM
    Bioventus, LLC
    Durham NC
    ------------------------------



  • 4.  RE: Cloud Flow user lookup issue

    TOP CONTRIBUTOR
    Posted Apr 16, 2021 02:23 AM
    I use bw_SystemUserFieldName($select=fullname,firstname,internalemailaddress) in my expand query all the time for systemusers, and get the details back no problem.
    One thing I would suggest is, using variables for the values you want and add a compose after the variable, before using the values (as variables) you want to use - that way you can see the output in the actual flow and use Save and Test to refine.

    ------------------------------
    Donal McCarthy
    Digital Marketing Administrator
    BrightWork
    Galway
    ------------------------------



  • 5.  RE: Cloud Flow user lookup issue

    SILVER CONTRIBUTOR
    Posted Apr 16, 2021 09:45 AM
    Hello Jill

    This post should help:
    https://www.ameyholden.com/articles/expand-query-lookups-power-automate

    Regards

    Deborah

    ------------------------------
    Deborah Pitt
    The Pensions Regulator
    Brighton
    ------------------------------



  • 6.  RE: Cloud Flow user lookup issue

    TOP CONTRIBUTOR
    Posted Apr 16, 2021 10:30 AM
    thanks @Donal McCarthy and @Deborah Pitt for the replies.  I have come across the post you linked and have tried that, but I still must be doing something wrong. because no matter how I try it, my name column is blank.  I see the values in my get records output, but not in the HTML table.

    I'm attaching the screen shot of my Fetch XML query and the HTML table - maybe someone can spot where I went wrong?  The get records step is pulling a list of approval records (new_approval) which are child records of another entity (new_strategicaccount)

    ​​

    ------------------------------
    Jill Vazquez
    Technical Specialist - CRM
    Bioventus, LLC
    Durham NC
    ------------------------------



  • 7.  RE: Cloud Flow user lookup issue

    TOP CONTRIBUTOR
    Posted Apr 16, 2021 10:36 AM
    Try this:
    1. Initialize a string Variable called Full Name.
    2. Do a Set Variable with the Full Name - it should create an Apply to each (you can't initialize a variable inside one of these).
    3. Add a Compose after the Set Variable and put the Full Name variable as the input.


    ------------------------------
    Donal McCarthy
    Digital Marketing Administrator
    BrightWork
    Galway
    ------------------------------



  • 8.  RE: Cloud Flow user lookup issue

    TOP CONTRIBUTOR
    Posted Apr 16, 2021 10:58 AM
      |   view attached
    @Donal McCarthy I have not tried that before, but it didn't work - but maybe I did it wrong


    ------------------------------
    Jill Vazquez
    Technical Specialist - CRM
    Bioventus, LLC
    Durham NC
    ------------------------------



  • 9.  RE: Cloud Flow user lookup issue

    TOP CONTRIBUTOR
    Posted Apr 28, 2021 03:45 AM
    @Jill Vazquez
    Apologies for the late reply - I've been swamped.
    Recently, variables started showing the outcome of the run in the variable, getting rid of the need for using compose with variables.
    If you look in the run of the flow, you might see what is happening.




    ------------------------------
    Donal McCarthy
    Digital Marketing Administrator
    BrightWork
    Galway
    ------------------------------



  • 10.  RE: Cloud Flow user lookup issue

    SILVER CONTRIBUTOR
    Posted Apr 16, 2021 10:31 AM
    This could be a longshot, but I've gotten around similar issues by creating a calculated field on my main entity that I basically hide from users and use only for these types of purposes.  In this example, you could add a calculated field for the user name on your entity and then pull the calculated field in.

    ------------------------------
    Terry Letkeman
    Bespoke Technology Services
    Winnipeg
    ------------------------------



  • 11.  RE: Cloud Flow user lookup issue

    TOP CONTRIBUTOR
    Posted Apr 16, 2021 10:39 AM
    @Terry Letkeman don't think I haven't thought about doing something like that!  This is an older process using only traditional workflows and I used Kaskela's WorkFlow Elements to attach a word template of the approvals to an email.  Another project necessitated a redesign to the process ​and a cloud flow seemed like the way to go since I needed to get related records of related records and everything else is working beautifully!  I have the approvals still generating the old way as my backup but I am determined to solve this!

    ------------------------------
    Jill Vazquez
    Technical Specialist - CRM
    Bioventus, LLC
    Durham NC
    ------------------------------



  • 12.  RE: Cloud Flow user lookup issue

    SILVER CONTRIBUTOR
    Posted Apr 18, 2021 12:25 PM
    Sounds like this was already covered, and you are a extra layer deep in having to reach to a user in a related record, but "List Records" is what I've used.  You mentioned the subsequent Apply to Each that occurs in that List Records can not know that it's only retrieving one record (even when it's set to row count = 1), but I've used it in similar circumstances.  Sounds like you are already a step beyond this, but I just wanted to share in case it helps at all.  I end up with the nested Apply to Each situation by the time I'm actually using the data, and it can be easy to grab an incorrect field, etc.  In this case I'm using List Records because my origin for the user name was a sharepoint list.




    ------------------------------
    Tim Miller
    IT Manager
    R.L. Deppmann
    Novi MI
    ------------------------------



  • 13.  RE: Cloud Flow user lookup issue

    TOP CONTRIBUTOR
    Posted Apr 20, 2021 03:05 PM
    Thanks for the reply @Tim Miller .  When I set up my list records for my approval records and then set up my list records or get row by ID to get the corresponding approved by user, that all seems to work fine but then I have trouble with the HTM table.  If I build the HTML table within the apply to each box, it don't have it available to insert into my email message.  If I add the HTML table after the apply to each step, I don't have the appropriate columns available to add to my table.  I think I might be missing some part of the understanding of how the apply to each thing works.​

    ------------------------------
    Jill Vazquez
    Technical Specialist - CRM
    Bioventus, LLC
    Durham NC
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role