Crystal Reports XI Custom Group Field

Question

I need to create a provider Name group which is based on any or all of three fields that contain service provider codes. Each Service Provider Code links to its own SP_Program_Location table to optain that providers Provider ID which is then linked to a provider table to obtain the Providers Name. If no providers have been assigned for a client ( all three provider fields = 0) the provider grouping should be "No PROVIDER ASSIGNED", otherwise, the name of providers need tobe included in the provider group. The number of providers for a client can range from 0 to 3. As soon as I group on my Provider formula I loose data records. I am grouping on a location and then Provider followed by Client detail data.

Program.provider1 ---->sp_Program_Location.SP_Program_Location_Codde--->sp_Program_location.Provider_ID ---> Provider.PROVIDER_ID

Program.provider2 ---->sp_Program_Location1.SP_Program_Location_Codde--->sp_Program_location1.Provider_ID ---> Provider1.PROVIDER_ID

Program.provider3 ---->sp_Program_Location2.SP_Program_Location_Codde--->sp_Program_location2.Provider_ID ---> Provider2.PROVIDER_ID

Thank you for your assistance

Brian

Answer 1

Create a formula field, as defined below, then group on it

//use my Array_Push() function [http://www.cogniza.com/blog/?p=104]
//which requires the Array_Contains() [http://www.cogniza.com/blog/?p=103]

Local StringVar Array providers;

If Not(Isnull({Provider1.PROVIDER_NAME})) Then
  providers := Array_Push({Provider1.PROVIDER_NAME});

If Not(Isnull({Provider2.PROVIDER_NAME})) Then
  providers := Array_Push({Provider2.PROVIDER_NAME});

If Not(Isnull({Provider3.PROVIDER_NAME})) Then
  providers := Array_Push({Provider3.PROVIDER_NAME});

If Ubound(providers)=0 Then
  "No PROVIDER ASSIGNED"

Else

  //create hyphen-delimited list.
  Join(providers, "-");