cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Chrisguff12
Resolver I
Resolver I

Screen with multiple reports, with filters, and Email Option.

I have been trying to set up a report screen using other thread on the site but I can’t seem to get the formulas right.

I want to use one screen to show two different sets of reports (one at a time). I also want to be able to filter the data in a few ways. And lastly, I would like to set up an email option. The reports are on data tables, if I need to, I could switch to galleries. I just like the way the data tables show all the data from all the reports. The data tables would only be visible after a report is selected then you select the filters then you would select which email group to send the current visible report to.

I’ve included a screen shot of the Objects names.

Here are the names and choices:
Select Report: ["Cleaning Reports", "Maintenance Reports"] // (Dropdown)
Machine Name: //Linked to Tables on OneDrive the names are (Etcher_Cleaning_Sheet & Etching_Maintenance_Reports)
Coworker Name: // (Text Input) Search by coworker
Date: ["This Week", "This Month", "Year to Date"] // (Dropdown)
Email Report to: ["Etching Team", "Management"] // (Dropdown

Chrisguff12_0-1612835132096.png

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Chrisguff12 :

  Please try :

 

Switch(
   'Date_Filter_VH-C_Dropdown'.Selected.Value,
   "This Week",
   Filter(Etcher_Cleaning_Sheet,'Date and Time'>=DateAdd(Today(),If(Weekday(Today())<>1,-Weekday(Today())+2,-6),Days) && 'Date and Time'<DateAdd(Today(),If(Weekday(Today())<>1,9-Weekday(Today()),0),Days)),
   "This Month",
   Filter(Etcher_Cleaning_Sheet,Year('Date and Time')=Year(Today()) && Month('Date and Time')=Month(Today())),
   "Year to Date",
   Filter(Etcher_Cleaning_Sheet,Year('Date and Time')=Year(Today())),
   Etcher_Cleaning_Sheet
)

I think this link will help you a lot:

If and Switch functions in Power Apps 

 

Best Regards,

Bof 

View solution in original post

7 REPLIES 7
v-bofeng-msft
Community Support
Community Support

Hi @Chrisguff12 :

I'm not sure if I understand what you mean,please point it out if there is an error:

  • You want to display data in a datatable control as a report.
  • You want the data source in the datatable control to change according to the selection of a drop down control.

if so,I advice you to create two datable controls and use their visible property to show or hide them to achieve your needs.For example:

1\Add a Dropdown control(Dropdown1)

Items

["Cleaning Reports", "Maintenance Reports"]

2\Add a Datatable(DataTable1)

Items

Cleaning

Visible

Dropdown1.Selected.Value="Cleaning Reports"

3\Add a Datatable(DataTable2)

Items

 

Maintenance

 

Visible

 

Dropdown1.Selected.Value="Maintenance Reports"

In this way, different reports can be displayed according to the dropdown's selection

 

Best Regards,

Bof 

Yes perfect thank you. That solves the first part of what I'm trying to do. and like I thought I was making it harder then it needed to be. 

 

Now after one of the reports has been selected I need to filter the results by the drop downs, textinput, and combo boxes. I always seem to have trouble using filters and getting the right formulas they always seem to be missing something.  

I need to be able to apply each filter independently or all of them to reduce the data displayed.

for the date there are three options ["This Week", "This Month", "Year to Date"].

 

After filtering the data. I need to send the results in an email.

Chrisguff12_1-1612842052185.png

 

 

Hi @Chrisguff12 :

I've made a test for your reference:

1\My data source:

ClearCollect(TestCollection,{'Date and time':Now()},{'Date and time':DateAdd(Now(),35,Days)})

2\Add a dropdown control(Dropdown2) 

3\Set the datatable's items property to:

Switch(
   Dropdown2.Selected.Value,
   "This Week",
   Filter(TestCollection,'Date and time'>=DateAdd(Today(),If(Weekday(Today())<>1,-Weekday(Today())+2,-6),Days) && 'Date and time'<DateAdd(Today(),If(Weekday(Today())<>1,9-Weekday(Today()),0),Days)),
   "This Month",
   Filter(TestCollection,Year('Date and time')=Year(Today()) && Month('Date and time')=Month(Today())),
   "Year to Date",
   Filter(TestCollection,Year('Date and time')=Year(Today()))
)

 Formula Reference:

1\Get the time at 0 AM on the first day of the week

DateAdd(Today(),If(Weekday(Today())<>1,-Weekday(Today())+2,-6),Days)

2\Get the time at 0 AM on the first day of next week

DateAdd(Today(),If(Weekday(Today())<>1,9-Weekday(Today()),0),Days))

66.gif

Best Regards,

Bof

 

Hello,@v-bofeng-msft 

 

Thanks for the date formulas. I had to replace the collection with my data source on one drive but I got it working. When I haven't made a selection, the data table is blank. How can I make the filter default to show all data until a selection is made from drop down?  

 

My current formula.

Switch(
   'Date_Filter_VH-C_Dropdown'.Selected.Value,
   "This Week",
   Filter(Etcher_Cleaning_Sheet,'Date and Time'>=DateAdd(Today(),If(Weekday(Today())<>1,-Weekday(Today())+2,-6),Days) && 'Date and Time'<DateAdd(Today(),If(Weekday(Today())<>1,9-Weekday(Today()),0),Days)),
   "This Month",
   Filter(Etcher_Cleaning_Sheet,Year('Date and Time')=Year(Today()) && Month('Date and Time')=Month(Today())),
   "Year to Date",
   Filter(Etcher_Cleaning_Sheet,Year('Date and Time')=Year(Today()))
)

 

Hi @Chrisguff12 :

  Please try :

 

Switch(
   'Date_Filter_VH-C_Dropdown'.Selected.Value,
   "This Week",
   Filter(Etcher_Cleaning_Sheet,'Date and Time'>=DateAdd(Today(),If(Weekday(Today())<>1,-Weekday(Today())+2,-6),Days) && 'Date and Time'<DateAdd(Today(),If(Weekday(Today())<>1,9-Weekday(Today()),0),Days)),
   "This Month",
   Filter(Etcher_Cleaning_Sheet,Year('Date and Time')=Year(Today()) && Month('Date and Time')=Month(Today())),
   "Year to Date",
   Filter(Etcher_Cleaning_Sheet,Year('Date and Time')=Year(Today())),
   Etcher_Cleaning_Sheet
)

I think this link will help you a lot:

If and Switch functions in Power Apps 

 

Best Regards,

Bof 

View solution in original post

@v-bofeng-msft 

 

Everything works great now. Thanks for all your help. 

Chrisguff12
Resolver I
Resolver I

Email portion:

Just wanted to let anyone looking at this thread that the email portion of this thread is on another form.

https://powerusers.microsoft.com/t5/Building-Power-Apps/Emailing-a-data-table-along-with-its-filters...

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,603)