Method:API Q&A

Re: MethodAPISelect_DataSetV2 XML Examples?

Method_David — Tue, 25 Jun 2013 19:16:39 GMT

Hi gbisaga,

No you cannot, since you may have deleted records. What you can do however is a simple call where for your fields parameter all you pass in is max(RecordID) and that will give you your max recordid, then you can build your query to get all records. Or you can use count(RecordID) to get the count. Simple C# examples below

MethodAPI.Service _MethodAPI = new MethodAPI.Service();

lblMethodAPISelect_XMLV2.Text = _MethodAPI.MethodAPISelect_XMLV2(CompanyAccount, Login, Password, "", "invoice", "max(recordid)", "", "", "", "");

_MethodAPI.Dispose();

MethodAPI.Service _MethodAPI = new MethodAPI.Service();

lblMethodAPISelect_XMLV2.Text = _MethodAPI.MethodAPISelect_XMLV2(CompanyAccount, Login, Password, "", "invoice", "count(recordid)", "", "", "", "");

_MethodAPI.Dispose();

Dave

Re: MethodAPISelect_DataSetV2 XML Examples?

gbisaga — Tue, 25 Jun 2013 17:25:27 GMT

Thank you, Dave - you have definitely been there answering my questions, and I do appreciate it. Can I depend on RecordID to be a count of rows in the table? If I can, I could deal with fetching where "RecordID >= MyLastRecordID and RecordID < MyLastRecordID+1000" - that would be much better than the time-based query I'm doing now.

Re: MethodAPISelect_DataSetV2 XML Examples?

ImTheirWebGuy — Tue, 25 Jun 2013 16:24:23 GMT

Absolutely, Dave. Thank you again for your help.

Re: MethodAPISelect_DataSetV2 XML Examples?

Method_David — Tue, 25 Jun 2013 16:18:55 GMT

Hi ImTheirWebGuy, gbisaga, and LaCrews,

For any .asmx page you can click the name of the available operations and that will take you into operation specification. This includes a description of the soap envelope, which is what you were asking for ImTheirWebGuy. This is standard. So you can go to https://www.methodintegration.com/MethodAPI/Service.asmx and click on each operation to see the operation specification.

gbisaga, for your issue of retrieving records from the customer table. The api has a 1 million cell limit for data retrieval. Other than that really its whatever filter you want on the where clause will get your data as long as you don't exceed 1 million cells. If you just need all records, have you considered using recordid? Its an identity field that all your tables have in method. And then you can just have your where you pass in iterate. e.g. first call would have "where recordid > 1 and recordid < 1000" in the where clause. Second call would have "where recordid > 1000 and recordid < 2000", etc. We have not forgotten your request for count...but right now its not happening.

Mark, thanks for the love!

We internally actually use the api every day. An example would be its part of our billing process. If there are new functions/features needed you can always make a post asking for them. We are always looking for suggestions on how to make the API better.

You guys also cited the lack of php examples as something we are deficient on, and something we should have going forward. Why don't you guys send me an email and we can discuss the possibilty of collaborating on some php examples that we make available as part of our documentation. It can be a learning experience for us .Net people.

Dave

Re: MethodAPISelect_DataSetV2 XML Examples?

ImTheirWebGuy — Tue, 25 Jun 2013 14:59:48 GMT

Thank you thank you thank you gbisaga! This is EXACTLY what I was looking for! https://www.methodintegration.com/MethodAPI/service.asmx?op=MethodAPISelect_XMLV2

Re: MethodAPISelect_DataSetV2 XML Examples?

ImTheirWebGuy — Tue, 25 Jun 2013 14:34:17 GMT

Hey, I'm totally down with a hangout IRL, and I'll keep the beers flowing the enire time (as long as we can talk shop). :D

Re: MethodAPISelect_DataSetV2 XML Examples?

gbisaga — Tue, 25 Jun 2013 14:30:38 GMT

By the way, I should give a shout out to Mark Crews also - he has definitely been helpful in the past, as he is here. Sorry for forgetting about you, Mark. Maybe we should form the Method API Real World (tm) user group. :-)

Re: MethodAPISelect_DataSetV2 XML Examples?

ImTheirWebGuy — Tue, 25 Jun 2013 13:42:38 GMT

And David, I really do appreciate your responsiveness, but gbisaga has nailed it: it seems like there's not really an understanding of how the API will actually be used in the real world. I want to implement this software in more places, but further API development has to be part of that.

Re: MethodAPISelect_DataSetV2 XML Examples?

ImTheirWebGuy — Tue, 25 Jun 2013 13:38:20 GMT

Outstanding! I'm actually working from within a WordPress front end, but I'm sure there's useful structure in your code that would help answer some of the questions I'm having. I don't mean to sound frustrated, but I just keep hearing the same answers over and over which don't actually address my questions. I'm not sure why it's so difficult to get across that the core transaction of this API is an XML exchange, and I just want to know what values and syntax are needed in that core exchange (since the API blows out anything other than an exact match with a generic error). Sending you an email now.

Re: MethodAPISelect_DataSetV2 XML Examples?

gbisaga — Tue, 25 Jun 2013 13:31:13 GMT

I totally feel your pain because I am in the same boat. There are some folks here on the forum who are trying to be helpful (Dave from Method being one), but often I find we are not speaking the same language. I suspect the biggest problem is that Method people have never really had to use their API to solve real business problems, so they are not quite sure what to do with them. Sounds like we are doing much the same kind of thing. I am syncing the customer table and then generating Estimates based on a workflow from my client's online engineering system, but will soon have to add generating customers, leads, etc.

Here's an example of the problems I'm having with the API: I want to sync over the Customers table to my own database because I want to do my own searches, because Method's search interface is slow and not very capable. Thus, I want to grab a number of records at a time and process them, rinse and repeat. Turns out there's no way to limit queries by a count; all you can do is limit by some other criteria like date, and hope there's no more rows than the API can handle. This clearly makes for very slow and very difficult syncing, but I've not had much luck in communicating this need to Method folks here in the forums.

Regarding your problem, I have slogged through the API interface in PHP myself. Are you using CodeIgniter? I wrote a CI library to access the Method API. It's not anything spectacular, but I would be glad to share the code with you if you would like it. Let me know if you would like my code and/or if you just want to share Method/PHP experiences. :-) My email is gbisaga at gmail.

Re: MethodAPISelect_DataSetV2 XML Examples?

LaCrews — Tue, 25 Jun 2013 12:57:00 GMT

Hi ImTheirWebIT

I'd have to say that I've gotten great support from these guys.

Have you tried using MethodAPISelect_XMLV2 instead of the DataSet Select? Instead of returning a .Net object it returns raw xml.

From Method's API documentation here's an example xml response for the Select_XMLV2 method

<?xml version="1.0" encoding="windows-1252" ?> <MethodAPI response = "Success"> <MethodIntegration Table="invoice"> <Record> <TxnDate>6/24/2008 5:00:00 PM</TxnDate> <Subtotal>44.0000</Subtotal> <RefNumber>33176</RefNumber> <Customer>Bob Crenshaw</Customer> <BalanceRemaining>44.0000</BalanceRemaining> </Record> <Record> <TxnDate>7/22/2008 5:00:00 PM</TxnDate> <Subtotal>188.0000</Subtotal> <RefNumber>33976</RefNumber> <Customer>Nat Chapman </Customer> <BalanceRemaining>50.0000</BalanceRemaining> </Record>

HTH

Re: MethodAPISelect_DataSetV2 XML Examples?

ImTheirWebGuy — Tue, 25 Jun 2013 03:41:50 GMT

Thanks, Dave, but aside from learning that Like is supported and joins are not, that just wasn't very helpful. I've used all the documentation at http://www.methodintegration.com/Method-API-for-QuickBooks-CRM.aspx, but don't see much in the way of actual data examples in there. You offer some code examples in .Net and VBA, and that's it. To figure out the actual data required by your API, I would have to code this up in C# using your examples, and then spit out the XML output, then code my app with the correct specs.

Whether C#, PHP, Ruby, or any other language, the end result is an XML data packet. The coding language doesn't matter at all; your strict endpoint rejects natively constructed SOAP XML packets via PHP's SoapClient, so I can't even try to mimic your method. I literally need to know the exact XML data structure to create my Method integration. See here for the long and painful process by which I figured that out: http://imtheirwebguy.com/cforms-to-methodcrm-getting-dirty-with-soap/

I'm not asking for PHP code samples (although a bunch of your other users are, and no surprise, given how many more small businesses use LAMP stacks versus IIS, but that's a business model issue for your execs to figure out), but given that you're offering an API, if would be really nice if you guys supported it with examples of the actual data necessary to complete the transactions.

To top it off, when I pay for premium support, and the first thing the support tech says is "as consultants, we don't support the API", it really seems like that endpoint is just a kludgey add-on as opposed to a core value proposition we're paying for.

I have one client halfway through a substantial integration with this software, and easily a dozen more who COULD benefit, if I was confident that we could get some support that is a little more than "RTFM" and "Google how 2 SOAP noob". Providing well formed XML examples for each of your API calls would be a really great start down that path.

Re: MethodAPISelect_DataSetV2 XML Examples?

Method_David — Mon, 24 Jun 2013 22:40:39 GMT

Hi Matt and ImTheirWebGuy,

Good catch Matt.

ImTheirWebGuy you're gonna want to look at the documentation here http://www.methodintegration.com/Method-API-for-QuickBooks-CRM.aspx

- like is supported

- (referr ing to t he documentation) datReturnedDataSet is a dataset passed in by reference....see my example above for the c# way

- Joins are not supported via the api, neither are subqueries. This is to guard against users entering anything and causing problems (not to mention sql injection among other things).

Dave

Re: MethodAPISelect_DataSetV2 XML Examples?

ImTheirWebGuy — Mon, 24 Jun 2013 21:54:22 GMT

Hi Matt!

OK, that solved the initial error message. Now, how do I use the rest of the fields?

- Can I use a "Like" operator in the strWhereClause (or any other fuzzy operator?)

- Putting any value at all in datReturnedDataSet causes an error; what's the syntax for value in this field?

- Since we're constructing SQL, can I use a Join statement in my Table value?

I'm not looking for someone to write my code for me, but some real world examples of actual data in each of your API calls would be a really nice thing to have for third party developers, no matter what language they're building in.

Re: MethodAPISelect_DataSetV2 XML Examples?

Matt — Mon, 24 Jun 2013 21:13:51 GMT

Try using CompanyName='4th' with single quotes instead of double quotes.