Limiting Ad-hoc Query to a specified number of results

David Wanless

We wish to run a Global Change to add Interactions to the 100 Constituent records with the highest values of an Attribute. To do so, we want to create a Query of Constituent records, ordered by the Attribute value, and limit the number of records to 100. Has anyone found a way to do this, either using out of the box functionality, or by adding a custom field akin to a row number to a Query node? We've tried various options but not found a solution. In

Blackbaud claim that this functionality exists, but the solution presented doesn't seem applicable to our situation or to be very general at all, although it may work for some specific circumstances.

I know we could create a Direct Marketing Effort and use the limiting feature there, but that's a lot of overhead for a process we want to use every week. Unless anyone has any better ideas, we'll probably just have to manually update a Query with the desired Attribute value each week, before running the Global Change.

Chris Whisenhunt

@David Wanless we have a product that this can be done with, but might be a bit of overkill for your situation. I would suggest finding a value that the top 100 have and then using a > X-1 operator to get your 100. Happy to discuss further about this need if you want to find time.

Liz Hackett

@David Wanless HI David, no idea if this will help but could you use the ‘Set Sort and Group options’ in query?

David Wanless

Dear @Chris Whisenhunt,

If I'm understanding you correctly, that's what we do when running it manually, by perusing the values and working out the cutoff, but we want to be able to run it automatically, and the value will be different every week, so I don't see how that could work for us. Feel free to explain further if I've not understood you.

Which product are you referring to, by the way?

Kind regards,

David.

Chris Whisenhunt

@David Wanless you are correct. Our product, “Velocity” can manipulate any data point within BBCRM and allows you to simply get data like you're looking for within minutes. Best part is it's schedulable just like any other business process.

I'd be more than happy to give you a working demo and if that isn't something you can work with, we can find another way to get this done for you.

chris.whisenhunt@zurigroup.com

David Wanless

Thanks, @Chris Whisenhunt. I do know about Velocity. We did look into it as part of another project.

David Wanless

Dear @Liz Hackett, thanks. I'll look into that. It's certainly not something I'd thought of. Ciao, David.

David Wanless

Dear @Liz Hackett,

That's an interesting idea, but after a brief experiment, I don't see how it would work, as adding a Sort and Group option like that would require that our output field be a COUNT, so we wouldn't get one row for each record.

Ciao,

David.

Johanna Rosenthal

Hi @David Wanless - did you ever find a solution to this problem? I've been looking to do something similar and also struggling to find a way to make it work!