Creating HTML table of Azure DevOps query list/date conversion for every work item

[iivanov]

Hello,

I am currently building a Flow to return a list of items from a query in Azure DevOps (VSO/VSTS), which will create an HTML table from the data, then send it in an email.

The one part that I am stuck on is how to convert the date/times to something that's human-friendly. For example, from ADO, this is the format:

I can easily do the conversion to the format I want (12/20/2018 format) for one value, but I'm unsure how to do it for an entire list.

Here is what I have so far: 

Here is the formateDateTime function I have in the HTML table, which works for converting the Current Time action into the correct format:

formatDateTime(body('Current_time'),'MM-dd-yyyy')

How would I run every list item through that conversion?

Thank you for your help!

[v-bacao-msft · Accepted solution]

Hi @iivanov ,

I don't know the specific configuration of Parse JSON and Assemble list into table action. Could you provide more details about them?

I guess you want to integrate the details of the items obtained in Get Ready for Triage query action into an HTML table.

If so, I recommend using Get work item details and array variables to organize the table content.

Please consider using the following method to configure Flow.

• Initializes an array variable and iterates over the value of Get Query results action.
• Configure Get work item details action and append the contents of its output to the HTML table.
• For the format of Date, please refer to the following Expression:

formatDateTime(body('Get_work_item_details')?['fields']?['Microsoft_VSTS_Common_StateChangeDate'],'MM/dd/yyyy')

Image reference:

Tips:

When configuring Append to array variable action, the Dynamic content related to Get work item details may not be available.

Please try to specify a Type in Work Item Type field first. After Append to array variable action is configured, you could select Enter custom value and use Dynamic content-Work Item Type generated by Get query results to configure this field.

Best Regards,

[iivanov]

Thank you for the quick reply @v-bacao-msft ! Works perfectly!

I have some values, such as dates and a few other string parameters, that may be empty in ADO. How would I account for that so that the email doesn't fail when these are null?

Thank you again!

[iivanov]

Short update from me since I was able to use some support threads here to get this one figured out. Here's what I used to get around NULL values:

if(empty(body('Get_work_item_details')?['fields']?['YOUR_FIELD']), '', formatDateTime(body('Get_work_item_details')?['fields']?['YOUR_FIELD'],'dd-MM-yyyy'))

This IF statement will evaluate if your field is empty; if it is, it will output a blank string (''). If it's not empty, it will format date/time of your chosen field to the dd-MM-yyyy format.

Thanks again @v-bacao-msft !