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

Filter list items by termstore hierarchy

I have a list with a termstore column. The particular termstore is clearly organized by hierarchy. Given a particular term (either label or guid) I need to filter the items in the list to find items that are descendants (or children - a single level would suffice) of that term.

 

So, given this hierarchy (sorry for the poor presentation - this editor apparently doesn't do well with multi-level lists):

TOP

  • A
  • A1
  • A1-1
  • A1-2
  • A2
  • A2-1
  • A2-2
  • B
  • B1
  • B1-1
  • B1-2
  • B2
  • B2-1
  • B2-2

Given the term A I want to be able to filter the list to just items where the termstore column contains either (direct children: A1, A2) or (all descendants: A1, A1-1, A1-2, A2, A2-1, A2-2). Given the term A1 I want to be able to filter the list to just items where the termstore column contains (direct children: A1-1, A1-2) or (all descendants: same). Given the term TOP I want to be able to filter the list to just items where the termstore column contains (direct children: A, B) or (all descendants: all items).

 

Note that EITHER the "direct children" OR the "all descendants" result would be equally satisfactory for my requirements.

 

The actual termstore hierarchy is fairly long (several hundred items if the top level were chosen) and so solutions containing "or" in the odata filter are going to run up against length limitations.

 

Performance matters in this case because the flow is triggered by http call from a ui page in js and the user is waiting on this response to populate a select list. Waiting 10-15 seconds (or even 30-40) is somewhat acceptable, but waiting 5-10 minutes won't cut it.

 

Attempted solutions rejected (so far):

  • Loading the whole termstore hierarchy into memory and looping through using the guidPath to create a dynamic odata filter both takes too long [6-9 minutes] and runs into the length limitations mentioned above.
  • I can't just "change the problem" by moving it out of flow to js because of permissions on the list - it has to run as another user (the flow owner) to have access to the items in the list.

It seems like this would be a simple application of the operator "StartsWith" on the guidPath property of the termstore column, but I can't figure out the odata syntax to make use of the guidPath property.

2 REPLIES 2
fchopo
Super User
Super User

Hi @plb 

Have a look at this thread: Managed Metadata column is supported in OData Filt... - Power Platform Community (microsoft.com) Although I'm not sure using CAML query gets all "descendant" items also. Apart from that, it won't work with multi-value MMS.

Anyway, hope it helps!

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
plb
Frequent Visitor

@fchopo thank you for your engagement. I believe you have given me the correct answer "it won't work".

 

I won't mark this as a solution, despite its correctness, because I don't want to give others the impression that my problem was solved.

Helpful resources

Announcements
MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power automate tips 768x460 v2.png

Restore a Deleted Flow

Did you know that you could restore a deleted flow? Check out this helpful article.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Users online (2,440)