Add a date filter with Ungroup(Table(Distinct statement on a lookup dropdown field in a gallery

[jmatistic]

I have a SharePoint List-based canvas app, with a gallery where user can view, edit and create new records. The app pulls data from the SharePoint list, "Timesheet Lines," into a collection, which is then displayed in the gallery. There are multiple dependent drop down lookup fields in the form, pulling choices from another SharePoint list for initiatives. I need to filter the values from the Initiatives list to only pull in items that are active, defined as falling between an effective and end date of the initiative. All records in the gallery will be associated with a week (start date and end date), or Sun-Sat.

I want to filter the drop downs to only pull distinct records from the list of Initiatives that are effective during the week that is on the timesheet line record. 

Below is what I have, before adding the filter for active choices. I need to filter based on Effective<=ThisItem.'Start Date' && End>=ThisItem.'End Date'. How do I insert the filter statement in the following code?

Ungroup(

    Table(

        {Value: Blank()},

        {

            Value: Distinct(

                'Initiative List',

                Category

            )

        }

    ),

    "Value"

)

[WarrenBelz]

Hi @jmatistic ,

Combining them should look like this - note I have used a different Table header as Value is also the output of Distinct

Ungroup(
   Table(
      {Data: Blank()},
      {
         Data: 
         Distinct(
            Filter(
               'Initiative List',
               Effective <= ThisItem.'Start Date' && 
               End >= ThisItem.'End Date'
            ),
            Category
         )
      }
   ),
   "Data"
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[jmatistic]

@WarrenBelz I tried this but, while it did limit the choices correctly, it did not preserve existing data in that field. Default is set to ThisItem.Category but when paired with your suggested code it does not work.

[WarrenBelz]

@jmatistic ,

Assuming this is a Combo Box, the DefaultSelectedItems should be

{Value: ThisItem.Category}

although I don’t understand how adding a filter changed the functionality as the output schema is still the same.

[jmatistic]

@WarrenBelz It was a drop down control. I switched it to a combo box and made the changes you suggested. Now it is giving me the existing value but if I want to change the value, the dropdown is completely blank. On new records, I get the full selection of choices, with the effective/end date filters not applied. 

items = 

Ungroup(

    Table(

        {Data: Blank()},

        {

            Data: Distinct(

                Filter(

                    'Initiative List',

                    Effective <= ThisItem.'Start Date' && End >= ThisItem.'End Date'

                ),

                Category

            )

        }

    ),

    "Data"

)

DefaultSelectedItems = 

{Value: ThisItem.Category}

[WarrenBelz]

@jmatistic ,

I do not fully understand the issue as I have no vision of how/where this is being used, although I assume that this is being used inside a Form as you refer to ThisItem. You are correct that it will not work on New records (you only asked how to combine the filters) - probably

Ungroup(
   Table(
      {Data: Blank()},
      {
         Data: 
         Distinct(
            Filter(
               'Initiative List',
               Value(ThisItem.'Start Date') < 1 || 
               Value(ThisItem.'End Date') ||
               (
                  Effective <= ThisItem.'Start Date' && 
                  End >= ThisItem.'End Date'
               )
            ),
            Category
         )
      }
   ),
   "Data"
)

but I am unclear on what you mean by but if I want to change the value, the dropdown is completely blank.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps