Adding an arbitrary sequential counter to a query view

Mitchell Gibbs

I'm trying to add an ability to achieve the equivalent of a “SELECT TOP (X)” records in a Selection to restrict the number of records returned. I'm striking out.

I've come close by creating a Query View Extension using an un-ordered ROW_NUMBER. When run in SSMS it seems to do exactly what I'm looking for. However, when used in CRM, it returns the same counter values for each Constituent consistently - not an arbitrarily numbered list. It's unclear to me what the counter becomes tied to. It doesn't seem relate to the clustered index, sequenceid or anything else I've looked at.

From SSMS, I am able to get a similarly wrong result when I also add a pseudo RANDOM field and then sort by that - which, of course, is my ultimate goal. When I do that - the COUNTER field also becomes consistent. I'm guessing these things are related - I just don't understand how or what I might be able to do about it.

These give me what I'm looking for:

SELECT TOP 100 ID, LOOKUPID, COUNTER, RANDOM FROM V_QUERY_CONSTITUENT ORDER BY DATEADDED

SELECT TOP 100 ID, LOOKUPID, COUNTER, RANDOM FROM V_QUERY_CONSTITUENT ORDER BY KEYNAME, FIRSTNAME

When run within CRM, it does not. Also, the following does not:

SELECT TOP 100 ID, LOOKUPID, COUNTER, RANDOM FROM V_QUERY_CONSTITUENT ORDER BY RANDOM

<QueryViewSpecExtension
xmlns="bb_appfx_queryview"
xmlns:c="bb_appfx_commontypes"
ID="DBC72166-990E-4029-A13C-76588BF6B27A"
Name="Constituent Arbitrary Counter Query Extension (custom)"
Description="An extension to the Constituent query view to add an arbitrary counter"
Author="PHS - Mitch Gibbs"
ExtendsViewName="V_QUERY_CONSTITUENT"
>

<AddFields>
<AddField>
<SelectClause AliasAs="COUNTER">ROW_NUMBER() OVER(ORDER BY (SELECT NULL))</SelectClause>
<OutputField Name="COUNTER" Caption="Counter" DataType="Integer" />
</AddField>
<AddField>
<SelectClause AliasAs="RANDOM">CONVERT(INT, CRYPT_GEN_RANDOM(4))</SelectClause>
<OutputField Name="RANDOM" Caption="Random number" DataType="Integer" />
</AddField>
</AddFields>

</QueryViewSpecExtension>

Any ideas? Or, has anyone achieved anything similar? Thanks!

Crystal Bruce

@Mitchell Gibbs Hi! We have a forum dedicated to Blackbaud CRM API's which might get you the developer help you need. Would you like me to move this over to here: Blackbaud CRM™ APIs? Thanks!

Mitchell Gibbs

@Crystal Bruce No. This isn't Sky or API related. This is CRM SDK-related. I would think this Developer forum would make the most sense. I appreciate the thought though! Thanks!

Will Mercer

@Mitchell Gibbs I'm only guessing but I imagine that the SQL is being interpreted differently when being executed the web app. Have you tried making this determenistic? I've not tested to see if this generates the randomness you are looking for but this may be helpful:
ORDER BY (SELECT 1)
or you could try this which I think is more likley to work for you
ORDER BY (SELECT newid())

I've not tested either of these in SDK so I don't know if you will hit the same issue. The only other way I can think of approaching this is to stage the data in another table using a global change and building this as a child view. Thats not great though of course! I'm sure someone else will have a better suggestion than that.

David Wanless

Dear @Mitchell Gibbs,

We'd find something like very useful. I've experimented with it before and had no luck.

There is a Blackbaud ‘idea’ for it, so please add your voice to that. See https://bbcrm.ideas.aha.io/ideas/CRM-I-3279.

I did build a limit counter into our bulk add Interaction Global Change, but even there I couldn't get it to choose the top n, it's just any n. It's still occasionally useful, but a general feature in Query would be much better.

Ciao,

David.