How to retrieve detailed outstanding balances

[Sander_Feenstra]

Hi there,

How can I get the all outstanding balances for a customer (or all customers at once)

with at least the following fields:

  invoice number, invoice date, currency, invoice amount, paid amount, (amount due)

What I'll need is the same data as shown in de "Balance by customer" report 

but then using a single API endpoint.

And the same question for creditors 🙂

Best regards,

Sander

[Sander_Feenstra]

Hi Suzdar,

First of all thank you for your quick reply.

Unfortenately it's not quite the answer I was hoping for.

1. First I have to make a request for the balances for all customers
    with a respective page size of 250  it takes 20 seconds for ~1250 customers.
2. For all returned customers with a balance <> 0.00  I request:
   /controller/api/v1/customer/{customerCd}/document?status=Open
   
   
3. Finally I'm be able to locally filter the returned data for the branchNumber I actually want.
   The result was 1180 open documents

This took all and all more than 3 minutes which I find not quite acceptable.

Even more worse when I did the same for the suppliers in my case ~7750 in total

and 99 suppliers with a outstanding balance.

This took 7 minutes. Maybe this had something to do with the fact

that /controller/api/v1/supplier/{supplierCd}/document?status=Open    

gave me also all closed documents, so as it looks the filtering doesn't work.

So I actually pulled 25k documents and only 286 with an actual balance on them.

It’s a pity because the report “Balance by Customer” in the font-end is exactly what I need

and can also be filtered by branch right away.

Furthermore it only takes less than 10 seconds to generate!

I also find out about the endpoints:

/controller/api/v1/customerDocument  &  /controller/api/v1/supplierDocument

Without any query params the customerDocument endpoint only gave me 3 results.

With ?status=Open it gave me 22   ?    

What did i miss?  I should expect 1180 docs.

It looks like these endpoints are exactly what I'll need and only lack a branchnumber filtering possibility

Can you help me out on this? Thanks in advance.

Br,

Sander

[Suzdar Ibrahim]

Hi Sander,

I'm not sure If I've understood your question correctly, I assume by creditor you mean supplier?

Anyhow, I'd suggest this:

Do a request to this endpoint to check the balance on the customer & supplier
/controller/api/v1/customer/{customerCd}/balance
/controller/api/v1/supplier/{supplierCd}/balance

Then return the list with open documents with this endpoint:
/controller/api/v1/customer/{customerNumber}/document

/controller/api/v1/supplier/{supplierNumber}/document

- Suzdar

[Sander_Feenstra]

Hi  Suzdar,

In addition to my previous reply, I also found out that I can't roughly sum the balances together from the documents returned by:

....customer/{customerCd}/document

Apparently I also need knowhow of the different document types in order to use the correct sign.

[ Invoice, DebitNote, CreditNote, Payment, VoidPayment, Prepayment, Refund, FinCharge, SmallBalanceWo, SmallCreditWo, CashSale, CashReturn, Undefined, NoUpdate ]

I find this also very inconvenient, can you somehow add a "sign" field to the document objects? 

Best Regards,

Sander

[Suzdar Ibrahim]

Hi Sander,

Are you able to show me an example of this? I'm not quite sure if I understood your request. You want a "+" or "-" sign for the values?

[Sander_Feenstra]

Well, signing the monetary amounts would be most appropriate but I don't think

(backwards compatibility wise) this is a good idea.

Either a sign or a D(ebit) / C(redit) indication could be a solution.

See my example below:  

Could something like that be add to the endpoints:

../CustomerDocument

../Customer/{CustomerCd}/Document

../SupplierDocument

../Supplier/{SupplierCd}/Document

Please also look a my previous post, didn't get a clear answer on that one.

https://community.visma.com/t5/Forum-in-Developers-Visma-net/How-to-retrieve-detailed-outstanding-ba...

Best regards,

Sander

[Suzdar Ibrahim]

Hi Sander,

I understand now! thanks for clarifying. As of today this is the only way to get the balances for suppliers and customers. I will take both your requests to our development backlog and hopefully it will be prioritized. 

Just to be sure you would like to have balances for customer/supplier based on ScreenId=AR632500?

/ Suzdar

[Sander_Feenstra]

Based on the reports:

"Balance by Customer"  / "Balance by Supplier"

[Suzdar Ibrahim]

Thanks! I will create a backlog ticket for the development team 🙂 Keep an eye on the release notes to see when it has been implemented.

[Sander_Feenstra]

Hi Suzdar, I replied accidentally to my own post 🙈  which was, of course, directed to you.

Kind regards, 

Sander