Owing calculation showing zero balances in error

[topsquirrel]

Hi,
My SI has recently been upgraded from ‘very old’ to master_2019.2. There are hundreds of invoices and payments with a few that should show owing balances but nothing is shown in the owing column. Similarly, the due filter shows no invoices.
There doesn’t appear to be any PHP errors logged.
I believe all the db patches have gone okay and patch 302 ‘Added owing to invoices table’ has run.
Is the default payment period still 14 days? If this is a setting somewhere I haven’t found it.
Can anyone suggest where I should start looking?
Thanks
David

[RRowley]

First, check the settings in si_preferences. The logic assumes that the pref_id == 1 record has the pref_description of “Invoice”. That is, all “Invoice” type entries in the si_invoices table has a preference_id of 1. If this is the case, then in si_invoices, set the last_activity_date to “2020-01-02 00:00:00” and the aging_date to “2020-01-01 00:00:00” where preference_id == 1.

This should cause the aging and owing information to be recalculated when preference_id 1 invoices are accessed. This will be done in bulk for all invoices the first time you log into SI and it builds that invoices select table. If you have a lot of records, then you will notice a delay while the update if performed the first time. After that, only records that have activity or show amounts owing go through a calculation.

[topsquirrel]

Thanks for coming back, I think I follow that but I’m unsure how that will work in my case as I have 12 records in si_preferences.

I have three businesses in the database and over the years I have created a few invoice types to distinguish between such as monthly service charges and one-off invoices. The record in si_preferences where pref_id == 1 seems to relate to my sole trader business which doesn’t do much these days whereas pref_id == 7 and == 9 relate to most of my invoicing transactions.

Should I set all of the si_invoices records to last_activity_date to “2020-01-02 00:00:00” and the aging_date to “2020-01-01 00:00:00”? Would that cause all invoice types to be triggered for owing and aging?

Attachments:

1. 

   ScreenHunter-1400.jpg
   

[topsquirrel]

p.s. I’m not averse to using different domains for each business. I could set si_preferences.pref_id to 1 and pref_description = “Invoices” within each domain…if that’s how it should work? 🙂

[fearless359]

I just uploaded an update that add a set_aging flag field to the si_preferences table. When loaded, the first access will update the database and set this field to 1 (true) for the preference ID of 1. All others will be 0 (false). You can then set the other preference records however you like to cause them to set the aging field. So no longer will the aging logic be tied to pref_id of 1, but to this new flag field setting.

I’ll have to look at the domain_id write up. It was written before I added foreign keys and likely does need to be updated to account for this.

[topsquirrel]

I’ll look forward to the update details Richard 🙂
btw I couldn’t update the post with what I did in my scenario as it thought I was a bot

[fearless359]

The foreign key logic breaks the alternate domain logic. I need to do a deep dive on it for a fix.

Concerning site thinking you are a bot, did you have and check the reCaptcha “I’m not a robot” box? It is something that I added recently to help assure that real people are signing up and sending messages on this site.

[Author]

Posts