API 2 using Power Query

[Wornout]

I have no idea what you are talking about patch. I put my client data in manager, They are my clients I want the customer data that I put into manager with their approval . I am their accountant (150 Pax), I was a programmer many many years ago but stopped and am not up with the play. I want to be able to pick up my client data which I hold from manager in excel power query so I can set graphs and time frames and charges. I am never using batch update or copy and paste as I lost data. Thanks to @Ealfardan for the answer .

[Patch]

@Wornout

customer data that I put into manager with their approval . I am their accountant

You being their accountant and them asking you to enter it into Manager does not change the fact the data is still the property of your customers. Just the same as data entered into the cloud version of Manager is still owned by the Manager user not NG Software.

@Wornout

so I can set graphs and time frames and charges.

Using customer data to quantify the cost and time estimate to do the task a client has contracted you to do is almost certainly within the task your customer has given you permission to do. Graphs of your customer data supplied to your customer is also likely to be within the scope of work your customer has authorised you to do.

Creating de-identified data for other tasks and to supply to any other entity is almost certainly not within the scope of work your customer has asked you to do. A very specific contract with your customer is required to legally do that.

Sure Google / Facebook / Microsoft business model is based on data harvesting form user data to sell to their paying customers, doing so requires a careful considerations of copyright, and the user agreeing to a contract where they loose ownership of data users create.

[Wornout]

@Patch

the

Look i am not here to justify myself to you. I know what i can and carnt do with my client data. They sign a forn saying i can put there data into excel to produce reports for them. All i wanted to know is what the path was. I certainly did not need an asumed lecturer.

[Acct1]

Hello,

I know these sound like stupid questions, but:

• Where in Excel do you go to use this?
• And where/what do you do with the code once you get there?
• I assume that any way of pulling data from Manager.IO into another program (like Excel) has to go through the API, since the actual SQLite database is stored in BLOBS (or, at least, I think that’s what I read)?

Thanks in advance

[Acct1]

BTW, I was intending that as a reply to this post: API 2 using Power Query; but somehow made it a general reply.

[Ealfardan]

Hello @Acct1,

@Acct1

I know these sound like stupid questions

I wouldn’t worry about that, everybody has to start somewhere.

@Acct1

Where in Excel do you go to use this?

You can refer to this resource:

Create, load, or edit a query in Excel (Power Query) - Microsoft Support

Namely, section titled Create a blank query

@Acct1

And where/what do you do with the code once you get there?

That depends on how much time you are willing to invest.

The most basic case is to use the query to retrieve a list of master records to be used in batch operations.

To start using the code, you will need to save the query and then after clicking on done, a form similar to this one will appear:

You will enter the parameters: url, path, fields, etc and you will have your data loaded into excel.

You can explore what you can do with this.

@Acct1

I assume that any way of pulling data from Manager.IO into another program (like Excel) has to go through the API, since the actual SQLite database is stored in BLOBS (or, at least, I think that’s what I read)?

Anytime you deal with any sort of database outside of its DBMS, you’re using an API. However, in the case of blobs and objects, this saves you the step of sending SQL code through API.

[Acct1]

Thanks @Ealfardan,

“You will enter the parameters: url, path, fields, etc and you will have your data loaded into excel”:

Would the URL be the URL for the API that is given when you create an access token? Or the direct URL for the database itself?

What is the path?

Thanks.

[Ealfardan]

@Acct1

Would the URL be the URL for the API that is given when you create an access token

That’d be the right url

[Ealfardan]

@Acct1

What is the path?

You can make this other query which will give you a navigable list of paths and their specs:

@Ealfardan

You can find all available paths by navigating this query:

@Ealfardan

• To learn more on the available fields, you can use this connector, which will give you a navigable documents revealing all paths and fields:

let
    Source = Json.Document(Web.Contents("https://{your subdomain}.manager.io/api2"))
in
    Source

[Acct1]

Okay, thanks for all the help!

[Wornout]

Do I have to have manager open?

[Ealfardan]

On Desktop Edition, you need to have Manager running in order for it to listen to api requests

[Wornout]

I still can not do it and I have had chatgpt working on it for 3 days ! we keep stumbling with custom fields. I am trying to get “customers” along with the custom fields Text, number, multi-fields ones . I have gone on to the server one and got pass words and can sign in. I dont want to use server one so have it on a usb stick and just load it from there. I have an api end point and secret . I dont know . I have been trying to do this for weeks and as I said GPTchat has been on it for 3 days and it now says you cant do it

[brandon_oma692]

Excellent I have this working and pulled in my inventory items from my test install of the server version I worked on yesterday. Is it possible to get the “item code” field also? “Item code” is my numeric part number and “item name” is my text description. I may not be doing this as intended or the best way.

THANK YOU

[brandon_oma692]

Got it. Bottom of inventory item screen edit columns. Item code checkbox. click update. refresh excel. pulled it in. did not need an advanced query.

But I think I messed up and should have my part number as “item name” and text description as “description”. I missed that on the first setup. Can I correct this without redoing everything or starting over?

EDIT

Well that was easy. inventory items batch operations. move 2 columns of data and set has description from false to true. did this with a copy of the file I am testing on the server not the main one. Went into my last sales invoice and deleted all the items and readded them and it looks BEAUTIFUL.

Summary numbers look good. What may I have broken and need to validate?

[Ealfardan]

My pleasure @brandon_oma692