Checking for the same record in Compose outputs?

(0) Share

Report

Posted on by Usernametwice

503

Hi all,

Not sure what happened to my last post but it seems to have not been posted. Anyway this is my second attempt.

I am trying to check for the same record (First name, Last name and email) in Compose outputs.

I have some data here as an example and as you can see 'Jane Tonkin' appears twice in the results:

[
 "Jane,Tonkin,jane@gmail.com",
 "John,Bravo,john@gmail.com",
 "Ben,Simons,ben@hotmail.com",
 "Jane,Tonkin,jane@gmail.com"
]

My flow current has an 'Apply to each' where it will check each record from the results and then a function to split each record.

Example of Jane Tonkin record when split:

[
 "Jane",
 "Tonkin",
 "jane@gmail.com"
]

I have then used a formula to determine which line of the split record I would like to use. So if I wanted to use the First name of the split record, it would be something like this:

replace(split(variables('recordLine'),',')[0],'"','')

As mentioned in the beginning of this post, I need to determine whether Jane's record has the same record (more than once) in the Compose output results above. I'm thinking I could create an expression/formula that would search for Janes First name, Last name, email (in the Compose output results) and then show how many times that record occurs. This also needs to be dynamic because it needs to check for multiple records that may have the same result.

Off topic but I have used something similar to iterate through a set of outputs and check for false (credit to @grantjenkins ).

Example:

length(xpath(xml(json(concat('{"root": { values:', outputs('compose'), '}}'))), '//root/values[normalize-space(iio_answer)="false"]'))

I'm hoping someone can help me write this as it's a little out of my league.

Thank you in advance !!

Categories:

Building flows

I have the same question (0)

All responses (8)

Answers (0)

grantjenkins 11,063 Moderator on at

Like (0)

Report
@Usernametwice23 If you just wanted to remove duplicate entries from your array, you could use a union expression as below.

Compose contains your array, including the duplicate entries.

Compose Unique uses the union expression to remove duplicates. You just need to pass in Compose twice.
union(outputs('Compose'), outputs('Compose'))

The output would be:

Apply to each would then iterate over each of the unique entries in your array using the output from Compose Unique.

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Unknown geen idee 1,757 on at

Like (0)

Report
Dear Usernametwice23,

I suggest you use a union() expression. The output for this will be only unique records, which I believe is what you are after. I am not sure if this also works with arrays with multiple items (never tried), if it does not you could combine all the items first into 1. In your case you would then get something like:
[ "JaneTonkinjane@gmail.com", "JohnBravojohn@gmail.com", "BenSimonsben@hotmail.com", "JaneTonkinjane@gmail.com" ]
Either way, check it out. There is plenty of documentation/videos around on the use of the expression. It is not difficult at all.

If this has helped, please indicate as like/solution in the thread for other users.
Happy flowing,
Koen

Was this reply helpful? Yes No
Usernametwice 503 on at

Like (0)

Report

Hi @Koen5 and @grantjenkins thanks for your reply.

I really appreciate your suggestions, the union expression is interesting indeed, but I need to detect how many duplicates there are for each name in the array.

So i need to use the 'length' expression somehow to give me the amount of duplicates for each record in the array if any appear. So in this case, Jane's record would give me 1 duplicate and the rest of the records would be 0 due to no duplicates. This is why I have an 'Apply to each' checking each name in the array.

Sorry for any misunderstanding and feel free to clarify.

Thank you!

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report
@Usernametwice23 What would you want as output showing the number of duplicates? Something like that below?

[ { "FirstName": "Jane", "LastName": "Tonkin", "Email": "jane@gmail.com", "Duplicates": 1 }, { "FirstName": "John", "LastName": "Bravo", "Email": "john@gmail.com", "Duplicates": 0 }, { "FirstName": "Ben", "LastName": "Simons", "Email": "ben@hotmail.com", "Duplicates": 0 } ]

Or did you just want an expression where you could pass in the email, and it returned the number of duplicates? Or something else?

Also, why do you want to know how many duplicates there are?

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report
@Usernametwice23 If you were after the output I mentioned in the last post, then this is how I'd build the flow.

Full flow below. I'll go into each of the actions.

Data contains your array of data.

XML converts the data to XML so we can use the count expression to find number of duplicates. The expression used is:
xml(json(concat('{"root": { value:', outputs('Data'), '}}')))

The XML output would be:
<root> <value>Jane,Tonkin,jane@gmail.com</value> <value>John,Bravo,john@gmail.com</value> <value>Ben,Simons,ben@hotmail.com</value> <value>Jane,Tonkin,jane@gmail.com</value> </root>

Select builds up our objects including First Name, Last Name, Email, and Duplicates. Below are the expressions used.
//From (input) union(outputs('Data'), outputs('Data')) //First Name trim(split(item(), ',')[0]) //Last Name trim(split(item(), ',')[1]) //Email trim(split(item(), ',')[2]) //Duplicates sub(xpath(outputs('XML'), concat('count(//value[.="', item(), '"])')), 1)

After running the flow, we would have the following output:
[ { "First Name": "Jane", "Last Name": "Tonkin", "Email": "jane@gmail.com", "Duplicates": 1 }, { "First Name": "John", "Last Name": "Bravo", "Email": "john@gmail.com", "Duplicates": 0 }, { "First Name": "Ben", "Last Name": "Simons", "Email": "ben@hotmail.com", "Duplicates": 0 } ]

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
Usernametwice 503 on at

Like (0)

Report

Hi @grantjenkins
Hmm your first suggestion could potentially work. I could get the 'Duplicates' number and create conditions based on if greater than 0 or less than 1.

Your second suggestion could work as well and may be better for what I plan to do, but I would need to pass the first name, last name and email to ensure it really is a duplicate record.

The reason I need to detect the number of duplicates is because I am setting up conditions based on if there are duplicate records or not. So if there are duplicates, I will be creating an error message that gets sent from the flow 'duplicates detected', otherwise if no duplicates are found then the flow will continue and create a new row in Dataverse based on the record details. The data in the array is coming from a CSV file.

Thanks for your help.

Was this reply helpful? Yes No
grantjenkins 11,063 Moderator on at

Like (0)

Report
It's already checking against the full text for duplicates 🙂 I'm checking this data "Jane,Tonkin,jane@gmail.com" which includes all properties.

This is probably how I'd build the flow.

Up to the Select is the same as before. Then I'd add a parallel branch that handled no duplicates, and duplicates separately removing the need for a Condition within the Apply to each which can be very slow for a lot of items. The expression used for both Filter arrays is:
item()?['Duplicates']

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

Was this reply helpful? Yes No
VictorIvanidze 13,073 on at

Like (0)

Report
Hi @Usernametwice23,

Let's suppose you array is
[ "JaneTonkinjane@gmail.com", "JohnBravojohn@gmail.com", "BenSimonsben@hotmail.com", "JaneTonkinjane@gmail.com" ]
The length of this array is 4.

After union() this array and
[ "JaneTonkinjane@gmail.com" ]
you'll get
[ "JaneTonkinjane@gmail.com", "JohnBravojohn@gmail.com", "BenSimonsben@hotmail.com" ]
Length of this array is 3. 4-3=1 is amount of duplicates.

Was this reply helpful? Yes No