nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Filtering out Inactive Records in Database View Queries

Kevin Eccles

My organization has been entering new values for constituent phone and email data while marking older data as “inactive.” This works well for what is visible in NXT, but unfortunately provides multiple duplicate rows when this is then queried in database view. I see two solutions to this problem, and I'd appreciate any assistance with either:

1) How do I filter out inactive records so they do not show up in database view? We have not yet migrated to Azure hosting for our databases, so I am stuck with more limited options for now. (Migration is scheduled)

2) Can I set limitations on what is allowed for data entry in NXT? Can I require that only one value per field is active? Can I require that only one value per field is allowed (active or inactive both)?

Thanks for any help you can provide.

Find more posts tagged with

Query Import Export

Comments

Joe Moretti

@Kevin Eccles I am not quite clear on what you asking since phones/emails being inactive and records being inactive are two different things. Advice on making phones/emails inactive, make sure you have a type of former phone & former email. This will eliminate these inactive phones turning up in lists, since you can not include former phone types in your lists.

Kevin Eccles

@Joe Moretti
I'm looking to remove phone and email information marked as inactive in NXT from a database-view query. While I would much prefer to enforce a one-per-field limit on data entered into NXT, I don't have the ability to enforce that policy just yet.

I am hoping there's a filter that can be applied to a database view query that allows me to pull only active info on a one-to-one relationship between contact info and constituent ID.

JoAnn Strommen

@Kevin Eccles Welcome to the BB community forums.

I don't know that hosting by Azure makes any difference. Inactive records as Joe pointed out is different than inactive phone/email. Inactive records will always be in the database. Inactive phone/emails are not viewed on a db record unless user checks box to show inactive.

While you can set limit to one # per phone/email type in db view, I don't see that you can for webview. (Probably part of the reason we restrict who can add data and we add it in db view.) IMO there's still a ways to go for pulling desired data in NXT.

You should also keep in mind that db query is a grouping tool. When exporting the data in the Export function, one has the option to exclude inactive, pull inactive and or primary fields to ensure best data is pulling. When I pull multiple email types in export I also use the sort option “is primary” descending so primary pulls first.

Faith Murray

@Kevin Eccles
In addition to @JoAnn Strommen's sage input, I would add that we also perform all phone/email updates in database view for another reason: the “date” field. When a person has multiple numbers, it's very useful for us to look at a record and know if the phone number was updated one year ago or 10 years ago - especially when reviewing append info for accuracy. However, when you update one phone number in webview, it changes the “Date updated” field on ALL numbers on that person's record.

Our fundraisers are not allowed to change bio/contact info on records – only our data entry ladies - and we do all data updates in database view.

Kevin Eccles

@JoAnn Strommen @Faith Murray

These are excellent insights, thanks for taking time out to reply. When I was using the export function to pull this information, it has phone and email information grouped. We have two phone number fields I'd like to have included as well as a main email. I have not been able to assign each of those fields to a particular column. If I could assign them to columns, limit to output one per constituent ID, and sort descending by update date, I think that would give me the output I'm looking for.

I also very much appreciate the tip about using database view to update information. I had no idea NXT would update all entries for a particular field if the update was performed in NXT. Thank you!

Faith Murray

@Kevin Eccles, that is doable in Export! You just have to right-click the “Phones” and narrow your criteria to select one Phone Type per export line/column. To get multiple export columns, you just have to click on the header (in yellow) before clicking on “Phone Number” in the left sidebar again. Here's one of my phone exports:

b29ab3c4fe1ae1c9708f321a24ca4a8b-huge-im

Kevin Eccles

@Faith Murray
Many blessings upon you and your house! It didn't occur to me that the GUI would have that much affect on the export query. I'm a terminal junkie and would be more comfy with a text editor and a place to run my SQL queries.

Thank you very much for your help there. I think I can work it out from here.