Hi Community,
I am stuck with an issue, but maybe just cannot see the wood for the trees.
In my sharepoint list I maintain Facilities and Certificates. Each Facility can have more than one certificate.
I want to count the amount of certificates, but group them first (e.g. 9001 and 13485)
My current flow looks like this:
1) Get List
2) Filter by ISO9001 and 13485 using: @OR(contains(item()?['Certification/Value'], 'ISO 9001'),contains(item()?['Certification/Value'], 'ISO 13485'))
3) Distinct by using union(body('Select_13485'),body('Select_13485'))
Even with the distinct every certificate is counted twice when I do length(outputs('Distinct_900113485'))
Can anyone help me where I am wrong and how to get at the right path?
Thank you
Solved! Go to Solution.
OK!
After the select, and before applying the union function, could you add another select, and select only the Facility field?
The union (distinct) function is returning all rows, because the function is comparing all columns (so all rows are different), and you need to apply the distinct function only to the Facility column.
Hope it helps!
Ferran
Hi @Anonymous
Sorry, I was confused!!
Before applying the union operator, which fields are you using in the select? (are you only selecting the facility field?)
Regards,
Ferran
Hi @fchopo
Huh? Are you sure that this function exists?
template function 'distinct' is not defined or not valid.'.
Hi @Fn7a
Sorry, I was confused!!
Before applying the union operator, which fields are you using in the select? (are you only selecting the facility field?)
Regards,
Hi @fchopo
i am just using 'Certification/Value' in my select with checking if ISO 9001 or ISO 13485 are present by using
@Or(contains(item()?['Certification/Value'], 'ISO 9001'),contains(item()?['Certification/Value'], 'ISO 13485'))
There is no other select before that action. Just the get list action filtering on all items with a certain status (Filter query Status eq 'valid')
OK!
After the select, and before applying the union function, could you add another select, and select only the Facility field?
The union (distinct) function is returning all rows, because the function is comparing all columns (so all rows are different), and you need to apply the distinct function only to the Facility column.
Hope it helps!
Ferran
Hi @fchopo
I followed your suggestion and did this:
My result returns 21. Which is correct compared to my excel calculation:
Thanks a lot for your help!