Hi, everyone.
I'm using Power Automate to automatize emails according to the data in an Excel worksheet. For my job, I keep track of every invoice I register in our CRM in an Excel file. When an invoice is recorded, I change the value in the "Status" column to "Registered" and email the Accounting department with the PO and invoice.
I keep the files in this path:
G:\My Drive\01 Entries\Active\SupplierName PO 1234
My company requires us to use Google Drive, so I can't use OneDrive.
The files I want to attach have the following name format:
01 YYYY-MM-DD PO 1234 SupplierName.pdf
The variables for the file names and the data for the email's body are retrieved from the Excel file.
I'm getting the data from Excel with an SQL connection, as described on this page: https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel
These are the steps in my flow (summary):
- Define the variable with Excel's path.
- Open the SQL connection.
- Query the data that I need to populate the email and define the file's paths.
Nevertheless, when it comes to the step of sending the email, I got the error that the file doesn't exist.
I know the error is in the variable. If I write the path without using the variables it works, but when I use the variables, it doesn't find the file.
The variables I need to get the paths are these:
For the folder's path:
For the files:
- Document kind ID (01 for PO, 02 for Invoice, 03 for entry acknowledgment)
- Document's date (in YYYY-MM-DD format)
- Document kind ("PO" for PO, "INV" for Invoice, "Entry" for entry acknowledgment)
- Document ID (integer for PO, string for Invoice. Entry acknowledgment doesn't use)
- "PO" + PO# (for Invoice and Entry Acknowledgment).
*For the date, to avoid getting it as an integer, I convert it to string in Excel with this formula:
IF(AND(LEN(MONTH($M2))=1;LEN(DAY($M2))=1);CONCATENATE(YEAR($M2);"-0";MONTH($M2);"-0";DAY($M2));IF(LEN(MONTH($M2))=1;CONCATENATE(YEAR($M2);"-0";MONTH($M2);"-";DAY($M2));IF(LEN(DAY($M2))=1;CONCATENATE(YEAR($M2);"-";MONTH($M2);"-0";DAY($M2));CONCATENATE(YEAR($M2);"-";MONTH($M2);"-";DAY($M2)))))
I tried this without result:
- Using directly the SQL query results to determine the file's path. Concatenating the variables as follows:
G:\My Drive\01 Entry\Active\%supplierNameQuery% PO %ocIdQuery%\01 %poDateQuery% PO %poIdQuery% %supplierNameQuery%.pdf
- Concatenating the file's path in Excel and adding %poFilePathQuery% in the outlook action.
- I tried to store %poFilePathQuery% in a new variable (%poPath%), but it didn't work too.
- I discovered that there was an extra line in the query, so I used the trimming action, but %TrimmedText% didn't work too.
- Tried to reconvert %TrimmedText% with two Python scripts, but didn't work:
#Try 1 (didn't work)
path = %TrimmedText%
print(path)
#It output anything
#Try 2 (didn't work)
path = str(%TrimmedText%)
print(path)
#It output anything
I discovered that the path variable only works until the directory's path (G:\My Drive\01 Entry\SupplierName PO 1234), but it doesn't with the file's name if it's and string, but as an integer, it works.
For example
Works: G:\My Drive\01 Entry\Active\SupplierName PO 1234\1.pdf
Doesn't work: G:\My Drive\01 Entry\Active\SupplierName PO 1234\01 2023-01-01 OC 1234 SupplierName.pdf
