ClearCollect and Filter Interaction

[nicola94]

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

[mdevaney]

@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.

[nicola94]

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

[mdevaney]

@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.

[nicola94 · Accepted solution]

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?

[mdevaney]

@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."