Lubos, I am trying to use api to pull back a TB (General Ledger Balances) via the api to integrate with my Business Forecasting package 4CastPro
.json does not work on Summary and I cannot see how to use api to actually run a tb report? It just lists the Trial Balance reports?
Reports are not stored in the form you see displayed. What are saved are the definitions of reportsâcolumn titles, date ranges, and so forth. The report data itself is synthesized fresh from the underlying transactions every time you view the report. So it sounds like you are trying to recover something that does not exist.
It seems that adding .JSON at the end of the URL doesnât work for this kind of report. Maybe @lubos can add this undocumented option also to this report.
@Davide, if you havenât already solved getting the data from custom reports, it is possible to get this data by using the old API and the approach @Ealfardan came up with, see API using excel. Trick is to have checked âShow custom field as columnâ so that custom fields appear also in the JSON data in Excel. But of course this entails all the records for each module as it can not be confined to a timeperiod.
out of curiosity, you mentioned it works with trial balance, can I ask how you extract the JSON data for the trial balance at some specific date ?
So the important bits are, you will need to retrieve the Key for the report via the /api/ entrance first. Then call the Front End view for the normal trial balance report but just add â.jsonâ to the url destination. e.g. /trial-balance-view.json?Key= instead of /trial-balance-view?Key=
Above was tested on ManagerServer v 20.10.89 running behind nginx which I use instead of caddy.
Note: These queries no longer work directly to the localhost:port install and only work with a reverse proxy setup in front of it. This has been the case since 20.10.1. Version 20.10.0 being the last version that would work directly to localhost:port.
@MarkLL, there doesnât seem to be any specific date set in the Curl code for the JSON Trial balance report. Do you know, does the API syntax in Manager have such a parameter ? or just extract the date as set in Managerâs Reports module ? I must admit Iâm not programmer and I donât know CURL command line so I set forward below what I am trying to do to get the Trial Balance from Manager with API and hopefully it sheds some light
I set up a test company called NorthwindAPI in the Manager cloud (expires in next week if it helps to try this out). The following is the request headers info:
Authorization or Key from the API entrance: Basic YWRtaW5pc3RyYXRvcjphOGE5MjVkNWM3
Url: alteryx.manager.io (only one company in this cloud or NorthwindAPI)
Path: Tm9ydGh3aW5kQVBJ (NorthwindAPI)
Here is what I did in Excel: select Data / New Query / From Other Sources / From Web
Hit Ok and then hit Load. Excel successfully made the API connection to Manager in the cloud. Below is the code Excel Power Query made automatically based on this connection to Manager.
Below is the code in Excel Power Query I have been working with trying to extract the Trial Balance report from Manager. It is based on the Excel Power Query code @Ealfardan set forward, see link earlier in this thread.
@MarkLL, I plugged in the parameters you set forward in your code, highlighted, it didnât work and itâs very crude I know how I plugged it in but I did it just to demonstrate basicly what Iâm trying to achieve in this Excel Power Query code.
To get the Trial Balance there just seems to be some minor fiddling left to do in Excelâs Power Query code to somehow match with Managerâs undocumented API syntax. There must be some way to get this together ?
Btw Excelâs Power Query code, M, is considered a very simple language and Web.Contencts() is the only function in Excel Power Query which accesses web services like Manager API.
I know this is not a coding forum but getting this short piece of code right in Excel can benefit many Managerâs users and not only to get the Trial Balance report.
Due to the fact that there is some private info in my response, please check your DMâs. However, I will say that the url you are using to retrieve the Trial balance report is wrong. it should not contain the /api portion (which is why I refer to is as the âFront Endâ API).
The JSON Report data is quite complex so you will need to study it a bit to get a handle on finding what you need.