Power Platform Community Forum Thread Details

I'm having a difficult time building a flow to track cancellations and make up appointments for an attendance policy using Power Automate and a Dataverse table. This is what I would like to see happen with the flow.

Trigger:
- An Excel file is uploaded to a Sharepoint folder that has attendance data in it (i.e. File Number, Client Name, Cancellation Date, Appointment Status, Session Type, Cancellation Count, No Show Count, Make up Count, etc.
Actions: I would like Power Automate to do the following:
- Create and Map new attendance record data from the uploaded file to the corresponding fields in Dataverse.
- If a duplicate record is found (via the File Number and Client Name that uniquely identifies the client), update the existing record to reflect the updated attendance count
- Track and group attendance records by their respective 6-month policy periods (e.g., "Jan-Jun 2025" or "Jul-Dec 2025")
- Calculate Counts:
  - Count cancellations (Auto calculates Appointment Status = Cancelled).
  - Count no shows (Auto calculates Appointment Status = Did Not Attend)
  - Count make-ups (Auto calculates Session Type = Make-Up).
  - Total Cancellation Count (Automatically calculates the sum of Cancellation Count and No Show Count for each policy period.
- Thresholds:
  - When a client record has a total cancellation count between 3-5 and/or a make-up count between 3-5, this is flagged (highlighted or some other visual cue) from the views screen n Dataverse
- Notify Admin/Client:
  - If thresholds are met, send automated email alerts to the admin team and the client. There would be a different email sent to the admin team than the client and the client would receive a different email when the 3rd, 4th and 5th cancellation and/or make-up threshold was hit..

Dataverse Field Summary Table:

Field Name	Display Name	Data Type	Purpose
File Number	File Number	Single Line of Text	Unique identifier from EMR.
Client Name	Client Name	Single Line of Text	Name of the client
Cancellation Date	Cancellation Date	Date and Time	Date of the cancellation.
Incident	Incident	Multi-Line of Text	Type(s) of service(s) cancelled
Appointment Status	Appointment Status	Choice	Tracks whether the appointment is Cancelled or Did Not Attend
Cancellation Reason	Cancellation Reason	Multi-Line of Text	Optional reason for the cancellation.
Session Type	Session Type	Choice	Differentiates between regular and make-up sessions.
Policy Period	Policy Period	Single Line of Text	Groups cancellations & make ups into 6-month periods for tracking.
Cancellation Count	Cancellation Count	Whole Number	Tracks cumulative cancellations per policy period.
No Show Count	No Show Count	Whole Number	Tracks cumulative did not attend occurrences per policy period
Make-up Count	Make-Up Count	Whole Number	Tracks cumulative make-up sessions per policy period.
Total Cancellation Count	Total Cancellation Count	Whole Number	Tracks cumulative cancellations + no shows per policy period
Policy Period	Policy Period
Notes	Notes	Multi-Line of Text	Notes from the admin team regarding this record.

Issues being faced with the flow:

The flow triggers independent of a file upload causing too many actions be fired. This is causing MS usage warnings and inaccurate counts within the Dataverse fields
Trigger currently being used is - When a file is created (properties only) due to other similar Sharepoint triggers being deprecated
Dynamic content for the file uploads to Sharepoint are not populating. I have Owner/Administrator permissions to Sharepoint, Power Apps, etc. so unsure why dynamic content isn't showing
Flows are not working due to the aforementioned

I would appreciate any assistance with how to set up this flow (Triggers, actions, expressions) so that it's working properly. Thank you.

Categories:

Building flows