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.
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:
Parent (lookup to same Category table)
Values like the following:
Spark Plug Engine
Timing Belt Engine
Is it something like this or a different model?
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
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.
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.
and sorry, which of the two options did you try that I provided? 1 table with parent or multiple tables?
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.
Hi @golodias , the three tables will for sure work. Hopefully goes well.
So many events that are happening this month - don't miss out!
Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.
Your chance to join an engaging competition of Power Platform enthusiasts.