using CRM.FindRecord is there a way to request only certain fields?

SOLVED

So the issue is that say the communications table is very large (which it is on a lot of production systems) and even though it is indexed using findrecord is slow as its does a select *

when i really only need a few fields.

I want to use FindRecord as it applies the security SQL so using CreateQueryObj is not a runner.

Is there some hidden way to do this (i cant see anything in the documentation)

AND/OR

specify the top x rows

OR

request only a specific set ...eg rows 11-20

To provide some context this query

select comm_communicationid
from vCommunication
where cmli_comm_companyid=846 and comm_private is null
order by comm_datetime

takes around 15 seconds in sql querymgr and returns 4583 rows with all the data

but

select comm_communicationid, comm_subject, Comm_Email, Comm_Note
from vCommunication
where cmli_comm_companyid=846 and comm_private is null
order by comm_datetime

takes about a second.

  • +1
    verified answer

    Okay I have answered this myself and figured out a way to speed things up.

    Posting here in case its useful to anyone.

    So in SQL we can use "OFFSET x ROWS FETCH NEXT y ROWS ONLY;"

    --page 1

    select *
    from vCommunication
    where cmli_comm_companyid=43 and comm_private is null
    order by comm_datetime desc
    OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;
    go

    --page 2

    select *
    from vCommunication
    where cmli_comm_companyid=43 and comm_private is null
    order by comm_datetime desc
    OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;

    In CRM's api we can append this to the OrderBy property

    var x=CRM.FindRecord("communication,vcommunication",
      "cmli_comm_companyid=43 and comm_private is null");

    x.OrderBy="comm_datetime OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;";

    Now you do need to pass up the page and number of rows from your app to build out the correct OFFSET statement

    AFTER SOME MORE TESTING THIS BIT BELOW IS WRONG

    And also...to get the correct recordcount you in fact need to do this...

    var x=CRM.FindRecord("communication,vcommunication",
      "cmli_comm_companyid=43 and comm_private is null");

    x.OrderBy="comm_datetime";

    var _recordcount=q.recordcount;

    x.OrderBy="comm_datetime OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;";

    ...by setting

    x.OrderBy

    again the query is rerun and only returns 3 rows (in this example)

    SO IN FACT WHAT YOU NEED TO DO IS WRITE A QUERY TO GET THIS VALUE....THE TRADE OFF IS THAT THIS MIGHT BE A HIGHER NUMBER THAN THE USER CAN IN FACT SEE...I THINK ITS WORTH IT MYSELF

    var _countwry="select count(comm_communicationid) as 'count' from communication where cmli_comm_companyid=43 and comm_private is null";
    var _countwryq=CRM.CreateQueryObj(_countwry);
    _countwryq.SelectSQL();
    var recordcount = _countwryq('count');