Help! Multiple Details On One Line

[govmule]

Hello,

I've got a table named 'FollowUp' In it is a unique customer ID called 'AppNum'. There are six types of followup records with a field called 'FollowUpType' to identify each one. There is also a field called 'InterviewDate' that shows the date the client met with a case manager.

I've written four formula's stating:

1) If {followuptype} = '3' then {interviewdate}
2) If {followuptype} = '4' then {interviewdate}
3) If {followuptype} = '5' then {interviewdate}
4) If {followuptype} = '6' then {interviewdate}

My goal is to have one row on the report per client that I can stick these formulas in so we can see four dates (types 3 - 6) in one line instead of a new row for each.

I tried the solution of grouping by 'AppNum' (it's the unique ID) and placing all these formula's in the group header and then supressing the details. It's not working. It only displays the date if that is the first followup record.

I'm stumped. Any help would be very much appreciated.

Thanks,

Jack

 

[synapsevampire]

Create a grouping for Appnum.

Change your formulas to capture the values, then use another formula to display them:

//@fllwtyp3 capture formula
global datevar intvwdate3;
If {followuptype} = '3' then
{interviewdate};

Then at the Appnum Group Footer level:

//@fllwtyp3 capture formula
global datevar intvwdate3;
intvwdate3

You'll need to do this for 3-5, providing you sort by {followuptype}. The last one, assuming that you always have a {followuptype} of 6, would be the field {interviewdate}, if not, create all of the formulas.

An alternative would be to use an array, since it's only 4 dates, you could do this, but formatting might be a problem so I offered this instead.

A crosstab might work too.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lbass]

Hi long-lost kin,

You might try creating separate detail sections a, b, c, and d, and placing one of your formulas per section, positioned horizontally the way you would like, and then in the section editor, use the underlay feature for your appnum group and each detail section. I think this would work if you only have one interview date per followup type.

If you have multiple dates per followup type, you could do a descending sort of the date field, and then try grouping on appnum and on each formula and again use the underlay feature. This should give you the most recent date per type.

If the followup types are not important (i.e., if they were only a means of forcing a horizontal layout, but are not important in and of themselves), you could also try just using the interviewdate field and using the multiple column feature in the section editor.

I'm not where I can actually test these ideas, so hope I'm not leading you astray...

lb

 

[govmule]

Thanks Everyone. I ended upm using a linked subreport to take care of this problem. I really appreciate the help you offered though.

Thanks Again,

Jack Luster

 

[synapsevampire]

Seems odd to use a subreport, I must have misunderstood the requirements (not the first time...).

Try to avoid subreports with larger datasets for performance reasons.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[lbass]

Hi,

In case it was not obvious to some, my earlier suggestions don't work, although I've used some of them in combination with adding alias tables to solve slightly different problems. The challenge of the current (granted, already solved!) problem is creating an independence between different instances of the same field. The following works:

1-Add the followup table in one instance to serve as a "master table" and, using aliases, add the followup table again as many times as necessary to reflect the number of followup types.
2-Link each followup table to the "master table" (but not to each other)using the unique customer ID.
3-For each alias table, select one followup type.
4-Group on the unique id field in the "master table" and add the followup date field from each alias table to the group header. Suppress the detail section. (If there is only one date per followup type, you wouldn't have to group, but could just use the detail section.)
5-Sort each date field in descending order, so that the most recent (if that's what you're looking for) is represented in the header (if there is more than one instance per followup type).

Apologies for not testing my earlier ideas before blurting...

lb