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

ClearCollect and Filter Interaction

My data sources are two CDS Tables:Work_Order_Articles and Articles.

In the Work_Order_Articles table, there is a Article field which is a lookup field to the field Article in the Articles Table.
Of course, the Articles items have a property called Name, which is exactly what it sounds like.

What I am trying to do is to create a gallery control that uses a collection as a data source and that displays the articles in a certain work order.
Therefore, I have thought aboyt two solutions, that should IN THEORY, give me the same result, but for some reason I can't make one of the work.

Please note that in the Work_Order_Articles table, both the article and the Work_Order are lookup fields, since I tried to maintain a header/detail pattern for the relationships.

Solution A:
I set the "OnVisible" property of my page as :
ClearCollect(work_order_articles_collect;Work_Order_Articles)
Then, I create a gallery control whose "Items" property is:
Filter(work_order_articles_collect;Work_Order_ID=some_work_order)
This works (see first attachement, with the gallery showing two items), however, the ClearCollect is not delegable and therefore I could have problems when used on large data sets.


 

Solution B:
I set the "OnVisible" property of my page as :
ClearCollect(work_order_articles_collect; Filter(Work_Order_Articles;Work_Order_ID=some_work_order)
Then, I create a gallery control whose "Items" property is:
work_order_articles_collect
This, however, does not work. In fact, when I try to show the article name (ThisItem.Article.ID) I get a blank field. (See second picture)

Is there something that I am missing when using the filter function inside a ClearCollect?

Edit: As @mdevaney pointed out, I made a mistake while writing my post.
In my code for solution B, I don't set the "Items" property of the gallery to Filter(work_order_articles_collect) but to work_order_articles_collect

 
 





 
1 ACCEPTED SOLUTION

Accepted Solutions

So, I managed to find a solution.

What I did was the following:

ClearCollect(
    cc_work_order_articles;
    ShowColumns(
        Filter(
            work_order_articles;
            work_order.ID = some_work_order
        );
        "cref0_sg_work_order";
        "cref0_sg_article_id";
        "cref0_article_qt"
    )
)

This way my output is exactly what I wanted, even though I am not sure why.

One thing I would like to have some confirmation about is this:

As far As I know, ShowColumns is not delegable.
However, since Filter is delegable to my data source (CDS), as long as the output of the filter is smaller then 2000 rows, I will not encounter any problems, right?


View solution in original post

5 REPLIES 5
mdevaney
Super User
Super User

@nicola94
In solution B you are using this code in the Gallery Items property:

Filter(work_order_articles_collect)

Instead I think you should just use the collection by itself in the Items property since it was already filtered

work_order_articles_collect

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.

Hello @mdevaney .
You are right, I made a mistake in my post on the forum.
However, my code is as you say: without the filter().
Therefore, this is can't be the solution

@nicola94
If you take a look in the Collections viewer does the article Id show as blank? Collections viewer can be found in the main menu or on the View tab. I want to know what the underlying collection looks like so we can rule out a problem with the gallery.

Please send a screenshot of the collections viewer for both methods.

So, I managed to find a solution.

What I did was the following:

ClearCollect(
    cc_work_order_articles;
    ShowColumns(
        Filter(
            work_order_articles;
            work_order.ID = some_work_order
        );
        "cref0_sg_work_order";
        "cref0_sg_article_id";
        "cref0_article_qt"
    )
)

This way my output is exactly what I wanted, even though I am not sure why.

One thing I would like to have some confirmation about is this:

As far As I know, ShowColumns is not delegable.
However, since Filter is delegable to my data source (CDS), as long as the output of the filter is smaller then 2000 rows, I will not encounter any problems, right?


@nicola94 

SHOWCOLUMNS works a little bit differently than many other formulas with regards to delegation.  Its output is limited to 2,000 rows rather than its input. Seeing that you've put the formula inside a collection which also has a 2,000 row limit will be OK so long as the result set is 2,000 rows or less.

 

I am not a CDS expert but it appears WorkOrderID is not a simple number type column.  Its a complex field of some type.  Maybe you can tell me which type by looking at the column definition.  I also see this in SharePoint for LookUp columns and People columns.

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,602)