select date based of a record 1

[mbDutch]

Question -

I have created a running total field that calculates a Current Qty + Qty added - Qty Substract. This balance
changes on a daily basis. Now quite often this balance will go negative on a certain day. What I want to do is pick the first day that balance went negative and only that day and place it in the group header.
Any help would be greatly appriciated.

 

[IanWaterman]

Are the quantities details or summaries.

If details

Place date in detail line
Right click detail section and open section expert.
In formula opposite suppresion

Enter this formula
Qty + Qty added - Qty Substract >= 0

This will suppress all lines where that calc = 0 or more.

If summaries then do this to your date group header instead.

Ian

 

[mbDutch]

Thanks Ian - let me provide some additional info:

I have a group that groups by Product. The group header lists the description of the products, item number etc. Then the detail shows the date of what was added and substracted plus the running balance I created (field name = @Balance). So now I want to pick the date of when it first went negative and place that date only in the group header (So I can supress the detail). possible?

 

[IanWaterman]

YOu will have to do this with a variable, but the date would have to appear in the group footer. It can not appear in the header as the variable will not have been evaluated at that point.

In product header add formula (and suppress so not visible)

whileprintingrecords;

global datevar NegDate:=date(1900,01,01);
// This just rests dates date for each product

In detail add a formula

whileprintingrecords;

global datevar NegDate;
If Negdate= date(1900,01,01) and #RT Balance < 0 then
Negdate:= datefield;
// By testing for reset stops date being over written by subsequent dates when balance is still negative. YOu will need to creat a running total for balance which is reset on each change of Product group.

In group footer add formula

whileprintingrecords;

global datevar NegDate;

Ian

 

[mbDutch]

Thanks Ian. Worked like a charm.