cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
golodias
Frequent Visitor

Hierarchical lookup table Value must be a data entity record

I have a table let to register on going projects (projectTable) with columns level1, level2 and level3 which are lookup columns to another table CategoryTable. The CategoryTable is hierachical table which means if level2 depends on level1 and level3 on level2. For example, if level one is Car, level2 would be tire, wheels etc and level3 further subdivision.

 

I managed to successfully show the option is combo box in projectTable form. The problem is there are duplicate values in the choices in level1 and when I use distinct to show unique only, I can't patch or use the form to submit to the table from the form. Obviously the duplicates in level1 are not multiple entry but repeat of level1 for each row in level2. When I use distinct I get Value must be a data entity record error. 

 

What I want is in the form of projectTable, dropdown/combo box of unique level1, once user selected level1, only corresponding level2 to show up in the level2 combo box, same for level3. Any suggestions would  be greatly appreciated. 

golodias_0-1656424573251.png

 

9 REPLIES 9
dpoggemann
Super User
Super User

HI @golodias ,

 

Can you share your table structure for the Category table?  When you mentioned hierarchical I was thinking you would not have duplicates because you would have a table like the following:

Category

Name

Parent (lookup to same Category table)

 

Values like the following:

Name            Parent 

Car                NULL

Engine           Car

Spark Plug     Engine

Timing Belt    Engine

Tire                 Car

 

Is it something like this or a different model?

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

For example level1 as lookup column in the projectTable appears as car,car,car,car (Same number of times it is mentioned in row). The same goes for level2 and 3. If I use Distinct to show only unique ones then I get error that value must be data entry record

 

golodias_0-1656486245737.png

 

 

dpoggemann
Super User
Super User

Hi @golodias ,

 

So your category table has the 3 columns for Level 1, level 2, level 3?  Why not structure as I mentioned with the category / parent?  This will remove the lookup having duplicate values.  You could also do 3 different category tables (category, Subcategory, SubSubCategory) with lookups to their parent tables on each record.  Then your through lookup values on your project table would point at these tables and show only the values 1 time as well. 

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

I tried that but then I couldn't filter in the subcategory based on the selection in category. For example if I select car in lelevl1 I just want to see car related in level3 and the same goes for level3. This is example table, the real category table is quite long, unless I filter it based on previously selected cat, it would be impossible to pick the right subcategory. 

dpoggemann
Super User
Super User

Are you doing this in a model or canvas app?

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew
dpoggemann
Super User
Super User

and sorry, which of the two options did you try that I provided?  1 table with parent or multiple tables?

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

Canvas app but the tables are in dataverse

One table with parent, I am working creating three tables based on each column (level1,level2 and level3) with level2 table lookup column to level1 table, and level3 table lookup column with level2 table. Then on Project table I create lookup to each table. 

dpoggemann
Super User
Super User

Hi @golodias , the three tables will for sure work.  Hopefully goes well.

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Users online (4,429)