Search results by user ID 5814

Syncing Customer/Lead rows without TimeModifiedAccounting

gbisaga — Sat, 29 Jun 2013 13:16:36 GMT

Hi, I am trying to sync over my Customers table to my own database to allow faster/more complex searches than the API allows and I am running into a problem. How do I know the latest rows to fetch from Method? What I am currently trying to do is get all rows with TimeModifiedAccounting > max(TimeModifiedAccounting) in my local table. But now I am finding that there are a bunch of Customer rows with TimeModifiedAccounting as null. Under what conditions is TimeModifiedAccounting null?

Would it be better to get all rows with RecordID > my highest RecordID? The reason why I did not do this in the first place is that I am afraid somebody will edit an existing record; I would want to sync over that updated information, but presumably the RecordID will still be the same.

Thanks!

Gary

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?

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?

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: Create Estimate via API - customer link?

gbisaga — Thu, 13 Jun 2013 21:41:49 GMT

Dave, thanks for your response also. Little by little things are sinking in. :-)

Gary

Re: Create Estimate via API - customer link?

gbisaga — Thu, 13 Jun 2013 21:24:03 GMT

Mark, thank you so much for your response. I think I was thinking of Method/QB as being more of a normalized relational database than it actually is. It sounds like all those Estimate fields like Billing and Shipping address are denormalized copies from Customers. That goes against my sensibilities of database design but I understand that QB is an old database. Thanks, I will go back to it with this model in mind.

Gary

Re: Create Estimate via API - customer link?

gbisaga — Thu, 13 Jun 2013 20:24:09 GMT

Dave, thank you for your response. I am creating my Estimate records very much like that. The problem is, when you view the Estimate record in the MethodCRM UI, the only Estimate field that is filled in is the Customer field itself - "Amy Smith" in your example. All the other fields that were present in the Customer record - Billing Address, Shipping Address, etc. - are still blank until you manually go in through the UI and re-select the customer from the Customer pulldown.

Is there any way through the API to make those other fields populate so that no user interaction with the UI is required? Thanks!

Gary

Create Estimate via API - customer link?

gbisaga — Thu, 13 Jun 2013 11:49:03 GMT

Hi, I am trying to use the API to create QB Estimates from the project management system I wrote for my client. I want to link my new Estimate to an existing Customer record, but I can't figure out how to do it. The best solution I have so far is to fill in the Estimate.Customer field with the FullName from the Customer I want to link to the estimate, but that requires a user interaction in the MethodCRM UI to confirm that's really the customer record that's required. It seems like, if I use the API, I should be able to create objects such as Estimates with no user interaction.

In general, is there some reference that tells information like this and which fields in the Estimate "table" are really views into another table (like Customer)? Thanks for your support.

Gary

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

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.