pc Calculated Fields Finding Issue

[OlgerDiekstra]

Hi Guys,

Been butting my head all morning trying to figure out what was going on.

The DB I maintain handles a lot of stuff, including invoices and payments (and customers of course). Invoices can be paid immediately and on a plan (ie, a weekly, fortnighly or monthly payment, either via a direct bank debit or CC debit).

This of course makes life a lot more complex than simpel instant payment of invoices with just a single payment.

The DB of course has heaps of master tables and TO's.

This morning I was working on a forecasting report of products that are to be made in the next few weeks. The idea is that our production manager can run it over any period needed. That part is all fine.

To determine which products are about to be made, I look at the corresponding invoice (which has lineitems for each product), and look at the estimated end date of that invoice. The "Est End Date" field is a calculation that takes into account whether its a weekly, fortnighly, monthly payment and how many payments are left (calculated using the total invoice minus the balance of payments paid).

Because some fields use related fields, a lot of calc fields are unstored. Ie, to get a total paid balance, I need to sum all paid payments in the payment table.

The fields I was having issues with were Est End Date and NumberOfPaymentsLeft. Both calc fields and unstored.

For some reason, every time I tried to use those fields in a find, I would get zip, even though I could see those fields value when displaying all records and the values they hold. Using a date range to find (ie 1/1/2015...1/3/2015) would also return records from 2016 and 2017, and records in 2015 outside my scope.

Using NumberOfPaymentsLeft which is a simple number (ie 3) trying to find records with that number returned zip, even though I could see the value 3 in multiple records.

 

It was doing my head in. Googling showed barely anything.

I stumbled on one thread that mentioned something similar and they suggested rebooting the box (this is a server hosted DB).

Lo and behold. It's freaking working now. Which is good, but what happened here? I've read some threads about indexing probs, but I can't view indexes for these unstored calc fields .

 

Does anyone have any idea what might be happening? If it's an underlying structural problem with the DB (I inherited it a year and a half ago when I started with the company, and back then it was a shocking example of shoddy programming, since then a lot has been done to improve things), then I'd like to know so that I can take measures to fix that.

If it's something with FM thats wrong (it's a FM 13 server with FM13 clients, first version of 13, haven't gotten around to updating yet), then I might have to get FM involved.

It's not system resources or anything (afaik), it's 8-core server with 24GB memory and enough diskspace left for the DB. The DB itself is about 1.9GB.

 

Thanks for any pointers!