Jump to content

Calculated Fields Finding Issue


OlgerDiekstra
 Share

This topic is 2868 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

 

 

Link to comment
Share on other sites

This topic is 2868 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.