Filter Collection Only Works in Preview

[alistairhunt]

I have the following formula to construct a collection using CDS as the datasource. The Checklist entity has a 1:N relationship with the ChecklistDetail entity

ClearCollect(
    colCheckListDetails,
    First(
        Filter(
            Checklists,
            Vehicle.'Identification Number' = VehicleType.Selected.'Identification Number' And ('Status Reason' = 'Status Reason (Checklists)'.New Or 'Status Reason' = 'Status Reason (Checklists)'.'In Progress')
        )
    ).'Checklist Details'
);

This collection contains 135 items which is filtered further based on an OptionSet value. These filtered items are set as the Items property for my Gallery.

Filter(colCheckListDetails, 'Equipment Location' = 'Equipment Location Options'.'Vehicle')

This filtered collection works fine when the page content is displayed in Preview mode but not when published. I have used the Monitor to confirm that the collection is returning the 135 rows but I am not able to see the filtered content using the OptionSet filter.

[v-bofeng-msft]

Hi @alistairhunt :

Could you tell me:

• "ClearCollect(colCheckListDetails,...)" is placed in which property of which control? Is this Event executed when you run the App?
• What is 'Equipment Location'?What is it's data type?
• What is  'Equipment Location Options'?What is 'Vehicle'?What is it's data type?

I suggest you set this gallery's items properyt to

colCheckListDetails 

This can help you check whether there are records in colCheckListDetails when you run the application after publishing it.

Best Regards,

Bof

[alistairhunt]

Hi Bof,

Many thanks for the reply. In answer to your questions;

1. ClearCollect is placed in the OnVisisble event for the Screen containing the gallery and as far as I can tell, when using the monitor, the event runs.
2. 'Equipment Location' is an OptionSet field within the CDS Checklist Details entity.
3. 'Equipment Location Options' is the Global OptionSet name in my CDS solution. 'Vehicle' is one of the OptionSet values.
4. I can confirm that when there is no filtering on the gallery items, the content of the gallery is displayed correctly when published. The problem seems to be with the OptionSet in the Filter for the Gallery items.

Regards

Alistair

[v-bofeng-msft]

Hi @alistairhunt :

   I’m not sure how you call "Global OptionSet" directly in the canvas app, I have never tried this before (I did a test just now but no luck).Could you describe "'Equipment Location Options'.'Vehicle'" more specifically? (How did you set this OptionSet as a data source?)

   Since option set is a special data type, I generally use a combo box to filter.For Example:

   1\Add a combo box(ComboBox1), set the combo box's Items:    

Choices('Checklist Details'.'Equipment Location')

   2\Set the gallery's Items:

Filter(colCheckListDetails, 'Equipment Location' = ComboBox1.Selected.Value)

Best Regards,

Bof

[alistairhunt]

Hi Bof,

OptionSet information is now included with the CDS attributes. The screenshot below shows the availability of the OptionSet values in the auto-complete for the Equipment Location Options attribute

I have also found that turning on one of the Experimental Features, Formula-level error management, seems to resolve this issue (but I do need to do some more investigation)

[v-bofeng-msft]

Hi @alistairhunt :

   The wave under the "=" operator usually indicates that the data types of the values on both sides of the equation do not match.Usually I would suggest to solve this problem from the perspective of data type to improve the robustness of the app.

   In addition,If you find a better way, please consider marking it as a solution. Thank you for your contribution to this.

Best Regards,

Bof