cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mirtemir
Helper IV
Helper IV

Filter of search gallery by fieldname and start- and enddate

Hi,

I would like to sort a gallery by the field StartDatum en Approval state Pending.
These Items have to be shown first.

And I have a searchbox for the gallery. T
It has to be possible to search for the field called Aanvrager.
And it would be perfect if it were posible to also filter on StartDate and EndDate.

How can i achieve this?
@v-xida-msft 

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @Mirtemir ,

Do you want to filter your Gallery Items based on a TextSearchBox?

Do you also want to sort your Filtered result based on StartDatum column?

Further, could you please share a bit more about the “Approval state” column? Is it a Choice type column?

 

Firstly, if you want to filter your Gallery Items based on a TextSearchBox, please consider set the Items property of the Gallery to following:

Filter(
      ExpenseMaster;
      Aanvrager.DisplayName in 'Office365-gebruikers'.DirectReportsV2(
                                   'Office365-gebruikers'.ManagerV2('Office365-gebruikers'.MyProfileV2().id).id
                               ).value.displayName,
      SearchTextBox.Text in Aanvrager.DisplayName
)

 

If you want to sort above Filtered result by the StartDatum column, please try the following formula:

Sort(
     Filter(
      ExpenseMaster;
      Aanvrager.DisplayName in 'Office365-gebruikers'.DirectReportsV2(
                                   'Office365-gebruikers'.ManagerV2('Office365-gebruikers'.MyProfileV2().id).id
                               ).value.displayName;
      SearchTextBox.Text in Aanvrager.DisplayName
     );
     StartDatum;
     Descending
)

 

For filtering result based on the StartDate and EndDate, you could add two DatePicker controls in your app, rename them as "StartDatePicker" and "EndDatePicker". Then remove the formula from the DefaultDate property of the two DatePicker controls. Then, modify your formula as below:

Sort(
     Filter(
      ExpenseMaster;
      Aanvrager.DisplayName in 'Office365-gebruikers'.DirectReportsV2(
                                   'Office365-gebruikers'.ManagerV2('Office365-gebruikers'.MyProfileV2().id).id
                               ).value.displayName;
      SearchTextBox.Text in Aanvrager.DisplayName;
      If(
         !IsBlank(StartDatePicker.SelectedDate);
         StartDate = StartDatePicker.SelectedDate;
         true
      ),
      If(
          !IsBlank(EndDatePicker.SelectedDate);
          EndDate = StartDatePicker.SelectedDate;
          true
      )
     );
     StartDatum;
     Descending
)

 

If the “Approval state” column is a Choice type column, and you want these records whose “Approval state” column value is Pending to be shown up first in your Gallery, I afraid that there is no direct way to achieve your needs. As an alternative solution, please check the following workaround:

Set the OnStart property of App to following:

ClearCollect(
             RecordsCollection;
             Filter(ExpenseMaster; 'Approval state'.Value = "Pending");
             Filter(ExpenseMaster; 'Approval state'.Value <> "Pending")
)

Then set the Items property of the Gallery to following:

Sort(
     Filter(
           RecordsCollection;     // replace here with collection table
           Aanvrager.DisplayName in 'Office365-gebruikers'.DirectReportsV2(
                                   'Office365-gebruikers'.ManagerV2('Office365-gebruikers'.MyProfileV2().id).id
                               ).value.displayName;
           SearchTextBox.Text in Aanvrager.DisplayName;
          If(
            !IsBlank(StartDatePicker.SelectedDate);
            StartDate = StartDatePicker.SelectedDate;
            true
          ),
          If(
             !IsBlank(EndDatePicker.SelectedDate);
             EndDate = StartDatePicker.SelectedDate;
             true
          )
     );
     StartDatum;
     Descending
)

Please re-load your canvas app (fire the OnStart property of App), then check if the above solution is helpful.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-xida-msft
Community Support
Community Support

Hi @Mirtemir ,

Do you want to filter your Gallery Items based on a TextSearchBox?

Do you also want to sort your Filtered result based on StartDatum column?

Further, could you please share a bit more about the “Approval state” column? Is it a Choice type column?

 

Firstly, if you want to filter your Gallery Items based on a TextSearchBox, please consider set the Items property of the Gallery to following:

Filter(
      ExpenseMaster;
      Aanvrager.DisplayName in 'Office365-gebruikers'.DirectReportsV2(
                                   'Office365-gebruikers'.ManagerV2('Office365-gebruikers'.MyProfileV2().id).id
                               ).value.displayName,
      SearchTextBox.Text in Aanvrager.DisplayName
)

 

If you want to sort above Filtered result by the StartDatum column, please try the following formula:

Sort(
     Filter(
      ExpenseMaster;
      Aanvrager.DisplayName in 'Office365-gebruikers'.DirectReportsV2(
                                   'Office365-gebruikers'.ManagerV2('Office365-gebruikers'.MyProfileV2().id).id
                               ).value.displayName;
      SearchTextBox.Text in Aanvrager.DisplayName
     );
     StartDatum;
     Descending
)

 

For filtering result based on the StartDate and EndDate, you could add two DatePicker controls in your app, rename them as "StartDatePicker" and "EndDatePicker". Then remove the formula from the DefaultDate property of the two DatePicker controls. Then, modify your formula as below:

Sort(
     Filter(
      ExpenseMaster;
      Aanvrager.DisplayName in 'Office365-gebruikers'.DirectReportsV2(
                                   'Office365-gebruikers'.ManagerV2('Office365-gebruikers'.MyProfileV2().id).id
                               ).value.displayName;
      SearchTextBox.Text in Aanvrager.DisplayName;
      If(
         !IsBlank(StartDatePicker.SelectedDate);
         StartDate = StartDatePicker.SelectedDate;
         true
      ),
      If(
          !IsBlank(EndDatePicker.SelectedDate);
          EndDate = StartDatePicker.SelectedDate;
          true
      )
     );
     StartDatum;
     Descending
)

 

If the “Approval state” column is a Choice type column, and you want these records whose “Approval state” column value is Pending to be shown up first in your Gallery, I afraid that there is no direct way to achieve your needs. As an alternative solution, please check the following workaround:

Set the OnStart property of App to following:

ClearCollect(
             RecordsCollection;
             Filter(ExpenseMaster; 'Approval state'.Value = "Pending");
             Filter(ExpenseMaster; 'Approval state'.Value <> "Pending")
)

Then set the Items property of the Gallery to following:

Sort(
     Filter(
           RecordsCollection;     // replace here with collection table
           Aanvrager.DisplayName in 'Office365-gebruikers'.DirectReportsV2(
                                   'Office365-gebruikers'.ManagerV2('Office365-gebruikers'.MyProfileV2().id).id
                               ).value.displayName;
           SearchTextBox.Text in Aanvrager.DisplayName;
          If(
            !IsBlank(StartDatePicker.SelectedDate);
            StartDate = StartDatePicker.SelectedDate;
            true
          ),
          If(
             !IsBlank(EndDatePicker.SelectedDate);
             EndDate = StartDatePicker.SelectedDate;
             true
          )
     );
     StartDatum;
     Descending
)

Please re-load your canvas app (fire the OnStart property of App), then check if the above solution is helpful.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks, the code works:

Sort( Filter( ExpenseMaster; Aanvrager.DisplayName in 'Office365-gebruikers'.DirectReportsV2( 'Office365-gebruikers'.ManagerV2('Office365-gebruikers'.MyProfileV2().id).id ).value.displayName; SearchTextBox.Text in Aanvrager.DisplayName ); StartDatum; Descending )

 

I will try the datepicker also but even this is sufficient for now.

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,416)