Combining records with a formula 1

[flepkows]

Hello all, I am using the packaged CR for .NET and I am generating a report for which I need a formula I am having trouble creating. I have a table in my DB that records events (dates) for a certain part. When I execute the report I am getting seperate records on my report for each match it finds in the event table for a given part. I am looking for a way to right a formula that will combine these records into one record per part number. Any help is greatly appreciated.

Thanks Frank

 

[lupins46]

Sounds like you need a grouped report based on the part number. Suppress the detail line if you don't want the detail.

 

[flepkows]

I am currently grouping on part number, here is an example of the data I am getting.

PartNumber Date1 Date2 Date3 Date4

P#4567
3/4/04 9/9/9999 4/4/04 9/9/9999
9/9/9999 4/6/04 9/9/9999 1/2/04
.
.
.

I have formulas that are calculating these dates shown and I am setting the dummy value to 9/9/9999 for certain fields if no data exists in the database. I want the above data to be displayed as follows

PartNumber Date1 Date2 Date3 Date4
P#4567
3/4/04 4/6/04 4/4/04 1/2/04

Is this possible, it is essentially adding the records found for each date in the event table so that the new record contains all these date for that given part. Also will a formula to do this not work if say there are two records in the database for a part that was changed and the two actually are seperate events? Thanks in advance

 

[lbass]

If date1, date2, date3, date4 are separate date fields, not instances of the same field, you could right click on each field and insert a summary->minimum and then suppress the details section. This would assume that there was only one "real" date per date field. If there could be multiple "real" dates, you could use the three-formula method to concatenate the instances into one line.

-LB

 

[synapsevampire]

LB's Minimum sounds right, and you can add to that a display characteristic such as:

if minimum({table.date},{table.group}) > currentdate+1000 then
"N/A"
else
totext(minimum({table.date},{table.group}))

-k

 

[flepkows]

These are seperate date fields and yes the possibility exists for there to be more than one date value for a given field. So I am assuming I have to use some type of formula to pull the data out I want and add it together, that also supports multiple records per part number group. This might be tough for me im kind of a newbie. Thanks in advance for any help

 

[lbass]

You would have to create formulas like the following:

//{@reset} to be placed in the group header for parts:
whileprintingrecords;
stringvar dates1 := "";
stringvar dates2 := "";
stringvar dates3 := "";
stringvar dates4 := "";

//{@accumdates} to be placed in the detail section:
whileprintingrecords;
stringvar dates1;
stringvar dates2;
stringvar dates3;
stringvar dates4;

if {@date1} <> date(9999,09,09) then
dates1 := dates1 + totext({@date1},"M/d/yy") + ", " else
dates1 := dates1;
if {@date2} <> date(9999,09,09) then
dates2 := dates2 + totext({@date2},"M/d/yy") + ", " else
dates2 := dates2;
if {@date3} <> date(9999,09,09) then
dates3 := dates3 + totext({@date3},"M/d/yy") + ", " else
dates3 := dates3;
if {@date4} <> date(9999,09,09) then
dates4 := dates4 + totext({@date4},"M/d/yy") + ", " else
dates4 := dates4;

//{@displaydates1} to be placed in the group footer (Parts):
whileprintingrecords;
stringvar dates1;
left(dates1, len(dates1)-2)

//{@displaydates2} to be placed in the group footer (Parts):
whileprintingrecords;
stringvar dates2;
left(dates2, len(dates2)-2)

//{@displaydates3} to be placed in the group footer (Parts):
whileprintingrecords;
stringvar dates3;
left(dates3, len(dates3)-2)

//{@displaydates4} to be placed in the group footer (Parts):
whileprintingrecords;
stringvar dates4;
left(dates4, len(dates4)-2)

Then you would suppress the detail section. You might need to right click on each display formula->format field->common->check "Can grow." If you want, you can drag the groupname for the Part group into the group footer and suppress the group header, too, but the display formulas must be placed in the group footer, since they are running totals, and would not be correct in the group header.

-LB