I have been struggleing for a few days to create a simple flow to send an email if a date in an excel sheet is is more than X days old. I have most it almost working but my issue is more about how should I be entering the data and excpressions. It seems that I am working way too hard just to enter and refer to simple expressions. The dynamic content box is not helping much
I wanted to attach an image of my flow I have so far for reference but it seems this forum doesn't allow it.
My basic flow is
"Listrows in an excel table" -> "Filter Array" -> "Apply to each": "append values from the filter to var X" -> Send email with X in the body
Anyway, here's my questions and pain points
1) how can I turn off the auto apply to all action?
I am using an "append to string variable" action within a "apply to each module" which is based off the output of a "filter array". If I use dynamic content and reference an item that is from the wrong action, my entire block gets nested inside a new "apply to all module". This causes all sorts of issues espeically since I didn't notice this was automatically done. I need to trun it off and back to prompt
2) how do I get the dynamic content to show the fields in the output of the filter array or current item in apply to all?
I noticed in hover text of the "current item" has an advanced formula so I used that to manually enter the expression I need. This is painful especially since I do not know the syntax and had to keep hovering over the a different entry to figure out the how to refer to the fields.
After manually entring the field with the help of the expression editor, I can see my item in the module in purple/pink text but only know what it is if I hover over. Good news is that if I close and re-open the flow, it magically appears in blue text with the proper name and is readable. It shows up as if I entered it from dynamic content.
3) How should I enter/test an expression? Is there another editor?
The flow seems to work quite well but my expression is for something like filter array is getting to be a little complex and hard to track quotes and paranthesis. I am entering this all in one single line edit box and only know if it fails when I try to run the flow. I am starting to use notepad to make sure I line up the parans, etc but I suspect there has to be a better way.
this is the condition in my Filter array:
" @and(greater(int(item()?['ID']), 27), greater(item()?['TravelDate'], variables('txtDate')))"
I still need to appy a formatDataTime function to my fields which I am dreding since it will amost unreadale unless I copy it out of the edit box an put it into an editor just to read it. My thought is to use more variables to break up the expresion but this just makes the overall flow larger and harder to read.
BTW, If someone has formula to filter out a date that is more than X days old (e.g. Date > 30 days), that I could use with the filter array action please feel free to share. I see many examples but it seems flow chokes on the formatDateTime function format at run time and if I use a string compare I may have an issue cause my dates are in mm/dd/yyyy format but a true comparison may need to be in yyyy/mm/dd format. I am thinking this is a reason why some of my comparisons may have issues. Note I also need to check for empty/blank dates but I should be able to use lenght = 0 to figure that out.
4) Is there a way to support comments in an action? or restore previous flow?
I have been using the comments section of an action to store a previously working expression but that is painful. I want to simply comment out a working formula, change it a little and see if it works with a minor change. I am finding that flow does not have good way to go back to earler working flows and restore them. If I make a minor mistake in a formula, I have no way to undo it after a run since the run causes a new save to take place which makes my working copy lost. My workaround is to save a copy of the flow with a new name but then I have way too many temp copies cluttering the myflows area. A simple way to add a comment to a formula would go a long way.
5) any advice on where to get a practicle how to walkthrough on how to enter flow epxressions? I found the reference to odata but it's a tough read. Some info/videos showing how to enter advanced odata in flow would go a long way. I am starting to see how power tihs lanaguage is but performing the basics within the tool is the biggest hurdle. I think I am learning but understanding the basic of the underlying language and how to use in the tool seems to be show stopper for flow.
Thanks for letting me vent and sorry for the long message but I wanted to see if here is help for these items as I am not sure if I am working in the most productive way here.
Hi there. So, there's a lot to unpack here and I don't know all of the answers...so I'll hit the ones I do know and hopefully get some momentum behind that. Don't lose hope. It seems like a bunch at first, but I imagine there will be a couple of 'aha' moments and the rest will fall into place.
On your 'btw' with the date math...this has been a bit of a journey for me so I wrote this to help things along for others. It's not exactly what you are doing, but I walk through the principles and expressions needed to subtract two dates. From there, you can use a condition to see if it's more than '30' or not. You don't need to format the dates to do math with them, but you do need to convert them to 'ticks'. It's a weird concept, but I explain it in the article.
One trick I've seen people use for #2 is to copy the expression when they're done building it and pasting it into the comments. This helps mess with it later. As far as editors (#3), try using Notepad ++ There are probably others, but this one is simple enough for me to use, and it highlights the parens if I'm off on my count.
On #5, I found this blog article helpful, along with this learning program. There are tons of cool blogs out there, as well, so it's usually about finding the 1 or 2 (or 9 or 10) that resonate with you the most.
On #1, if it works in your scenario, you can wrap a first() around the array and that will grab only the first record and (hopefully not kick off the loop).
Keep us posted...lots of good help on here.
If this reply has answered your question or resolved your challenge, please consider marking it as a Solution. This helps other users find it more easily via search.
I finall was able to break through on how to get my flow going. The key for me is to ignore the dynamic content editor. It almost always puts the wrong values in for me and causes undo apply to each blocks. Classic example is when I try using filter array. I may post it as new topic but a classic example is when using filter array. When trying to put in an expression to refer to an item, the dynamic content will either automatically insert an apply to all for the body() of the actions's output with the filter array inside the loop or not give you a way to select an element's value in the list. I find I need to manually type in the "item()?['col_name']" to use things properly and if I indavertently select a field from the source of the filter array (to see how to refer to the item, the dynamic content inserts the loop as I mentioned above.
Understanding the "item()" property, and ignoring 90% of the dynamic content was the key to my breakthrough. The following is an example of how a filter worked for me. Note the documentation for the "item()" property implies it is only for loop but it seem it also refer to the item within a filter arrary. In my example below, it refers to an element within the body array (the source of my filter). Since I am using this filter for each element in loop, I am uses the fuller syntax of "items(...)" to refer to the current item in the loop.
Three Super User rank tiers have been launched!
Features releasing from October 2020 through March 2021
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!