cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jwi
Level: Powered On

Drop down based on date field

Hi all,

I have an app in which we can add workplace inspections. One of the fields is the 'inspection date'.

I am looking for a solution to add 2 drop down menus: one for the inspection year, one for the inspection month.

both drop down menus are then based on the inspection date field.

 

Knipsel.JPG

I you please inform me how I can do that?

Thanks upfront for your kind assistance!

John

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Drop down based on date field

@jwi 

If you want to combine those 2 code blocks here's how to do it:

 

 

SortByColumns(
Search(
Filter( '[dbo].[WerkplekInspecties]'; If( IsBlank(Dropdown_year); WerkplekInspectieDatum = WerkplekInspectieDatum; Year(WerkplekInspectieDatum) = Dropdown_year );
TextSearchBox1.Text;
"Note"
);
"Note";
If(SortDescending1; Descending; Ascending))
)

 

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

View solution in original post

4 REPLIES 4
Super User
Super User

Re: Drop down based on date field

@jwi 

In the Items property of your month dropdown control input the following code.  I have chosen to leave a blank field in case the user does not wish to apply any filter.

 

[Blank(),1,2,3,4,5,6,7,8,9,10,11,12]

In the Items property of your year dropdown control input the following code.  We want to write a formula to calculate the previous 2 years, current year and next 2 years automatically so the app will not have to be updated.  As of today the result would be [blank, 2017, 2018, 2019, 2020].

 

[Blank(),Year(Now())-2,Year(Now())-1,Year(Now()),Year(Now())+1]

Then to FILTER you should put this code in the Items property of your gallery.

 

Filter(
    your_datasource_name,
    If(
IsBlank(dropdownMonth),
InspectionDate = InspectionDate,
Month(InspectionDate) = dropdownMonth
), If(
IsBlank(dropdownMonth),
InspectionDate = InspectionDate,
Year(InspectionDate) = dropdownYear
) )

 

You might be wondering about the code InspectionDate = InspectionDate.  I did this to create a condition where all workplace inspections will show in the gallery when a dropdown in blank.  The inspection date of the current record will always be equal to itself.

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

jwi
Level: Powered On

Re: Drop down based on date field

@mdevaney 

 

Thanks a lot for your reply.

I decided only to use the year dropdown for now. So far so good.

 

However, in the items property of my gallery I have to combine two codes.

Can you give me some guidance to do that?

 

Thanks!

John

 

Filter(
    '[dbo].[WerkplekInspecties]';
    If(
        IsBlank(Dropdown_year);
        WerkplekInspectieDatum = WerkplekInspectieDatum;
        Year(WerkplekInspectieDatum) = Dropdown_year
 
SortByColumns(Search([@'[dbo].[WerkplekInspecties]']; TextSearchBox1.Text; "Note"); "Note"; If(SortDescending1; Descending; Ascending))
Super User
Super User

Re: Drop down based on date field

@jwi 

If you want to combine those 2 code blocks here's how to do it:

 

 

SortByColumns(
Search(
Filter( '[dbo].[WerkplekInspecties]'; If( IsBlank(Dropdown_year); WerkplekInspectieDatum = WerkplekInspectieDatum; Year(WerkplekInspectieDatum) = Dropdown_year );
TextSearchBox1.Text;
"Note"
);
"Note";
If(SortDescending1; Descending; Ascending))
)

 

 

---
Please click "Accept as Solution" if my response helped to solve your issue so that others may find it more quickly. If your thought the post was helpful please give it a "Thumbs Up."

View solution in original post

Highlighted
jwi
Level: Powered On

Re: Drop down based on date field

thanks a lot, this works!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (8,389)