By using this website, you agree to our Terms of Use (click here)
Good Afternoon,
I recently found myself hired because of my robust experience writing SQL programs. However, I'm actually working with a deployment of Acumatica. I'm dealing with JAMIS, a re-seller of Acumatica. I got a bit excited once I realized I could use an ODBC connector to parse Odata aggregates. There appears to be a bit of a problem with the OData feeds. While some work, others do not, even though the report works in the Acumatica under my login. The instructions online make it appear to be very easy, which when it works, it is. Is there a common list of problems or suggestions that commonly interfere with OData reads? Thanks in advance for any assistance.
Kind Regards,
Kitt Parker
Will the same OData feed give an error, then 30 minutes later (for example), start working?
Or does the failure happen consistently for only some inquiries?
What's the error message?
Thanks for the prompt reply, I'll have more info this coming Monday. I came down with the Flu and have been knocked down for the last few days. =/
Ok, I believe the problem has to do with how OData works and some time-out setting must exist somewhere. Odata sets with small amounts of data work fine, large data sets seemingly spin until a disconnect occurs. Knowing if there is some special method for dealing with large Odata sets would be beneficial. That aside, I have a couple questions that deal with acumatica General Inquiry specifically. There is a field on a PX.Objects.GL.Batch table named FinPeriodID. The first thing I tried was using an expression to set a condition for a specific date. This date appears to be in the format MMYYYY, when I type 122013 directly in, it works fine. However, when I try to use an expression that will resolve to the same input, acumatica appears to not make the connection and I get 0 results back.
Below is used in condition. Results in string "122013"
=CStr(Month(DateAdd( Now(),'m', -1)))+ CStr(Year(DateAdd( Now(),'y', -5)))
Hopefully you do not tell me acumatica is incapable of resolving an expression as a condition.
Can you tell how long it takes before failing?
I will say though that OData is pretty slow. Why that is exactly I'm not sure, but I think it partly has to do with the fact that it needs to move the data from the Database through the Web Server, then out to you, rather than directly from the Database like you get with ODBC. Also, regarding the protocol itself, one very knowledgeable person described it to me as "chatty" so the data size is probably larger than it needs to be.
One thing you can do though to get the Generic Inquiry to run faster is feed it with a SQL View rather than using the regular Acumatica Data Access Classes. That's what I'm doing with Query Quarry. You can download the Customization Project on the bottom of this Query Quarry page (click here) to see how to do that.
For your expression question, try putting a dash between the month and year, also padding the month with a leading zero if needed like this:
=Right('0'+CStr(Month(DateAdd( Now(),'m', -1))),2)+'-'+CStr(Year(DateAdd( Now(),'y', -5)))
Hi Tim,
Thanks for the response. I tried your expression in the condition and it generated the same result, I also dropped it into the result set to confirm the correct resolution was occurring. The other oddity with this field specifically is when I attempt to use the ODBC connection to select with where = "122013" or "12-2013" it fails to select as well. That just about drives a nail through automatic report generation based on specific date ranges relative to current date. It would be nice to create custom SQL views as you touched on, however, our current re-seller does not permit database connections and forces us to strictly use the provided web interface with the claim it handles everything we need. One of the current pitfalls of cloud hosting with some providers, they lock you out of your own data...
What data field are you trying to match the expression value to? Is it a PostPeriod field?
Tell me more about your ODBC connector. Are you using ODBC to read Acumatica data through the OData protocol? Is this a third party tool? If so, I'd love to know the name. I recently learned about the Task Factory Pro connector (click here) that allows you to pipe a REST API into SSIS. I haven't tried it with OData yet, but since OData is just a REST API, I'm pretty confident that it will work.
Import the Query Quarry Customization Project that I mentioned above and open it. You'll see that you can actually push a SQL View into Acumatica through the web interface using the Customization Project. You still have to tinker with creating the SQL View in your own environment, but you can push it into the SaaS environment using the Customization Project, all through the web interface. Doug Johnson has more details on how to do this in this post (click here).
It is the FinPeriodID or the TranPeriodID on the dbo.Batch table. Also, the ODBC connector is a third party tool provided by Cdata. https://www.cdata.com/drivers/odata/odbc/. I was able to successfully query some of the odata streams with it. I will be looking at the Query Quarry, thanks for the additional information. Is there a special method for interacting with a PostPeriod field?
You want Batch.FinPeriodID. I just remembered that it's actually stored in the database as yyyymm.
So I just used the following formula on the Conditions tab and it worked (I changed it to only go back one year because of my demo data):
=CStr(Year(DateAdd( Now(),'y',-1)))+Right('0'+CStr(Month(DateAdd( Now(),'m', 0))),2)
Hi Tim,
Thanks again, don't know what I would do without you lol. No one at the local level has a grasp on how the data is being handled within the system.
It's a learning curve, let's get there together. We all have a piece to contribute. Thanks for the info about your ODBC tool.