I have automated importing of POS data into manager using excel power query but I am still facing problems with how to work with references. If I used custom references for imported transactions, that would completely mess up the auto numbering of manual transactions.
Because of all that, I finally decided to have my imported transactions numbered in sequence along with manual transactions but getting the next number in sequence turned out to be much more difficult than I expected.
I tried using the Manager url to get the Debit Notes table as follows:
let
Source = Table.FromRecords(
Json.Document(
Web.Contents("https://agileas.manager.io/debit-notes.json?Take=50&SortBy=1&Desc=True&FileID={ so and so }")
)
)[reference]{0}
in
Source
In theory that should work perfectly, but the query &SortBy=1&Desc=True doesn’t seem to apply on the json file. That’s a bummer.
Is there any way to get this to work? I’m also open for any other method that doesn’t involve retrieving the full set of 6000+ debit notes everytime I need to know the next number in sequence.
Have you considered starting the manual transaction numbering at, say seven digits and using smaller numbers for custom references for the imported transactions?
Sorry, I’m not sure about the specifics of Excel power query. Best I could do is offer some feedback on potential API querying issues, but it seems you might have that mostly under control at the moment.
I am unsure if the API ever created references automatically. The main use case that I have had is copying accounting data from another system into Manager, and so I already had pre-defined references in that scenario. Never had to rely on the auto incrementing reference via API.
That’s right, the API doesn’t create auto refs neither does batch create.
But if I could somehow retrieve the next number in line I could assign the references myself. There are two ways to do this right now:
Manual user input – but this breaks the automation and introduces errors.
Retrieving the entire invoice list, which is extremely inefficient.
I tried querying the tabs by adding .json in the URL so I can sort the tab table by references descending and get only the first fifty but the sorting part didn’t work.
That’s brilliant.
