Other than dozens of hours watching MS Learn videos and documentation, dozens of hours of classes on LinkedIn Learning, and about a week of reading community articles, I am brand-spanking new to Power Automate! I'm hoping some help from this community will accelerate my learning curve - so thanks in advance to all the community solution providers!
The purpose is to track business documents saved into a SharePoint ‘Docs2Process’ folder (files may be scanned and manually added, email attachments added, and possibly other document collection methods), allow inputs for document-specific data which will be used to rename the file and to determine the file storage location.
I customized the Work Progress Tracker Sharepoint List Template and added these input columns:
- [DocType] (Choice between credit card receipts, bills, vendor statements, and more)
- [Account] (Choice between various credit cards, vendor accounts, etc. and updated by bookkeeper)
- [Vendor] (Choice between vendors generated from QB Online report by bookkeeper)
- [DocDate] (Transaction date)
- [Amount] (Transaction amount)
I created my first successful flow to create a new list item whenever a new file is created in ‘Docs2Process’ folder; also, attach the file contents, [Status] is updated to ‘Not started’, [Start date] is updated to ‘utcnow’, and [Due date] is updated to ‘utcnow’ + 24 hours.
Guidance in developing my Tracking and Filing automations would be most appreciated. Here are my objectives:
Tracking Objectives
- When any of the input columns are updated, the [Status] column updates from ‘Not started’ to ‘In Progress’
- When the [Due date] is past, the [Status] column updates to ‘Behind’ (only if the current state of [Status] is either ‘Not started’ or ‘In Progress’)
- When all the input columns are updated, the [Status] column updates to ‘Completed’
- When the Filing automation successfully renames the attachment file and moves it to appropriate Sharepoint folder, the [Status] is updated to ‘Archived’
- When user updates the [Status] column to ‘Blocked’, a notification is sent to bookkeeper to assist
Filing Objectives
- When [Status] = ‘Completed’, create a new FileName for the attachment using data in the input columns
- [DocType]_[Account]_[Vendor]_[DocDate]_[Amount]
- [DocType] choice value will be converted, i.e. ‘Credit card receipt’ nomenclature will be “cc”
- [Account] choice value can be used ‘as is’
- [Vendor] choice value will prob need to be converted since the choice options will be updated regularly and the FileName should be without spaces, i.e. ‘ABC Supply Co.’ might become “ABCSupply”
- [DocDate] will be converted into ‘YYYY-MM-DD’ format
- [Amount] value should be formatted with 2 decimal places, no currency symbol, and negative amounts begin with “-“ sign
- Then (after the new FileName is created for the document), determine the new file location in a separate SharePoint documents folder (Accounting) and subfolders
- [DocType] choice value will determine the primary folder, i.e. all ‘Credit card receipt’ doctypes will be in the “03. Payables” primary folder
- [DocDate] value will determine both the ‘year’ and ‘month’ subfolders, i.e. “03. Payables/2022/04. April”
