Hi There,
I have a table with a list of customers called “Webhook”, and I need a way to identify where an entry is duplicated based on identical name, email and phone values. Somehow I would like the 1st instance of the customer to not be identified as a duplicate, just any further entries.
I am also hoping to use the same expression to enter “duplicate” into a “status” column within the table. And also a virtual column to count only unique customers…
As always any help would be incredibly awesome!
Thanks guys!
Would this give you what you want?
COUNT(FILTER("Duplicates",[webhook]=[_THISROW].[webhook]))
If you want to check multiple fields. then you should OR your Count() > 1 conditions.
→ OR(COUNT(name expression)>1, COUNT(email expression)>1, ,)
1 Like
Thanks @TeeSee1 , that works for the number of duplicates. What about the format rule to highlight every instance of a duplicate except the first. (As in the 1st entry of a customer is valid, but every one after that is a duplicate)
using MINROW, I believe it can be done. give me some time to play around
1 Like
This is what I have come up with
The last check expression is..
AND([Dup Flag], [_THISROW]<>MINROW(“Duplicates”,“_RowNumber”,[webhook]=[_THISROW].[webhook]))
1 Like
Thanks, thats awesome!! Could I use that in a “status” column formula to enter “duplicate” as the value somehow?
IF(, “duplicate”, “”) should do it.
Your Status is a text col, correct?
One thing to be aware is that if there are many rows in a table across which the format rule expression works, then the multirow format rules can be expensive and slow down the app. The format rules are computed real time on the screen.
2 Likes
It’s an enum (that contains “EGB Duplicate” as an option), so Ive tried:
IF(AND([Duplicate Flag], [_THISROW]<>MINROW(“Webhook”,“_RowNumber”,[email]=[_THISROW].[email])),“EGB Duplicate”,“”)
But the status column remains blank, but it does work in the formating rule…
It is a simple IF statement based on the outcome of the expression.
I do not see why it should not work..
Could you try changing it to TEXT, just to see if it works, even though I tried ENUM an it works…
Just for debugging purpose, could you create a VC with
AND([Duplicate Flag], [_THISROW]<>MINROW(“Webhook”,“_RowNumber”,[email]=[_THISROW].[email]))
and see if it comes out OK?
and may be put an expression in your status
IF ([this vc], “Duplicate”,“”)
[this vc] col, is it showing the correct outcome?
and if you have
IF(TRUE, “Duplicate”,“”)
this should display “Duplicate” for all the rows.
Just try these things. Honestly I have no clues as to why it is not working …
I think I might know why now.
The status is a real col. And Appformula is recalculated not as other rows are edited. (I myself have this confuse me sometimes..)
So you should make the status col a virtual col.
At least add a VC and see if it works for you.
can you show me the results in the VC [this vc]?
Argghh, my apologies, when I reopened the VC has worked, must have not refreshed! still nothing on the real column, but thanks for all your help!
One last thing, if I was to add phone number (so a duplicate is where both email AND phone are in another row) to this, how would I do that?:
AND([Duplicate Flag], [_THISROW]<>MINROW(“Webhook”,“_RowNumber”,[email]=[_THISROW].[email]))
Thankyou so much @TeeSee1 !!
1 Like
| phone | AND Duplicate | OR Duplicate | |
|---|---|---|---|
| aaa | 001 | Y | Y |
| aaa | 001 | Y | Y |
| aaa | 002 | N | Y |
I was not sure if you wanted the AND Duplicate or OR Duplicate condition.
For AND Duplicate conditions you replace
[email] = [_THISROW].[email]
with
AND([email] = [_THISROW].[email], [phone] = [_THISROW].[phone])
wherever it appears.
For OR Duplicates,
with
OR([email] = [_THISROW].[email], [phone] = [_THISROW].[phone])
I think this should work…