Query Matching Multiple "Constituency Date From" Entries

Aaron Cobb

We had a problem where a user globally added a Constituent Code to our entire database for the year without properly setting up the global add.  Now we have multiple users who are showing up with two Constituency From Dates with the same code, and it's making a few different queries show up oddly.  When I try to run a query specifically to find the users with two Constituency From Date field entries, I'm not getting any matches, even when I open the user record and see that the From Dates are listed twice.  (For example, a user has the Consituency From Date of 1/15/2015 and 1/20/2015, but when I run a query with "Constituency From Date equals 1/15/2015 AND Constituency From Date 1/20/2015" there are no matches, despite there being hundreds of users that theoretically should match this.)



Help!  Does this field just not allow multiple matches?

JoAnn Strommen

Just to be sure, are you querying constitueny, not primary constituency?  It would seem like it should work but I got unexpected results too.  Can you try using fuzzy date of 1/2015?  It looks like it pull them then but will pull in any extras also done that month.  Maybe extras could be eliminated by including constitutent code in criteria also.  Just a thought.

Aaron Cobb

JoAnn Strommen:

Just to be sure, are you querying constitueny, not primary constituency?  It would seem like it should work but I got unexpected results too.  Can you try using fuzzy date of 1/2015?  It looks like it pull them then but will pull in any extras also done that month.  Maybe extras could be eliminated by including constitutent code in criteria also.  Just a thought.

I'm already searching by the specific Constituent Code.  We have other quereys that work with the Constituency From Date that are pulling two matches for each of the users with two dates, so I know the field is working fine with one date, but when I search for any two dates I get zero matches every time.  It seems like either zero matches for multiple or multiple matches for the code itself.  Brutal.



I'll try playing around with different fuzzy dates and month searches, but I am hoping to find a specific list of users with two or more dates for this specific code.  (Or any code, really - there's no reason any user should have a Code with multiple From dates in our database; user error led to this.)  I have an exported Excel spreadsheet that I have set up to compare users with the same name and different dates, but it's very tedious to go through the list of approximately 1,000 people, switching back and forth between RE and Excel, to edit each one manually...  Even just an idea about searching for users who have duplicate consituent codes would be helpful, since the specific dates aren't the problem.

Jody Johnson

I just tried this too, and I can't refine my results to only show the constituency code I want, even if I add the criteria of number of constituencies to be greater than 1. There doesn't seem to be an option of duplicate constituency codes. 



But there is a way you could do it with exporting the data. It will require a few steps, but it will give you the results you need.

1. export a query with all the constituents with that constituent code, include constituent ID, Consituent Code and Date From, Date To
2. Create a pivot report with that excel file
   1. you will want to add consituent ID to the rows field, and too the values field, with the property of count
   2. Add constituency date from and date to to the columns field
3. copy all the data and paste into a new worksheet (choose values for your paste option, as this will keep only the data, you get this option when you right click to choose paste), you might need to format the date columns to be a date and not text
4. Now sort your list by  grand total number and delete any that have the number one
5. the remaining names have this constituent code more than once
6. take this list and create one file with just the constituen ID, and change all the dates to  01/01/1800
7. Import this list and run a query for that date from and that constituent ID
8. With the query you get, use your global delete to remove the consituent code from the record (as you haven't deleted the duplicates, it will remove both)
9. Go back to your original list from step 5, you will then see both date from dates for a record in one line, delete the date you don't want, 
10. take that list and import it and it will then add the constituent code only once to a record

This is still a few step, but it shouldn't take more than a day to fix the data.



Hope this helps!



Jody 

Vanessa Taylor

I don't have a good answer for you, but I thought I would let you know you aren't alone - we have found querying by constituency code to be a mess that often returns unexpected and/or wrong results. I haven't investigated it further or contacted support because it's just not at the top of my list of things to deal with right now, but I really think there's something wrong there!



Actually, I do have one possible suggestion - would merging two queries work? Do one for each situation, and then merge using the AND operator?

Aaron Cobb

Vanessa Taylor:

I don't have a good answer for you, but I thought I would let you know you aren't alone - we have found querying by constituency code to be a mess that often returns unexpected and/or wrong results. I haven't investigated it further or contacted support because it's just not at the top of my list of things to deal with right now, but I really think there's something wrong there!



Actually, I do have one possible suggestion - would merging two queries work? Do one for each situation, and then merge using the AND operator?

Many thanks to Jody for the excellent quasi-longterm solution, but unfortunately we need this data by tomorrow morning.  (I know, right?  Guh.)  For now we don't have the time to learn a new process for exporting and importing, especially given that messing it up could cause an even bigger problem.  Not enough confidence from the team today!



But Vanessa, your idea was excellent.  The actual number of duplicate records turned out to be a bit smaller than we had thought - about 600 users affected - and I'm in the process of manually deleting the duplicated code entries now.  It should be enough of a solution to get us through the week.  Great idea!

Vanessa Taylor

Great, so glad that worked for you! It's certainly a terrible feeling when something goes wrong with global adds, especially when it messes with a report you need ASAP!