Answered

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

(0) Share

Report

Posted on by iivanov Microsoft Employee

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!

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (1)

Verified answer

v-bacao-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No
iivanov Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
iivanov Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

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 !

Was this reply helpful? Yes No