Need Formula Help on Array

[overcharge]

Hi Experts,

Now i'm facing a problem for calculating sum.

ProgTitle(PK) ProgID SeqNo Duration(MMSS) Show Time(HHMMSS)
1 12 0 0300 070000
2 12 1 0200 070300
3 12 2 0250 070550
4 13 0 0100 080000
5 13 1 0146 080146
6 15 0 0201 090000

Above is my data, my problem is how to show the Show Time column in my CR report. Cry

When SeqNO= 0, i need to refer start time from another table which is linked with ProgID.

For example, for ProgTitle=1 and SeqNO=0, it means that the start time 070000, for ProgTitle=2, the show time is start time + 1st duration which is 0700000 + 3min = 070300... etc

We can see that there is a pattern. When the ProgID is same, we reder to the same start time and need to calculate its Show Time.

Any advices for me to solve this problem? Thank in advance.

 

[Madawc]

1) Link the second table with a 'Left Outer', meaning that you get all of the records from the first table, whether or not they have a record on the second.

2) Create a formula field that does the correct calculation, based on SeqNo etc. Put this next to the data while developing, you can suppress it or remove it later if it is not wanted.

Allow for nulls, do an Isnull test before checking for possible values, otherwise the formula will stop without output or explanation.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[IanWaterman]

I am assuming your initial start time 070000 and duration 0300 are strings.

Break up into Hours, mins and seconds

@StartVar

global numbervar starthr;
global numbervar startmin;
global numbervar startsec;

global numbervar durmin:= tonumber(left({Duration},2));
global numbervar dursec:= tonumber(right({Duration},2));

If SEqNo = 0 then starthr:= tonumber(left({StartTime},2));
If SEqNo = 0 then startmin:= tonumber(mid({StartTime},3,2))
If SEqNo = 0 then startsec:= tonumber(right({StartTime},2));

If startsec + dursec >= 60 then startsec:= startsec + dursec-60 else startsec:= startsec + dursec;
If startsec + dursec >= 60 then startmin:= startmin + 1;

If startmin + durmin >= 60 then starthr:= starthr +1;
If startmin + durmin >= 60 then startmin:= startmin + durmin-60 else startmin:= startmin + durmin;

Split dtails into two sections and place this formula in lower section below your data, suppress this section.

Place this formula in upper details

@start

global numbervar starthr;
global numbervar startmin;
global numbervar startsec;

If SEqNo = 0 then timeserial(tonumber(left({StartTime},2)), tonumber(mid({StartTime},3,2)), tonumber(right({StartTime},2)) else
timeserial(Starthr, startmin, sartsec)

Ian