Plug-ins > FM Books Connector Online

Trick to using count(*)?

(1/1)

pjreagan:
I'm trying to use a QueryString sql statement to return a count of records in QB for a particular table but keep getting errors.  Plugin and Intuit documentation say that using the count(*) should work, but no luck so far.

Here's my code:
PCQO_BeginSession
PCQO_Authorize("b2F1dGhfdmVyaWZpZXI9eDB4ZGlleiZyZWFsbUlkPTM5NzgwNjMzNSZkYXRhU291cmNlPVFCTw==")
PCQO_RqNew("query"; "Item")
PCQO_RqAddFieldWithValue("QueryString"; ("SELECT count(*) FROM Item WHERE name = 'Studio A'"))
PCQO_RqExecute
PCQO_SGetStatus

The SGetStatus ruturns:
CODE: 1
SEVERITY: Info
MESSAGE: No results found
SOURCE: PCQO_RqExecute

But if I change "count(*)" to just an asterisk, then the code successfully returns a record.  So the session is OK and the value is present in the item table of my QB file.

What am I doing wrong?  Have I overlooked something in the documentation?

Thanks in advance for any help!

Jerel Malong:
If you don't need the Where reference, you can try utilizing the SCountEntities function as that provides the same functionality as the QueryString Select statement.

In the mean time, I will do some further testing to investigate the count(*) feature.

Chris Turner:
I performed some testing with the "count(*)" operator with the following calls, and was able to successfully execute the request to count all entities that matched the query conditions below. However, I did also uncover a bug in the plug-in when getting the result of that query.

The script code I used was:
PCQO_BeginSession
PCQO_Authorize( $$SessionTicket )
PCQO_RqNew( "Query" ; "Item" )
PCQO_RqAddFieldWithValue( "QueryString" ; "Select count(*) From Item Where Name = 'Amaretto'" ) // "Amaretto" is a simple product in my QBO test company
PCQO_RqExecute

The execution was successful, but there was no direct way to get the count through this method. This would be the bug that was uncovered.

As Jerel mentioned in his post, you can use the PCQO_SCountEntities( EntityType ) supplemental function in order to get the count of the entities. If you need to use a where clause to limit the records you wish to acquire from QuickBooks Online, you can use the following query string to get the limited set, and then use a short loop in the script to count the entities. It's a little extra work, but the end result will be the same.

"SELECT Id FROM Item WHERE Name = 'Studio A'"
(then loop and use PCQO_RsOpenFirstRecord and PCQO_RsOpenNextRecord to iterate over the response set, setting a counter variable and incrementing it with each record)

Thank you for bringing this to our attention. We have added this to our issues and enhancements for the plug-in, and expect to have the issue addressed in the next release of the plug-in.

pjreagan:
Thanks Jerel,
I'm sure the SCountEntities function will be useful elsewhere in my system.  But the current issue is determining if an item is already created, and if so, how many times does it appear.  In theory QB only allows an item name to be used once, but I'm always curious to double-check.


Thanks Chris,
Yes, once I stopped getting errors, I was going to ask how to get the returned count value.  In the mean time, that looping idea could work.  BTW, even cutting/pasting your code (changing the name to something that's in my item list) still produces the error for me.

'Will look forward to the next release!

Navigation

[0] Message Index

Go to full version