Outstanding amount per customer

[Florian Haase]

Hi

what would the best, most effective or most correct way be to get the outstanding domestic amount per customer?

I see there was a discussion about grouping in the aggregate endpoints which was not an available function at least.

But loop through the aggregate with filter on customernumber seems not to be very effective.

Get all opencustomertransactions and group on clientsite could potentially result in many selected rows

And how to use customerbalance im not sure because there the max of month+year per customer has the last balance?

Any suggestions?

Florian

[Florian Haase]

Yes! That seems to work now, thank you!

[Florian Haase]

I tested this now and it still runs into a timeout

[Øyvind Årseth · Accepted solution]

Hi Florian
We've had some tests failing the last weeks, so the backend is not updated with the latest changes yet. If everything goes according to plan, tomorrow's deployment should fix this issue. Let us know if a test, say after lunch tomorrow, still fails. 
Sorry for the delay!

Øyvind

[Florian Haase]

ok, since there is no better suggestion we are going to fetch all openCustomerTransactions with restamount<>0 and summarize it on our side. Just not sure how many transactions we will have on each run.

[Øyvind Årseth]

Hi Florian

You should be able to run a query against customerCurrentBalance, which would provide the outstanding amount regardless of period for the latest movement, which I again suppose would eliminate the issue of not knowing which period to query for each individual customer (since it is safe to assume that the latest movement differs).  

query CustomerBalance ($cid: Int) {
    useCompany(no: $cid){
        customerCurrentBalance (filter: {customerNo: {_eq: 10000}})
        {
            items
            {
               customerNo
               outstandingAmount
               numberOfOpenEntries   
            }
        }
                     
    }
 }

Currently, this query only returns a timeout, probably due to a bug, we are investigating. 

[Florian Haase]

Is there any progress, or any new information?

[Marius Bancila]

I can confirm there is a bug in the backend that produces this outcome when querying a view (not a table). We are currently investigating it.

[Florian Haase]

Any idea about a timeline? if this takes some time to be resolved, we have to change our implementation in the meanwhile

[Marius Bancila]

What I can tell is that the problem is now fixed in the backend. Since we have one release per week, happening on Thursdays, I expect this will only be rolled out to production next week.

[Florian Haase]

Thank you for the info. I can talk with the developer that this will be available on Friday and he can start to implement that.

[Florian Haase]

That was obviously not so easy? We could start with looping first, but hope that we don't hit any quota limits therefore.