Method:API Q&A

Re: Limit results from MethodAPISelect_XMLV2

Method_David — Wed, 13 Feb 2013 16:18:46 GMT

Hi gbisaga,

Don't forget all tables have a column called recordid so you can always filter on that

Paging has never come up before.

Row_Number and Top would be part of the select statement which we do not allow to be directly set by users since the security risks are too high.

Dave

Re: Limit results from MethodAPISelect_XMLV2

gbisaga — Mon, 11 Feb 2013 22:48:35 GMT

Thanks, David, but actually I did understand what you are saying. I know that every database I have used has some way of limiting queries for the purposes of paging through the results. Pagination is logically separate from a set of filtering criteria in that it is an additional criteria on top of the filtering. MySQL does that with the LIMIT clause; Oracle lets you put ROWNUM in the where clause. I see that SQL Server is pretty messy, giving you a "TOP N" or a ROW_NUM() function in the select clause. I've played with these but they don't seem to work in MethodCRM.

The prototypical example usage of this is a paged presentation of data; you start out by saying "give me THE FIRST N records that meet such-and-such criteria", and then I'd display those N records on a page; when the user clicks "next page", I fetch the NEXT N records, etc. You have an example on methodintegration.com site: click Customers, which will take you to a E xisting Customer Contacts List page that lists the first 10 records, with a "Next page" button at the bottom. I want to be able to do something like what you do on the Existing Customer Contacts List page. Or, let's turn this around: how would you do such a page using the API? Maybe there's something I'm missing.

Sorry for being such a pain, but I really need to do something like that and I can't believe it's never come up as a requirement before. Thanks for your support.

Re: Limit results from MethodAPISelect_XMLV2

Method_David — Mon, 11 Feb 2013 22:23:27 GMT

Hi gbisaga,

I think you misunderstood my response....using a date field was just an example.

You can filter by any combination of fields in the table. If it is there you can add it to the where clause with a conditon.

So if you had a string field, a numeric field, and a datefield you can build something like.

myField1 = 'Bob' and MyField2 > 5 and MyField3 > '2013/feb/11' and myField3 < '2013/feb/15'

Pretty much if you can do it in regular sql, you can do it in method

Dave

Re: Limit results from MethodAPISelect_XMLV2

gbisaga — Mon, 11 Feb 2013 20:44:12 GMT

Hi Dave, thanks for your response. It's not that I specifically want to use the LIMIT clause, but rather that I want to limit the number of rows that comes back. The result could return tens of thousands of rows, which is a lot of data to process at once. I can't really use a date clause like you suggest because I am syncing with past data and I have no idea what the dates of the records will be. Anything you can think of would be helpful. Thanks.

Re: Limit results from MethodAPISelect_XMLV2

Method_David — Mon, 11 Feb 2013 20:07:38 GMT

Hi gbisaga,

We don't run MySQL we run SqlServer...so in a word no....since limit is not a part of standard sql.

However what you can do to limit data is use the where clause to filter data. For example I might have a table with a date field and only want orders from today onwards. So I might pass in to the where clause something iike

MyDate >= '2013/feb/11'

Dave

Limit results from MethodAPISelect_XMLV2

gbisaga — Mon, 11 Feb 2013 19:54:01 GMT

Hi, I am trying to call MethodAPISelect_XMLV2 but I don't want all the records because that would be quite a lot. Is there any way I can limit the results I get, similar to a MySQL "limit" clause?