Array Formula Problem - some blank fields

[etseel]

Can anyone help me troubleshoot a problem I'm seeing with an array calculation?

I'm using CR9 PRO hitting an Access Database and looking at 4 time fields.

All formulas are evaluated at the Details level. I have 3 equations that calculate the time gap between the bench mark AO Time field which appears in each gap equation, and one of the other 3 time fields in each respective equation. Therefore I end up with 3 gap calculations for each record: @STgap, @MTgap, @TTgap. An example formula would be: @STgap = timevalue((...ST_Time)-(...AO_Time)) The underlying database does not have time stamps for each column on every record, but there will always be a result for at least one of these 3 formulas. I placed all 3 of these values into the details section and each formula is calculating correctly.

Next, I'm trying to use an array to capture the value of each of these 3 gaps, and determine the largest time gap among these results, FOR EACH RECORD.

Here is my Array:
//@TradeGap
local timevar stg := {@STgap};
local timevar mtg := {@MTgap};
local timevar ttg := {@TTgap};
local timevar array TradeGap := [ttg,stg,mtg];

maximum(TradeGap)

I used "local" variables so would reset values when they went to the next record of data. My array is working properly when I only include 2 of the 3 gap (MTG and STG) formulas, but fails when I include the TTG variable. What happens on the screen is that I always get the correct answer for a larger time gap between MT or ST, but when TTgap is the largest value, then all my result fields are blank.

Please tell me I'm doing something bone-headed or typed something wrong.

Thanks!

 

[synapsevampire]

I think that you're getting some unexpected values.

Try:

local timevar stg := currenttime-1500;
local timevar mtg := currenttime-1500;
local timevar ttg := currenttime-500;
local timevar array TradeGap := [ttg,stg,mtg];
maximum(TradeGap)

Note that you'll get the 3rd value.

If you change the -500 to -1500 you'll get a different value, hence the third value si being used.

Since we aren't privy to what's in your code, I can't really help much more than to say that the above will pull the maximum of the array.

-k

 

[lbass]

You say:
"The underlying database does not have time stamps for each column on every record, but there will always be a result for at least one of these 3 formulas. I placed all 3 of these values into the details section and each formula is calculating correctly."

But a particular formula would not calculate at all if one of the fields in it were null. If each of the formulas can be null, then you need to create defaults in each formula so that you always get results.

This tests out here--when I use a formula that can be null for {@TTgap}, then the {@TradeGap} is also null. So, for example, you might want to set a default for {table.TT_time} when null, e.g., currenttime or time(0,0,0) so that {@TTgap} is not null. Which value you use for the default depends upon your logic and whether you want a null to result in the largest gap or not.

-LB

 

[etseel]

synapsevampire - your test confirmed the formula was having trouble choosing the correct value, and suggested it was from the data elements, rather than the formula itself- which turned out to be the case.

lbass - So I looked at your suggestion and decided to try and handle the effects of a null value. I wrote some ifnull statements and changed the value of my null fields to 00:00 (in the Crystal, not in the underlying tables). That quickly resolved the problem with my Maximum() calculation, and made certain that I always have a result for my formulas. No change was required to the array formula, and it now chooses the correct value from the 3 separate formulas on each detail record.

Thanks for the quick answers - hope this helps somebody else...