You’re offline. This is a read only version of the page.
Hi all, I'm trying to create a flow to enable a school to import data from Excel into Dataverse.
I'm stuck on Lookup fields. For example I have a GroupType table referenced by a Group table.
My approach
I import the data from the groupTypes Excel file into Dataverse which generates the UIDs.
I then read data from dataverse into an array comprising groupTypeDescription and grouptypeid.
Challenge
When I import Groups I want to be able to pass the groupTypeDescription from the groups spreadsheet column and return the grouptypeid from the array and then use that to populate the lookup field on the groups Dataverse table.
The 'variables()' expression looks a likely candidate but I can't seem to crack it.
There are similar posts on here but I can't find the answer.
Can anyone offer any advice please?
Many thanks,
Jim
Hi @Eric I'm happy with the List Rows solution referencing the output as first(...) which is working well
Since then, I do now have the /xxx_odatapluraltablename(xxx_alternatekeyname='SearchText') approach working in other solutions.
One problem is that the prefix the system uses can sometimes differ from the 'Plural Display Name' displayed when you create or edit a table and exposed in odata. For example 'Plural Display Name: xxx_groups', actual plural name 'xxx_groupses' !
My solution To retrieve the right name every time I create a List Rows action on the table in question, run it, and then look at the odata string in the raw inputs 😉
thanks again for your excellent steer here, Jim 👍
Not sure if you solved it finally, but the other thing could be the case on the key name (ie hdc_keygrouptype)?
Hi, good spot but I tried with a / before and same error unfortunatley
The regOccurrence value that uses list rows also does not include a / and works so I don't think it's required Eric
I think you're missing a "/" before hdc_reggrouptypes
A great many thanks CNT and EricRenier for your help here. I'm up and running with a BUT 😉
I had previously used dataflows but wanted more control as you identified.
List rows and an expression referring to the output was a solution BUT I'd love to implement the alternate keys approach which seems cleaner and more concise however I just can't get this to work.
The flow returns Error in query syntax. Any idea what I'm doing wrong below?
The lookup table plural name is hdc_reggrouptypes, the key is hdc_keyGroupType. I've tried all lowercase also -
FYI, I used the list rows approach to retrieve the regOccurrence key above 🙂
Cheers,
Jim
Hi @Anonymous,
You don't need to populate an array of group description and group type. Instead to simplify the flow, use the "List Rows" action to get the required group ID and then use the group ID directly in your "Add a row" action.
Remember to bind a lookup field that format is: /PluralPhysicalTableName(guid). Example:
Share
Report
All responses (
Answers (
/cr23s_grouptypes(98bd54a1-2c1b-4e5a-bbb5-2b7b292d5119)Another trick as well is with alternate keys. Instead of retrieving the group ID, if the description is unique, set an alt key on it and then just set the lookup. Example:
/cr23s_grouptypes(cr23s_alternatekeyname='descriptionfromexcel')
Dataflows is another good option as stated by @CNT if you need to run under a regular interval, but Power Automate is also a viable option where you have more control and can get a more performant process.
Hope this helps!
#1
stampcoin
17
#2
mmbr1606
15
Super User 2025 Season 1
#3
ankit_singhal
11
Super User 2025 Season 1
