Re: Report Dept wise- MODELL DRIVEN APP

(0) Share

Report

Posted on by Drew Poggemann

9,287 Most Valuable Professional

Hi @Teresa24486 ,

You could use a switch-case in the flow to identify where to send the email based on the department so if Department A then send to email1@company.com and if Department B then send to email2@company.com.

Overall a table might be better long term for the department if you are driving communications and other information.

Hope this helps. Please accept if answers your question or Like if helps in any way.

Thanks,

Drew

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (11)

Answers (0)

Teresa24486 613 on at

Like (0)

Report
Copy link

Link copied!

Hi,
I need assistance in the flow creation. I am stuck in retrieving the records department wise and sending an email to the concerned department on a monthly basis.

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!
Hi @Teresa24486

I did some work to hopefully get you started here and made some design decisions in the process to make it easier to manage and hopefully get what you are looking to do...

Tables / Setup
Created a table called "Schedule" that has a date only field for schedule date and the Name field was set to AutoNumber. This table's primary job is to determine when you would want to run the process. So you would need to set this up with the dates you want to execute and send the reports. Of course you could get more complex with this and add the Department as a lookup field or other types of functionality but I kept it simple.
Created a table called "Department" that would hold each of your departments. This is better than a plain Choice field and trying to use String Map table in my opinion. Of course this is another option but I like using tables as they provide more flexibility.
Power Automate Flows
Activate Schedule if Current Date
The purpose of this flow is to run daily (1 time) and look at your schedule table to determine if it should activate the schedule record. All future dates would be setup as "Inactive" and this process would look at the schedule run date field and if it is the same as current date then activate the record (note this will be trigger for next flow 😀).

Some screen shots of this flow are below. Of course there are ways to optimize this as well and set additional criteria on the selection but I kept simple.

After this flow completes it will have updated the Schedule table record for the current date and that would kick off the second flow.

Widgets next 30 days by Department
The goal for this flow is that it would be kicked off by the schedule record update to the statecode and it would run for each department and look for records in your table (my Widgets table as example) where the department matches and the Expiry date is within the next 30 days) and send out notifications. Of course you might also filter based on a status of that record etc. as well but I kept simple in my example.

After this you would still need to create your Excel file and insert the data and then send the file by email to I am assuming an email field tied to the department table (another good reason to have as a table).

This might get you started on the Excel file part: https://benediktbergmann.eu/2020/09/27/create-and-fill-an-excel-file-dynamically-with-power-automate/

Hope this helps you down your path @Teresa24486 .

There are many ways to do this but I think this would work well.

Hopefully this helps. Please accept if answers your question or Like if helps in any way.

Thanks,

Drew

Was this reply helpful? Yes No
Teresa24486 613 on at

Like (0)

Report
Copy link

Link copied!

Hi @dpoggemann,
Thank you for the flow details and your time.

I started working on my flow in a similar way, just that I did not use the first part of the flow. Can we not simply initiate by the 2nd part using the filter condition of active records?

In my scenario, I have 3 tables- Schedules(where all the records reside), department table(dept names), Employee table(emp name, emp dept(lookup to dept), email address(datatype email).

Here is my flow:

My flow checks the expiry date based on conditions(3 interval period):

If condition matches, here it is:
I am unable to fetch the user email address to trigger an email using the email control.

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Teresa24486

You had mentioned you wanted to automatically run this process on intervals of bi-weekly, monthly, and 1.5 months in your private message and this is not an interval possible so I utilized the Schedule table to drive you entering these dates and then the process would run daily, if it matches the dates then it would execute and if not it would stop. Of course I accidentally picked the name "Schedule" as my table which is not like your "Schedule" table as yours is like my "Widgets" table.

Currently you have to manually trigger the flow which means it is not going to run on a schedule and someone has to manually initiate it.

You are looping through each record checking the expiry dates to see if they fit in the three periods, not sure I understand this logic. I thought you wanted to run on the periods and then grab all the records that expire within the next month for that period and then be able to add these into independent Excel files for each department? I added the loop for the department so you could dynamically add departments in the future and the process would still work. It would use the fetchXML query to pull all of the records expiring in the next month for each department and then you could add them to independent Excel files.

Hope this helps.

Thanks,
Drew

Was this reply helpful? Yes No
Teresa24486 613 on at

Like (0)

Report
Copy link

Link copied!

hi @dpoggemann,
So my understanding is you have 3 tables created in your scenario. Schedule(I havent created one for this purpose)for triggering flow based on intervals, Widgets(In my case Records/Schedules table as on screenshot) to track the records, Department table for department related records. I have one more additional table for the user records(emailing purpose).
I'll change the manual trigger to a scheduled one. So wont conditional loop work in my case based on the conditions used on intervals?

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Teresa24486 ,

Maybe I don't understand the conditions... You have a set of three that are checking each record with Or condition. What is this checking on the record with the Expiry Date? I thought you wanted the job to trigger ever 2 weeks, 1 month, 1.5 month intervals and pull all records that expire in next 30 days (by department) from that trigger date? If you are doing the combined condition is this grabbing records up to 1.5 months from date it is triggered?

Thanks,
Drew

Was this reply helpful? Yes No
Teresa24486 613 on at

Like (0)

Report
Copy link

Link copied!

Hi @dpoggemann,
I thought you wanted the job to trigger ever 2 weeks, 1 month, 1.5 month intervals and pull all records that expire in next 30 days (by department) from that trigger date? - Yes, correct.
So the condition says if expiry date<= 1month or 1.5months or 2 weeks then it should check the records from schedule/record table filtered by dept, (these filtered records are stored as individual files on OneDrive dept wise and email address should be fetched from the custom user table and send an email with the attachment.

Was this reply helpful? Yes No
Teresa24486 613 on at

Like (0)

Report
Copy link

Link copied!

Hi @dpoggemann,
Also, how do I loop through the other two conditions(1.5 months and biweekly)? where in the flow can I set these conditions?
Sorry if I am being blunt, I am in the learning process. 😐

Was this reply helpful? Yes No
Teresa24486 613 on at

Like (0)

Report
Copy link

Link copied!

Hi,
So I have reached a stage where I am able to get my records department wise as a csv attachment file as well as a table format on the body of my email (attached flow01.png).

Current Issues:
1- I am not able to convert the file to .pdf using the convert file control.
Error: The 'inputs.parameters' of workflow operation 'Convert_file' of type 'OpenApiConnection' is not valid. Error details: The resolved string values for the following parameters are invalid, they may not be null or empty: 'id'

2- I am getting all the records based on the my conditions used(3 intervals) but not for the specific month; so if there are 10 records, I get all 10 files segregated by Department but not ones specific to the coming month(which may be only 2-3).

3- I am getting 2 emails simultaneously for the same record(I do not have 2 email controls set). Signature(flow01.png attachment) is in the table format surprisingly.

4- If there are no records for the coming month, an email shouldnt trigger to the concerned dept OR should notify saying something like 'There are no records that are due for xx month'.

5- Send email to the department specific users, as of now I have set it to 'me'.

Can someone shed some light here please?

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @Teresa24486
1. Please make sure file that you are trying to convert is in accepted file formats. Example a .doc or something like that will not convert. https://docs.microsoft.com/en-us/onedrive/developer/rest-api/api/driveitem_get_content_format?view=odsp-graph-online
2. As I mentioned a few times in replies to this, I am not sure how your three ranges would get records for the next month. They are looking at each record and I assume will pull records that are expiring in next 1.5 months vs. 1 month. based on your condition. I still think you need to look at the approach of identifying the dates you want the process to run in a separate table and then have the first flow check if date matches current date and if so, update that record and then utilize this update to trigger the next flow (in my details provided).
3. Not sure yet on your two emails, would need more information...
4. If you select all records for the department that fall into the query for the next month you could utilize a length function on the List Records result, see here: https://d365demystified.com/2020/05/20/get-count-of-records-retrieved-in-cds-connector-in-a-flow-power-automate/
5. You could do a couple things here...
First you could have a distribution list or office 365 group setup for the department and then save this as a field on the department table (simple and easy to modify by changing the members of the group) and pull this email when sending out.
Second you could manage a team in the Dataverse and enter your users in that team. You would then need to loop through this and create a string with all the emails for the users to put into an email message (harder to build and maintain).
Hope this helps. Please accept if answers your question or Like if helps in any way.

Thanks,

Drew

Was this reply helpful? Yes No