Unanswered

Get text value of lookup column instead of GUID

(1) Share

Report

Posted on by Community Power Pla...

I have a flow that is taking newly created records from a Dataverse for Teams table, formatting the fields in html and ultimately converting to a pdf. A number of columns in the table are lookups that point to a sister table that contains a list of employees to aid with selection in the canvas app the records are created in. When these lookup fields are mapped in the html using [column name](value) they return the GUID instead of the text value that exists in those records. Is there a way to get Power Automate to read the names that are on the table instead of responding with their GUID?

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (0)

DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @Anonymous

Ultimately you want to look at the history of your when a row is triggered action and look at the output to see if the data you are looking for is included in there. If not you will probably have to do a get row from another table in the dataverse in order to retrieve the value you are looking for based on the GUID. Hopefully though, it's in the output and all you need to do is construct a valid expression. Feel free to copy the JSON output from the trigger here and we can help if need be.

That aside, I note you are using a premium connector. It's possible to create a PDF using the OneDrive connector and convert file action.

I've covered this here as word to PDF but I note that @RezaDorrani has covered HTML to PDF this week here. HTML to PDF is a pain using the included actions to be honest but depending on how complex your conversion is, cost vs ease might play a part to your solution design. It is also possible to convert Excel to PDF. I am planning to do a video on that soon if you think that might be of use.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Was this reply helpful? Yes No

Community Power Pla... on at

Like (0)

Report

Hello! @DamoBird365

That makes a lot more sense! This is the output from the trigger. The table is a large form with a large number of columns. I wanted to make sure the lookup fields would show up correctly so I only tested the 3.

{
 "cr17f_eq30x42sheetsize": false,
 "cr17f_olverifypowerdistrsubmittalinfo": false,
 "cr17f_ohlconduitconduitracks": false,
 "cr17f_eqdimensionaldrawingsfromwallsgrids": true,
 "_cr17f_vdcmember1_value": "837b648a-a7a7-eb11-b1ac-000d3a5c455b",
 "_cr17f_vdcmember1_type": "cr17f_faithemployeeses",
 "cr17f_ohpmccable": false,
 "cr17f_ohlmccable": true,
 "cr17f_oltrackingsheetforcondrunsandwire": false,
 "cr17f_dpelectrical": false,
 "cr17f_swsystems": false,
 "cr17f_rtspostanchors": false,
 "cr17f_olvendorreleasedapprovedsubmittals": false,
 "cr17f_mc85x11sheetsize": false,
 "cr17f_gcbimimplementationplanreceived": false,
 "cr17f_eqexcelleratevslapproval": false,
 "cr17f_mctrimbleaplusedforlayout": false,
 "cr17f_rtspreanchors": false,
 "cr17f_rtspanelcorners": false,
 "cr17f_mcfinishtrimpackagesneeded": false,
 "cr17f_ohp17x22sheetsize": true,
 "cr17f_mcdatajacks": false,
 "cr17f_dpsizes": false,
 "cr17f_ohl24x36sheetsize": false,
 "_modifiedby_value": "3a69819c-d19b-eb11-b1ac-002248086272",
 "_modifiedby_type": "systemusers",
 "cr17f_ugtrackingsheetforcondrunsandwire": false,
 "cr17f_rtslightinglayouts": false,
 "cr17f_ug30x42sheetsize": false,
 "cr17f_ugfeederschedulefromconstdrwused": false,
 "cr17f_rtsugconduitpaths": false,
 "createdon": "2021-05-05T15:43:38Z",
 "cr17f_mc11x17sheetsize": false,
 "cr17f_ohppullschedule": false,
 "cr17f_dpengineereddrawingsneededbeforeinstall": false,
 "cr17f_ohpspeedpulllist": false,
 "cr17f_rtsembeddedsleeves": false,
 "cr17f_ohl11x17sheetsize": false,
 "statecode": 0,
 "_statecode_label": "Active",
 "_owningbusinessunit_value": "aa431772-cf99-eb11-b1ac-000d3a3614d4",
 "_owningbusinessunit_type": "businessunits",
 "cr17f_rts3dscanningforproject": false,
 "cr17f_dpdimensionaldrawingsfromwallsgrids": false,
 "cr17f_drawingcompletionstatus": "Lorem ipsum",
 "_ownerid_value": "3a69819c-d19b-eb11-b1ac-002248086272",
 "_ownerid_type": "systemusers",
 "cr17f_eq11x17sheetsize": false,
 "cr17f_eqinstaldrawingsforlayout": false,
 "cr17f_mc30x42sheetsize": false,
 "cr17f_mcdimensionaldrawingsfromwallsgrids": true,
 "cr17f_ohlpullschedule": false,
 "cr17f_ohltrimbleaplusedforlayout": false,
 "cr17f_dpcabletray": false,
 "cr17f_dpinslabfloorboxes": false,
 "cr17f_mclevel": false,
 "_createdby_value": "3a69819c-d19b-eb11-b1ac-002248086272",
 "_createdby_type": "systemusers",
 "cr17f_projectname": "Power Automate 3",
 "cr17f_ohlequipmentloads": false,
 "cr17f_rtslocationforothsubcontractors": false,
 "cr17f_ohp85x11sheetsize": false,
 "cr17f_ug85x11sheetsize": false,
 "cr17f_ohpequipmentloads": false,
 "cr17f_olvendorchosenforequipment": false,
 "cr17f_ohldimensionaldrawingsfromwallsgrids": false,
 "cr17f_dp17x22sheetsize": true,
 "cr17f_eq24x26sheetsize": false,
 "cr17f_dp30x42sheetsize": false,
 "cr17f_rtstrapezehangers": false,
 "cr17f_olusingfeederschedfromconstdrawings": false,
 "cr17f_dp24x36sheetsize": true,
 "cr17f_dpwindowboxouts": true,
 "cr17f_rtsplywoodtemplates": false,
 "cr17f_rtsugvaults": false,
 "cr17f_eqpanelbuilds": false,
 "cr17f_rtsitneriorgearwindow": false,
 "cr17f_rtsductbanktrenches": false,
 "cr17f_ohpbluebeammarkup": true,
 "cr17f_eqswitchgearbuilds": false,
 "cr17f_mcroomnumbers": false,
 "cr17f_temptrimbleusedfortemplayout": false,
 "cr17f_bimcoordination": false,
 "cr17f_dpsystems": false,
 "cr17f_mc24x36sheetsize": false,
 "cr17f_eq17x22sheetsize": true,
 "_cr17f_foremansuperintendent1_value": "847b648a-a7a7-eb11-b1ac-000d3a5c455b",
 "_cr17f_foremansuperintendent1_type": "cr17f_faithemployeeses",
 "cr17f_olalterationsforpowerdistroneline": false,
 "cr17f_excellerateopportunities": "Lorem ipsum",
 "cr17f_ug24x36sheetsize": false,
 "cr17f_ohpconduitconduitracks": true,
 "_cr17f_groupleader_value": "877b648a-a7a7-eb11-b1ac-000d3a5c455b",
 "_cr17f_groupleader_type": "cr17f_faithemployeeses",
 "cr17f_ohl17x22sheetsize": false,
 "cr17f_ug11x17sheetsize": false,
 "cr17f_eq85x11sheetsize": false,
 "cr17f_mccoverplates": false,
 "cr17f_dppolethrufloorboxes": false,
 "cr17f_ohl30x42sheetsize": false,
 "cr17f_rts3dscanusedwithcoodmodel": false,
 "cr17f_mccolor": false,
 "cr17f_ohl85x11sheetsize": false,
 "_cr17f_constdrawingsupdater_value": "9b7b648a-a7a7-eb11-b1ac-000d3a5c455b",
 "_cr17f_constdrawingsupdater_type": "cr17f_faithemployeeses",
 "cr17f_mcdevicetypes": true,
 "cr17f_rtsfloorboxes": false,
 "cr17f_rtsquaziteboxes": false,
 "cr17f_ohptrimbleaplusedforlayout": false,
 "cr17f_ugtrimbleaplusedforlayout": false,
 "cr17f_rtsexteriorgearwindow": false,
 "cr17f_rtssitelighting": false,
 "cr17f_dpinslabconduit": false,
 "cr17f_rtstotalstationforasbuilts": false,
 "cr17f_swelectrical": false,
 "cr17f_ohpdimensionaldrawingsfromwallsgrids": false,
 "cr17f_buyamericanact": false,
 "cr17f_ohp24x36sheetsize": false,
 "cr17f_rtsgeneratorspad": false,
 "cr17f_mc17x22sheetsize": false,
 "cr17f_mcarea": false,
 "cr17f_dptrimbleaplusedforlayout": false,
 "cr17f_rtsroomreferencepoints": false,
 "cr17f_dpbusduct": false,
 "cr17f_dpconduitsleeves": false,
 "cr17f_eqapltrimbleuserforlayout": false,
 "cr17f_rtsembeddedstrut": false,
 "statuscode": 1,
 "_statuscode_label": "Active",
 "cr17f_ohlspeedpulllist": false,
 "cr17f_vdckickoffdataid": "1c4d45a8-b8ad-eb11-8236-000d3a5a7f13",
 "cr17f_ug17x22sheetsize": false,
 "cr17f_dpanchorpoints": false,
 "cr17f_tempneedfortemppllayout": false,
 "cr17f_rtspokethroughboxes": false,
 "cr17f_ohp11x17sheetsize": false,
 "cr17f_dp85x11sheetsize": false,
 "cr17f_swother": "ngjnfn",
 "cr17f_dp11x17sheetsize": false,
 "cr17f_ohlbluebeammarkup": false,
 "modifiedon": "2021-05-05T15:43:38Z",
 "cr17f_mctypicalroomlayouts": false,
 "cr17f_mclabels": false,
 "cr17f_tempinstalldrawingsneededfortemplo": false,
 "cr17f_ugdimensionaldrawingsfromwallsgrids": false,
 "cr17f_dpgearwindows": false,
 "cr17f_eqforemansiapproval": false,
 "cr17f_ohp30x42sheetsize": false,
 "ItemInternalId": "1c4d45a8-b8ad-eb11-8236-000d3a5a7f13",
 "SdkMessage": "Create",
 "RunAsSystemUserId": "3a69819c-d19b-eb11-b1ac-002248086272",
 "RowVersion": "304984"
}

Was this reply helpful? Yes No

DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @Anonymous

Are these fields all based on people? Do you have access to AAD (Azure AD)? Could this be their Azure AD User Object ID GUID in which case you could use the Get User Action?

Alternatively, I have to admit I am not 100% familiar with the Dataverse but I would imagine that you have a relationship established between your tables in dataverse and this guid is referred to in a column in another table.

If you navigate to Data->Tables in PowerApps/Automate, you can select the table in question and explore the relationship in order to establish where the fields you are looking for come from.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
15-20 of these fields are name lookups. The rest consists of text, dropdowns and Yes/No. I also do not have access to Azure AD. Would the following steps work?

For each lookup field data card:
Hide the input field tied the to form table
Place an input field that is tied to the Employee table
Set the form table input field (Name lookup columns will be replaced with new name columns created with text only entries) as [employee table input field].text so that it's only pulling the text value upon submission.

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Hi @Anonymous

I get what you’re saying and you could test on 1 lookup as a proof of concept but I don’t know if there is an easier way to do it. Wish I did.

give it a go and let us know how you get on.

if anyone else with know how on Dataverse lookups is watching, feel free to jump in.

Damien

Was this reply helpful? Yes No
DogWithRedSocks 63 on at

Like (0)

Report
I would do it like this (a work-around):

Create 3 string variables for your two managers and the dashboard approval
For these entries, store the lookup value (the GUID) in the respective variable
Add 3 Get items actions to your flow for every lookup table and filter by the respective variable
In your Convert HTML to PDF action, add the name (or the information you want) from the 3 Get items actions

Best regards,

DogWithRedSocks 🙂

Was this reply helpful? Yes No