cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Torrentj
Frequent Visitor

start an approval

I have an Excel spreadsheet populated by a Forms (works great).  Mid Flow it starts an approval and waits (again works great).  I would love to include in the approval email detail section a list of all that users' previous responses. 

Torrentj_0-1662671656131.pngTorrentj_1-1662671792540.png

 

the end result would be (John is user in example):

'John  Office'

'John Other Teacher'

'John Respite'

'John Office'

 

the above would be included in the initial approve/decline email (to help with the discission) 

 

1 ACCEPTED SOLUTION

Accepted Solutions
WillPage
Solution Sage
Solution Sage

Define a couple of variables:

WillPage_0-1662950722834.png

Here's my array, it's just a simple single column array of strings, one for each letter of the alphabet

WillPage_1-1662950770935.png

A Do until loop

WillPage_2-1662950828537.png

The result:

WillPage_3-1662950888576.png

Since you array is already an array of objects with many properties, instead of hand coding the JSON structure of the object within the Set variable action, you should use the addProperty() function to add the index on to the existing schema.

 

View solution in original post

4 REPLIES 4
WillPage
Solution Sage
Solution Sage

Get the items in your yellow table filtered for that person, use a Select to transform the data into an array of strings with the relevant info (by clicking the icon to switch from key-value to raw input mode) and do an expression like concat(item()?['Name'], ' ', item()?['Destination])  then a Join action and join them on a line break (shift and enter in the join-on input). Drop the result of the Join into your approval body.

Torrentj
Frequent Visitor

Update:  Thank you to @WillPage for getting me this far.  I have 2 minor tweaks needed:  

1.  I have my approval email showing the table data.  Is there a way to format it so it list as an 'numbered list'?

Torrentj_0-1662848807360.png

It gets it's formatting from a variable.

 

Torrentj_1-1662848877723.png

 

2. The next request might be a little more difficult.  The Date/Time is listed as a decimal.  How to format as MM/DD/YYYY HH: MM: SS

 

WillPage
Solution Sage
Solution Sage

@Torrentj  Well done getting this far. Your next challenges are a little complex, but not outside the bounds of possibility.

The reason your timestamps are decimal is because you're using Excel as a data source. When Excel displays a date, it's converting that decimal number to a date via an algorithm. The decimal is the number of days since Jan 1 1900, 00:00:00 UTC.

To convert that to a human readable timestamp, split the number on the . character, and use the addDays() function to add that number of days to '1900-01-01' e.g. addDays('1900-01-01',first(split(item()?['StartTime'],'.')))

You will also need to deal with the time too, so you have to take the bit after the dot and convert that fraction of a day into a number of minutes, and possibly seconds, depending on how accurate you need to be.

So, to do all that in a Select action:

addMinutes(addDays('1900-01-01',first(split(item()?['StartTime'],'.'))),int(formatNumber(mul(float(concat('0.',last(split(item()?['StartTime'],'.'))),1440),'0')),'MM/dd/yyyy HH:mm:ss')


This will round it to a minute. If you want seconds, simply use the addSeconds() function instead of addMinutes() and multiply by 86400 instead of 1440

There may be some UTC conversion to do here too so you're not a day out. Where I am on the opposite side of the world to London, the UTC date rolls over during business hours which adds a layer of complexity to this conversion, but you might not need to bother if the UTC date change happens outside of the working day. In any case you might need to add or subtract a day from the result to get it to line up.

Numbering your result isn't all that easy, I may need to do some screen shots to help you, so stand by..

WillPage
Solution Sage
Solution Sage

Define a couple of variables:

WillPage_0-1662950722834.png

Here's my array, it's just a simple single column array of strings, one for each letter of the alphabet

WillPage_1-1662950770935.png

A Do until loop

WillPage_2-1662950828537.png

The result:

WillPage_3-1662950888576.png

Since you array is already an array of objects with many properties, instead of hand coding the JSON structure of the object within the Set variable action, you should use the addProperty() function to add the index on to the existing schema.

 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (5,686)