Answered

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

(0) Share

Report

Posted on by Chrisguff12

233

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

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

v-bofeng-msft on at

Like (1)

Report
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

Was this reply helpful? Yes No
Chrisguff12 233 on at

Like (0)

Report

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.

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

Like (1)

Report
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))

Best Regards,

Bof

Was this reply helpful? Yes No
Chrisguff12 233 on at

Like (0)

Report
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())) )

Was this reply helpful? Yes No
Verified answer

v-bofeng-msft on at

Like (1)

Report
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

Was this reply helpful? Yes No
Chrisguff12 233 on at

Like (0)

Report

@v-bofeng-msft

Everything works great now. Thanks for all your help.

Was this reply helpful? Yes No
Chrisguff12 233 on at

Like (0)

Report

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/m-p/828827#M264606

Was this reply helpful? Yes No