API - Retrieve Trial Balance

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?

Kind Regards
Richard

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.

In the latest version, JSON format should be supported now on all reports. It was added at the same time when ability to Clone any report was added.

1 Like

Yes. It works with trial balance but still not with custom reports.

@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 ?

@Tor, the following CURL request will extract the report in JSON format and save it to trial-balance-rpt.json.

curl -v -4 -G "https://apiUser:apiPassword@dev.mioapi.local/trial-balance-view.json?Key=4c1903a8-4eeb-433e-a3f1-71e5a83ca91e&FileID=Tm9ydGh3aW5kLWRhdGE" -o "trial-balance-rpt.json"

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 :blush:

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

01

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.

Hi @Tor, you are close :wink:

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.

hi, if having this issue? may i know what is the reason?

@DnD_IT_Solution, try to see if the link works without the “/API”…

Hi @Tor ,
No luck, still the same error.
but I try to change the /API to small capital, it work perfectly now.
think the URL is case sensitive.

Thanks a lot. :laughing:

yes M code in Power BI is case sensitive