Skip to main content

Notifications

Power Apps - Microsoft Dataverse
Unanswered

Re: Report Dept wise- MODELL DRIVEN APP

(0) ShareShare
ReportReport
Posted on by 9,273

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

  • Teresa24486 Profile Picture
    Teresa24486 611 on at
    Re: Report Dept wise- MODELL DRIVEN APP

    Hi @dpoggemann ,

     

    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=o... 

     

    I am trying to convert my html output which went to a create csv connector and after which passes to the create file connector:

    Teresa24486_0-1629223756529.png

     

    Getting an error on the flow on the get metadata id connector:

    Teresa24486_2-1629224524723.png

     

     

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

     

    I used the fetch xml for my conditions and I am getting my records- still testing the email triggers though.

     

    3) Not sure yet on your two emails, would need more information...

    Looks like its ok now. Not sure why I received 2 emails though.

     

    4) and 5) - Still working out to filter the correct output.

    Teresa24486_1-1629224469727.png

     

     

  • Drew Poggemann Profile Picture
    Drew Poggemann 9,273 on at
    Re: Report Dept wise- MODELL DRIVEN APP

    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

  • Teresa24486 Profile Picture
    Teresa24486 611 on at
    Re: Report Dept wise- MODELL DRIVEN APP

    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'

    Teresa24486_0-1628962976620.png

    Teresa24486_1-1628963025439.png

     

    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?

     

  • Teresa24486 Profile Picture
    Teresa24486 611 on at
    Re: Report Dept wise- MODELL DRIVEN APP

    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. 😐

  • Teresa24486 Profile Picture
    Teresa24486 611 on at
    Re: Report Dept wise- MODELL DRIVEN APP

    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. 

     

  • Drew Poggemann Profile Picture
    Drew Poggemann 9,273 on at
    Re: Report Dept wise- MODELL DRIVEN APP

    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

  • Teresa24486 Profile Picture
    Teresa24486 611 on at
    Re: Report Dept wise- MODELL DRIVEN APP

    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?

     

  • Drew Poggemann Profile Picture
    Drew Poggemann 9,273 on at
    Re: Report Dept wise- MODELL DRIVEN APP

    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

  • Teresa24486 Profile Picture
    Teresa24486 611 on at
    Re: Report Dept wise- MODELL DRIVEN APP

    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:

    Teresa24486_0-1628667078721.png

     

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

    Teresa24486_1-1628667194482.png

     

    If condition matches, here it is:

    Teresa24486_2-1628667247540.png

    I am unable to fetch the user email address to trigger an email using the email control. 

     

  • Drew Poggemann Profile Picture
    Drew Poggemann 9,273 on at
    Re: Report Dept wise- MODELL DRIVEN APP

    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

    1. 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.
    2. 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.

    Screen Shot 2021-08-09 at 1.12.11 PM.pngScreen Shot 2021-08-09 at 1.12.54 PM.png

     

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

     

    Screen Shot 2021-08-09 at 1.22.22 PM.pngScreen Shot 2021-08-09 at 1.22.36 PM.png

     

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,508

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,839

Leaderboard

Featured topics