Using Field Rules vs Calculated Field Rules

[MortieM]

Is there a rule of thumb for the use of a defined field and Field Rules as a calculated field versus having an expression calculated field?

If there are a lot of calculated fields in an application does it make "good" sense to use a separate table, just for Field Rule calculated fields? Or is this even possible?

[CALocklin]

I assume what you are asking is about what Alpha calls a Virtual vs. Field level calculated field. See the Help file under "Calculated Field" for a better definition of these.

I believe the basic difference comes down to the fact that a "field level" calculated field is actually stored in the database and could, therefore, be exported along with other data. The "virtual" calculated field doesn't really "exist" anywhere - only the definition for the calculation actually exists - therefore these fields cannot be exported in the same way a regular field can be.

FWIW: My personal preference is to use calculated fields in tables, whether "real" or "virtual", very sparingly. I build a lot of databases and can't remember the last time I used a calculated field in a table. I use lots of them in layouts but see very little reason for carrying that extra baggage around in the table all the time even when I don't need it.

[Lawrence Fox]

Cal:

Then you haven't done a great deal of applications for insurance-related clients :). Some of these calculations are so complex that the only way to ensure that you're doing them right is to break them down into intermediate steps (at least in my experience anyways). Or there's situations where the answer is "the lesser of x or y" and each one is a long and involved calculation.

I guess you could "cheat" a bit and create UDFs to do some of this stuff instead of intermediate fields, though....

[CALocklin]

You are quite correct; I've done virtually nothing insurance related.

I would like to warn anyone who reads this about one more issue with calculated fields - just in case. I've seen people get into trouble because they didn't understand this issue or just forgot about it....

Remember that a calculated field will re-calculate every time the record is changed. In fact, my guess is that one of the "virtual" fields (I think they are called "global fields" in the table editor) will recalculate every time you use it. This means that you cannot use a value in the calculation that may change over time. (OK, you can but you shouldn't unless you also want the values in the old records to change.) One classic example was calculating sales tax using the tax rate that was saved in a separate table. When the sales tax rate changed and a global update was run for another reason, all older records ended up with the tax being recalculated based on the new rate. This created a bit of a problem with reporting the prior year's taxes. (Luckily, they did have a backup and, of course, they could have changed the tax rate back, recalculated again to reprint the old data, then updated the tax rate again, and recalculated again. Sound like fun?)

If the calculation is based only on other the values in the record, this won't be an issue. However, that's the situation where I don't see any reason to save the calculated value because it's very easy to recalculate it whenever needed. (Hmmm, having now thought about this more in the last few hours than I have in years, maybe it would be worth using the global/virtual calculated fields for those calculations that are frequently used in reports and based on other data in the record. That way I wouldn't have to copy and paste the calculation or worry about changing them all if I find an error.)

I should add that I have also seen (and used?) calculated fields for special situations where it improved the speed of something like a query or allowed the user to run a quick sort in a browse more easily.

The only time I would fault somebody for using a calc field is if it violates the warning above. It can work for awhile but almost every table eventually gets some type of update that ends up recalculating the calc fields and that can really mess up the data if the basis of the original calculations has changed. And, there is no guarantee that the user will even be aware of what happened until after they've published some bad data and someone else catches the error - not something that makes users happy.

[Lawrence Fox]

Cal:

Maybe this is a good time to pose a question that's been bothering regarding calculated fields at the table level. I confess that I haven't tested this yet, but I know it's going to come up in the app that I'm currently working my way through.

Let' say that Ploni Benploni's birthday is the 12th of March, 1942. That makes him 64 years old as of today (23-Jan-2006). Now let's say that his life insurance benefit (ant therefore premium) is based on a combination of his age and his salary, and that as soon as he turns 65, the benefit and premium drop by 40%.

So, if I was scrolling through a modeless form with employee information (by using the navigation keys) and saw Mr. Benploni's record today (23-Jan-2006) I'd see one set of values, since his age would be a calculated field as would the premium.

A couple of months from now, if I were to scroll through the form and hit his record and just look at it on the screen, if they are true "table-based" fields (instead of projections on the face of the form) would the age and benefit automagically change? If not, how could I get it to change? Run some kind of procedure/script every day as part of the startup procedure? (That could mean a VERY long wait for startup in the morning, I'll tellya...).

Would you make these sort of fields (and any fields required for the calculation) table-based "real" fields with field-level rules or would they be "form-based" fields with form-level calc's? (The difference being that when reports were run I'd have to recalc the current values "on-the-fly" as the report was being created. It also means that the calcuation rules only have to be created once and not for each and every report or form--which, in some cases, should things change, be a maintenance nightmare unless you were very rigid with naming conventions and had a nice tool like the one you developed to search reports and forms....:)).

It's an interesting question that lies at the heart of how to design a table and any forms and/or reports that would use the table (or set).

[Steve Workings]

A couple thoughts here:

Cal's most salient point should be heeded. Calc field values only change when a record change is committed.

That means it's probalby not a good idea in an insurance app to store someone's "current" age because it may be wrong the next day. It would be fine, for informational purposes, to display a calculated field on a form, because it would be updated each time the form is viewed. But, of course, stored nowhere.

So how to change the benefits on a periodic (daily, weekly, etc. basis?)

A couple things come to mind.

1. A periodic update routine for each benefit change that is age-based. The first step in each routine would be to run a query against the records based on the birth date. Lightning Query Optimization (LQO) makes this initial selection fast, with the rest of the routine likely to run pretty quickly.

2. Another approach could be: When a customer is signed up for particular benefits, the initial record setup could include a starting date, and perhaps ending date, for the benefit. This opens up many avenues to report and use the "available" benefits, etc.

While I've never written an insurance app, I do have one for an auto leasing company, which often includes leases with provisions that change over the term of the lease. For instance, one kind of lease is called a Step Lease where the monthly payment is $400 for the first 12 months, $375 for the next 12 months, etc. We record this info at lease setup time so when it comes time for the monthly billing, I know which charge to select.

Anyway, some thoughts off the top.

[Lawrence Fox]

Steve:

I may have to go with a combination of the two approaches. I'll probably go with the form-only calculated fields for some of the information (e.g., current age, current amount of coverage (which is sometimes a function of salary and age) and premium (which is sometimes a flat rate and sometimes a function of salaray and age--it depends on what the benefit is). My client needs to be able to call up your record on-screen at any time and must be able to answer the questions to you with you on the telephone. I don't want to force them to hit a "recalc" button each time they view a record (although I still may decide to do that anyways for other reasons).

When it comes to monthly billings, there's a separate table that gets populated with the values as at the moment of invoicing so that there's a permanent "snapshot/audit trail" of an employee's state of being at the time of the invoice (e.g., her salary, marital status, benefits (life, health, dental etc.), coverage (single, family etc), and so on). The invoice gets produced from THAT file (and we can then reprint the invoice at any time with the then-current details instead of keeping massive amounts of paper in filing cabinets providing food for mice :)). So I think I'll do a "calc on the fly" as part of the append process. If I can't do this, then I'll go with calculated fields and button on the form that says "Re-calc for most current values" and keep hitting the staff with limp noodles until they remember to hit the button :).

[Steve Workings]

Yeah, a combination is probably the way to go. Different tools for different jobs.

One thing you could do to force a recalc of a record is to use the form's OnFetch event to change some value -- perhaps a "last time/date viewed" field or even a dummy field, and commit it.

[CALocklin]

Interesting stuff. Steve, thanks for saving me the time to reply. I like your idea of changing a value with the OnFetch event - I'd never thought of that.

As noted above, I wonder if this would be a good place for the global/virtual calc field - especially for the age. That way it wouldn't be actually saved in the table but it would be immediately available in any layout without having to rebuild the calculation. I haven't used these fields in the past but it's something I'll try to remember for the future. Maybe they will turn out to be a real convenience. It will be interesting to see if there are any negative issues with them. I'm guessing you can't sort or query by them but I'll reserve that as only a guess and check it out later. Of course, if somebody already knows and wants to save me the effort of checking...

[Lawrence Fox]

That's another possibility that I was considering, but wouldn't that generate a great deal of network activity?

[Steve Workings]

If all you're doing is updating values for a single record, e.g. the one being viewed on the current form, then network activity isn't an issue at all.

This all brings to mind something I have for the auto leasing company. When a customer calls the AR dept, we need to display current, accurate data for the discussion with the customer. Trying to keep every record and every balance up to date in real time is a tough job -- I don't recommend it. Instead, when a customer's record of outstanding invoices is fetched, I do a bunch of calculations (current balance on each invoice, total due, etc) and display it all as variables on the form. This works quite well even if there are 50 or 100 invoices to show for a single customer, and the delay for the calcs is negligible.

[Tim Kiebert]

Interesting stuff. Steve, thanks for saving me the time to reply. I like your idea of changing a value with the OnFetch event - I'd never thought of that.

As noted above, I wonder if this would be a good place for the global/virtual calc field - especially for the age. That way it wouldn't be actually saved in the table but it would be immediately available in any layout without having to rebuild the calculation. I haven't used these fields in the past but it's something I'll try to remember for the future. Maybe they will turn out to be a real convenience. It will be interesting to see if there are any negative issues with them. I'm guessing you can't sort or query by them but I'll reserve that as only a guess and check it out later. Of course, if somebody already knows and wants to save me the effort of checking...

Cal,
In my learning curve I stumbled over that very situation. I have just recently discovered that you can filter and sort on such a field.

I have the following expression in a table based calculated Virtual field (ie defined while editing the table structure) called cplcy_due. It calculates the next due date that a maintenance policy needs doing. It uses a field (Lst_Dtdone) from the current table (plcy_eqp) and some lookups to another table (Policy) based on a linking field of Plcy_Idf.

case(lookupc("F",Plcy_Idf,"Frqn_typf","policy","Plcy_Idf1")="Days",Lst_Dtdonef+lookupn("F",Plcy_Idf,"Frqncyf","policy","Plcy_Idf1")
    ,lookupc("F",Plcy_Idf,"Frqn_typf","policy","Plcy_Idf1")="Weeks",Lst_Dtdonef+(lookupn("F",Plcy_Idf,"Frqncyf","policy","Plcy_Idf1")*7)
    ,lookupc("F",Plcy_Idf,"Frqn_typf","policy","Plcy_Idf1")="Months",addmonths(Lst_Dtdonef,lookupn("F",Plcy_Idf,"Frqncyf","policy","Plcy_Idf1"))
    ,lookupc("F",Plcy_Idf,"Frqn_typf","policy","Plcy_Idf1")="Years",addYears(Lst_Dtdonef,lookupn("F",Plcy_Idf,"Frqncyf","policy","Plcy_Idf1"))
     )

I then am using that calculated field in a querry run from a button on a form, based on the same table, that filters comparing the date in that calc field to the current date. And yes it sorts as well.

records_found = topparent.queryrun("date()>[B]calc->cplcy_due[/B]","[B]calc->cplcy_due[/B]","","No","",.f.)

I struggled for a while on this one. One thing that really threw me is that when using the filter builder (as a novice does) I could not choose a calc field to use in the expression because the calc option just wasn't there. So I figured "oh well, I guess you can't use a calc in a filter". BUt I could put the calc in the embedded browse and the do a quick filter on it which meant it must be using the calc in the filter some how. So I went right ahead and just typed it in the query direct as above and it worked.

Another interesting note is that if I define a calculation at the form level using the same expression, although it calculates correctly, it does not work in the query. It seems that the virtual calc field needs to be defined in the context that it will be used. Which I suppose makes sense.