Answered

Using Date and Time column from Sharepoint in PowerApps Dropdown list

(0) Share

Report

Posted on by User88

Currently I have a Date and Time column in a Sharepoint list and I would like to do 2 things with it in PowerApps.

First I would like to create a dropdown list that only shows the year from the dates and in that dropdown I would like for it to default to All when you get to the screen.

Second I would like to create a dropdown list for quarters based on the Date and Time column in Sharepoint, I would prefer to have it coded so that I can set a range equal to a number then display that number in the dropdown list so the end user only has to choose a number from the dropdown list and not set the date range themselves. I would also like this dropdown to default to All so that all items from the list are displayed upon landing on the screen.

Been trying to figure this out for days so any and all help would be greatly appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Verified answer

v-xiaochen-msft on at

Like (1)

Report
Hi @User88 ,

I made a sample for you.

Ungroup([DropColumns(AddColumns(LIST18.TestDate,"Year",Text(Year(TestDate))),"TestDate"),[{Year:"All"}]],"Value")

["All","1","2","3","4"]

If(ComboBox1.Selected.Year="All"&&ComboBox2.Selected.Value="All",LIST18,ComboBox1.Selected.Year="All"&&!IsBlank(ComboBox2.Selected.Value),Filter(LIST18,Month(TestDate)>=((Value(ComboBox2.Selected.Value)-1)*3+1)&&Month(TestDate)<=((Value(ComboBox2.Selected.Value)-1)*3+3)),!IsBlank(ComboBox1.Selected.Year)&&ComboBox2.Selected.Value="All",Filter(LIST18,Year(TestDate)=Value(ComboBox1.Selected.Year)),!IsBlank(ComboBox1.Selected.Year)&&!IsBlank(ComboBox2.Selected.Value),Filter(LIST18,Month(TestDate)>=((Value(ComboBox2.Selected.Value)-1)*3+1)&&Month(TestDate)<=((Value(ComboBox2.Selected.Value)-1)*3+3)&&Year(TestDate)=Value(ComboBox1.Selected.Year)))

Best Regards,

Wearsky

Was this reply helpful? Yes No
User88 20 on at

Like (0)

Report

Hi @v-xiaochen-msft
That worked really well, is there anyway that we could make it so that each year in combobox1 only shows up 1 time in the list? I'll have 30-40 different items in the list that might have the same end year.

Was this reply helpful? Yes No
v-xiaochen-msft on at

Like (1)

Report
Hi @User88 ,

Please try this in first combo box's items property

RenameColumns(Distinct(Ungroup([DropColumns(AddColumns(LIST18.TestDate,"Year",Text(Year(TestDate))),"TestDate"),[{Year:"All"}]],"Value"),Year),"Value","Year")

Best Regards,

Wearsky

Was this reply helpful? Yes No
User88 20 on at

Like (0)

Report

Hi @v-xiaochen-msft

Worked perfectly! Thank you!

Was this reply helpful? Yes No
User88 20 on at

Like (0)

Report

Hello @v-xiaochen-msft

So based on the code how would I go a step further and filter the gallery by 4 drop downs. For instance I would like to filter it by the Year, the quarter, the department, and by the item type.

If I need to start another thread for this I can.

Was this reply helpful? Yes No