You have done a good job of describing what you want, but you haven't shared any actual flow attempts. You clearly have a very good understanding of the process you want, so it would be better if you were to take that (heck you would probably be able to use that as a Natural Language input to CoPilot and build it or most of it correctly).
Since we are free like.... support, we aren't really intending to write everyone's complete work but provide guidance
Please see inside notes, it you follow them and come back for help you will definitely be done quickly.
Maybe I will try step by step describe, what I want
1. Process should be run manually
You said run it manually. Do you mean a person is going to trigger it? Or do you mean a file gets produced somewhere and it should run "automatically" which would be an Automated Flow, not a manual
2. I need read the mentioned table, filter only rows, where Status is empty
When flow triggers add a List rows from Table action for Excel. Make sure the file is in SharePoint or OneDrive and or if its Excel Online (business)
Add a Filter Status eq null
3. Add a Select action to only pull out the Supplier Column. We need to do this, because of your other requirement to do things based on the Supplier. So we need to create a Unique list of Supplier codes and Iterate the rest of your steps for EACH of those, versus iterating on the list you got from step 2 as a whole
Once you add the select, make the Input the output from #2, its Dynamic value property (its called value)
For the Map, I want you to click the little Advanced Button, so that it turns into a Single line, NOT a Key/Value pair view
inside put the supplier column name as thats all we want. This will output a full list of them, including duplicates of each other. Literally its just an array of the values, not even the "header" will be in the list
4. Add a Compose
add an expression
union(body('Select'), body('Select'))
The output of this expression will be a Unique Array of Supplier Codes which is what we want
5. Add an Apply to Each action, using the output from Step 4. This will loop through all the unique Supplier codes, so we can do the actions you want specifically to that supplier only
Inside the apply to each do your other steps
5a (4a). Split table by supplier and create excel file for each supplier with columns: date, supplier, Article, Quantity, Filled by supplier and save file under additional folder "ActiveRequests" with filename: supplier_code.xlsx
Add A Filter Expression
The input is the Output from Step 2 (we need to filter the entire list of excel based on this unique Supplier code)
Left side would be the Dynamic property Supplier (from step 2)
is equal to
right side item() <== is the expression item() because our Loop is just an array of Supplier Codes so item() =
s that iterations code
5b. Now that we have ALL the records from Excel that are for THIS particular supplier do the rest of your stuff. Now since you added some stuff out of order I am
Depending on if the file is in sharepoint or onedrive, add a List Files action and add filters to look for the file name of the supplier code we are on.
5c. Add a Condition
In the condition we want to check if the Length of the array of values returned from step 5b is 1 or 0, meaning did it find your file or not, there are other ways to do this also
If the file is there it will be 1 and so in the Yes side, do your comparisons
If no file, then Create the file in the No side of the condition. You will need to have an Excel "Template" file somewhere that you can create the file content from to create the new file.
5d. Do a Get Items from SharePoint, filtering on the Supplier Code, so you can get the Email Address
5e. Add a Delay action making it.. up to 10 minutes, because your new excel file is going to be locked so you may not get the link out of it yet.
5f. Create your SharePoint Share Link
5g. Use Send Email V2 to send the email using all the stuff you collected to send to the Supplier
5h. Add an Apply to each with the input being the output from step 5a. this was the step that gave us ONLY the rows in excel for that supplier code and that are active
inside THIS apply to each... you need to
1) add an Update Row in a table and update the excel file to say SENT for the specific for in this apply to each
PROBLEM: its most likely the file being locked could very well block this from happening, so you my need to add another Delay before this...
Explained above
3. Take supplier email from SharePoint list, share the file with supplier, extract sharepoint link and send email to supplier with this link
Explained above
4. After email send, should be excel update - put status Sent and Sent date in excel
Explained this above
5. Next time, when process run, system should check the folder "ActiveRequests", do we have file with mentioned supplier already, if we don't have, then run this process start from step1 ->step2->etc., BUT if file already exist, then check the content and compare with input file and add missing lines from input file and share already existing link with supplier.
Please read through this and start putting the pieces together and come back for help as you go.
165
Share
Report
All responses (
Answers (
