Power Platform Community Forum Thread Details

Hi,

I'm struggling to get a working flow to achieve what we need.

We have one SharePoint list called 'All UK Employees AD Listing' and another called 'Location List'. The first list contains all employees 'Display Name' along with their 'Department' and also has a column called 'Last121Completed' which contains the date the last 121 was conducted for that employee. Some items have no date in this column, indicating that no 121 has been completed.

I need to calculate per location the percentage of 121s completed in the last 6 weeks for all employees based at that location and update the corresponding item in the 'Location List'. For example, if the location has 100 employees and 20 of them have had a 121 in the last 6 weeks, it updates the corresponding 'Department' in the 'Location List' with 20.

I have tried many ways to do this, but always run in to errors and fail to achieve it. Could someone please provide a fresh method, as I think I'm losing my mind at this point?!

Thanks.

Categories:

Building flows

When using XPath for lookups, only one Select is needed ;)

Employees (Compose)

[
	{"Name": "Jim", "Department": "A", "C121": "2024-09-01"},
	{"Name": "John", "Department": "B", "C121": "2024-09-01"},
	{"Name": "Jane", "Department": "C", "C121": "2023-09-01"},
	{"Name": "Lara", "Department": "D", "C121": "2024-09-01"},
	{"Name": "Lasse", "Department": "A", "C121": "2024-09-01"},
	{"Name": "Liam", "Department": "B", "C121": "2024-09-01"},
	{"Name": "Tim", "Department": "C", "C121": "2024-09-01"},
	{"Name": "Tom", "Department": "A", "C121": ""},
	{"Name": "Terry", "Department": "B", "C121": "2023-09-01"},
	{"Name": "Frank", "Department": "D", "C121": "2024-09-01"}
]

Departments (Compose)

[
	{"Department": "A", "Location": "London"},
	{"Department": "B", "Location": "Birmingham"},
	{"Department": "C", "Location": "London"},
	{"Department": "D", "Location": "Liverpool"}
]

Select

From

union(
	xpath(
		xml(json(concat('{"Root":{"Item":', outputs('Departments'), '}}'))),
		'//Item/Location/text()'
	),
	json('[]')	
)

Map Location

item()

Map Employees

xpath(
	xml(json(concat('{"Root":{"Item":', outputs('Employees'), '}}'))),
	concat(
		'//Item[contains("',
			'|',
			join(	
				xpath(
					xml(json(concat('{"Root":{"Item":', outputs('Departments'), '}}'))),
					concat('//Item[Location="', item(), '"]/Department/text()')
				),
				'|'
			),
			'|',
		'", concat("|", Department, "|"))',
		']/Name/text()'
	)
)

Map C121Completed

xpath(
	xml(json(concat('{"Root":{"Item":', outputs('Employees'), '}}'))),
	concat(
		'//Item[contains("',
			'|',
			join(	
				xpath(
					xml(json(concat('{"Root":{"Item":', outputs('Departments'), '}}'))),
					concat('//Item[Location="', item(), '"]/Department/text()')
				),
				'|'
			),
			'|',
		'", concat("|", Department, "|"))',
		' and translate(C121, "-", "") > translate("', addDays(utcNow(), -42, 'yyyy-MM-dd'), '",  "-", "")',
		']/Name/text()'
	)
)

Map Percentage

formatNumber(
	div(
		xpath(
			xml(json(concat('{"Root":{"Item":', outputs('Employees'), '}}'))),
			concat(
				'count(//Item[contains("',
					'|',
					join(	
						xpath(
							xml(json(concat('{"Root":{"Item":', outputs('Departments'), '}}'))),
							concat('//Item[Location="', item(), '"]/Department/text()')
						),
						'|'
					),
					'|',
				'", concat("|", Department, "|"))',
				' and translate(C121, "-", "") > translate("', addDays(utcNow(), -42, 'yyyy-MM-dd'), '",  "-", "")',
				']/Name/text())'
			)
		),
		xpath(
			xml(json(concat('{"Root":{"Item":', outputs('Employees'), '}}'))),
			concat(
				'count(//Item[contains("',
					'|',
					join(	
						xpath(
							xml(json(concat('{"Root":{"Item":', outputs('Departments'), '}}'))),
							concat('//Item[Location="', item(), '"]/Department/text()')
						),
						'|'
					),
					'|',
				'", concat("|", Department, "|"))',
				']/Name/text())'
			)
		)
	),
	'0%'
)