Page MenuHomePhabricator

Civi dedupe: CID range + lifetime$ possible to combine?
Closed, ResolvedPublic

Description

We've had great results using
civicrm/contact/dedupefind?reset=1&action=update&rgid=13&criteria={%22contact%22:{%22id%22:{%22BETWEEN%22:[50000,80000]}}}&limit=10000
to dedupe ranges of CIDs. Is there a way to modify this to exclude donors who've given above $500 USD lifetime?

Event Timeline

MBeat33 created this task.Sep 19 2018, 8:23 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptSep 19 2018, 8:23 PM
Ejegg added a subscriber: Ejegg.Oct 4 2018, 2:39 AM

So, the way to add criteria is just a comma and the next condition - here's a first and last name dedupe search.
civicrm/contact/dedupefind?reset=1&action=update&rgid=15&criteria={%22contact%22:{%22last_name%22:{%22IN%22:[%22Eggleston%22]},%22first_name%22:{%22IN%22:[%22Elliott%22]}}}&limit=1

Now we just need the field name to use for the lifetime total. I bet it's somthing like custom_XXX

So something like:

civicrm/contact/dedupefind?reset=1&action=update&rgid=13&criteria={%22contact%22:{%22id%22:{%22BETWEEN%22:[50000,80000], [LIFETIME TOTAL < $500]}}}&limit=10000

maybe we can draw on Eileen's wisdom next checkin

So the field is 132 so the field name is 132

Eileenmcnaughton added a comment.EditedMar 6 2019, 8:15 PM

so in theory it should be

civicrm/contact/dedupefind?reset=1&action=update&rgid=13&criteria={%22contact%22:{%22id%22:{%22BETWEEN%22:[50000,80000],%22custom_id%22:{%22BETWEEN%22:[1,499]]}}}&limit=10000

I used BETWEEN 1 & 500 above because until we sort Civi has a problem with 0 in a between range

Eileenmcnaughton added a comment.EditedMar 6 2019, 8:16 PM

Or possibly

civicrm.frdev.wikimedia.org/civicrm/contact/dedupefind?reset=1&action=update&rgid=13&criteria={%22contact%22:{%22id%22:{%22BETWEEN%22:[50000,80000],%22custom_id%22:{%22%3C%22:[499]]}}}&limit=10000

OK this is the json I think

{
"contact": {

		"id": {
			"BETWEEN": [50000, 80000]
		},
		"custom_132": {
			"BETWEEN": [0, 499]
		}

}
}

Eileenmcnaughton added a comment.EditedMar 6 2019, 8:19 PM

OK so this proves it 'works' - in that we get the known zero error

/civicrm/contact/dedupefind?reset=1&action=update&rgid=13&criteria={%20%22contact%22:%20{%20%22id%22:%20{%20%22BETWEEN%22:%20[50000,%2080000]%20},%20%22custom_132%22:%20{%20%22BETWEEN%22:%20[0,%20499]%20}%20}%20}&limit=10000

I'm pretty sure this is correct

civicrm/contact/dedupefind?reset=1&action=update&rgid=13&criteria={%20"contact":%20{%20"id":%20{%20"BETWEEN":%20[50000,%2080000]%20},%20"custom_132":%20{%20"BETWEEN":%20[0.001,%20499]%20}%20}%20}&limit=10000

@MBeat33 can you try that last one^^

Restricted Application added a subscriber: Liuxinyu970226. · View Herald TranscriptApr 30 2019, 8:06 PM
Eileenmcnaughton closed this task as Resolved.Jun 25 2019, 8:23 PM