Method:API Q&A

Re: Limitations of MethodAPISelect_XMLV2

furrywombat — Sat, 28 Jun 2014 15:16:26 GMT

So, rather than simply creating a LEFT JOIN or INNER JOIN query, I create an entirely new field that links to the table in question. Unfortunately this would result in about 30-50 hours of sync time every time I need to create a new association given the size of our database. You guys need to make JOIN available or at least a pseudo JOIN available in the SELECT query at some point. It's making my job extremely difficult. Feel like I'm working with an abacus over here, man.

Re: Limitations of MethodAPISelect_XMLV2

Method_Chris — Mon, 16 Jun 2014 14:48:12 GMT

Apologies for the delay.

The query functions are for specific tables. You cannot join tables directly in the API. However, in reality, the tables you see in Method are actually Views behind the scenes, and you can add Linked Fields (via Customize > Tables / Fields) that show values from other related tables. For example, on ItemReceiptLineItem, you can expose fields from ItemReceipt table, like the ItemReceipt.Vendor. Pretty safe to say that if the ItemReceipt.Vendor is blank, there is no association between the ItemReceiptLineItem and an ItemReceipt.

Thanks

Chris

Limitations of MethodAPISelect_XMLV2

furrywombat — Fri, 06 Jun 2014 20:22:11 GMT

What exactly are the limitations of this function? Is it somehow possible to perform a more complex query... for example, to query the ItemReceiptLineItem table for created line items where ItemReceiptRecordID does not exist in the RecordID column of the Item Receipt table? Currently we are just querying both, creating temporary versions of each query in MySQL tables on our server and running the query on our end... but it feels like we're using a pistol to squash an ant.

The documentation is pretty limited... hoping to get a better understanding of how to perform more complex queries using this API function.

Thanks in advance!

- FW