Is There Another Way To Use Join()

[ChiTownDiva]

I want to Join a field based on a parameter.

Let me explain--

I have a formula called Instructor which is a concatenation of the instructors' first and last name. Now I want to join the @Instructor formula based on a parameter called Primary Sort.

If {?Primary Sort} = 'Merged Locations' then Join({@Instructor}, ", &quot else @Instructor"

Of course I'm getting that string array error message.

Does anybody have any ideas on how I can accomplish this?

Thanks in advance.

ChiTownDivaus

 

[synapsevampire]

Join will split an array into separate strings, is @instructur an array? Perhaps what you want to do is SPLIT the @instructor?

Please post example data and expected output, you'll get better results..

I tested this and it works fine:

if 1 = 1 then
join({?grp},", &quot
else
"Hi"

What you're attempting to do is use the @instructor formula as both an array and as a string.

-k

 

[lyanch]

The output of a formula cannot be an array.. so @Instructor cannot be used with join.. if it is just a concatenation in a string you can just display it.

Lisa

 

[lyanch]

Okay I take that back.. if its because you need to include commas (assuming spaces)


replace(@Instructor, " ", ", &quot

Lisa

 

[ChiTownDiva]

Thanks synapsevampire...

No, %Instructor is a an sql statement.

CONCAT("PERSON"."PERS_FIRST_NAME", CONCAT(' ',"PERSON"."PERS_LAST_NAME" ))

Or I can use @Instructor as a formula:

{PERSON.PERS_LAST_NAME}+', '+{PERSON.PERS_FIRST_NAME}

What I need to do is in the header list all of the instructors who taught a class for each location.

For instance:
Location Instructor
Baton Rouge, LA Latrice Dickerson
Pensacola, FL Kea Jackson
Pensacola, FL Kathy Hallsey

So, I have a formula called {@Merged Locations} that Joins the locations together--Join({?Locations}, ", &quot. Now I want to create a formula that joins the Instructors together. "Instructors" is not a parameter and that's where I'm having the problem.

Thanks.

 

[lyanch]

What does the data look like when it comes in? Why can you not just place them in your detail section:


{table.locationname} and {%Instructor}

I guess I don't understand why you need to join them?

Lisa

 

[synapsevampire]

First reference to %instructor, and no references to {?Primary Sort}...

I don't understand how these would line up alongside each other either but I'll assume that you've much more to reveal so I'll just address theory rather than a specific solution.

So you enter locations via a parameter, and want to somehow link those to data in a table.

Generally a location would be stored in the database as well, and a parameter is used only to limit the locations selected.

Locations and instructors would be linked by a key field.

So you would create a record selection formula to limit rows based on a parameter, and then just list your fields, perhaps grouping by location.

You seem to want to join a parameter to a data field without any commonality, perhaps by natural order you intend to align them???

Anyway, you can create an array from the instructors column, and then use a join to display them, but this is frought with potential problems, or you might just fill a string with them, as in the following:

whileprintingrecords;
global stringvar array Instructors;
Instructors := Instructors+{%Instructor}+chr(13)

This will gather them up, and then you can display them at the end.

-k

 

[ChiTownDiva]

Okay...this is getting confusing. I can't put this information in the details--it has to go in the header. The header looks like this:

Course Number: CZ900***
Course Title: Blah Blah Blah
Course Session Title: Blah Blah Blah
Course Session Code: CZ900-007
Session Start Date: 1/1/2003***
Session End Date: 3/31/2003***
Session Location: LA Baton Rouge***
Session Instructor: Jesse Ventura
Print Date: 4/4/2003

I hope this makes it clearer.

Thanks.

ChiTownDivaus

(The fields that have "***" are parameters). Presently, customers can input multiple course numbers and multiple locations.

Because the location is a parameter, there isn't a problem joining them, if a customer chooses multiple locations. The problem is I need to join the course sessions and the instructors. If there were 2 sessions during the time frame of 1/1 - 3/31/2003, I need the header to look like this:

Course Number: CZ900
Course Title: Blah Blah Blah
Course Session Title: Blah Blah Blah
Course Session Code: CZ900-007, CZ900-008
Session Start Date: 1/1/2003
Session End Date: 3/31/2003
Session Location: LA Baton Rouge, FL Pensacola
Session Instructor: Jesse Ventura, Anthony Michael Hall
Print Date: 4/4/2003

 

[Naith]

Hey ChiTownDivaus,

You can't use a join condition in conjunction with the instructor data in the report header. By the time you're trying to return the instructor records in the header, the report hasn't actually processed the records to know what they are yet.

To accomplish what you're trying to do, let's forget about the join functionality, and go back to the beginning.

Right click the Report Header, and Insert Section Below. Invert Report Header a and b, so that your new section is now Report Header a.

Create a subreport into which you pass the {?Primary Sort} parameter. Place the subreport into Report Header a.

In the subreport, group by Location. In the group footer of the subreport, place the following formula:

//{@ParameterData}
WhilePrintingRecords;
Shared StringVar Name;
Shared StringVar PrimarySortOrder;

If Length(PrimarySortOrder) + Length({Table.Location}) + 2 > 255
Then PrimarySortOrder := '***'
Else PrimarySortOrder := PrimarySortOrder + ', ' + {Table.Location};

If Left(Name,8) <> 'Too many'
Then
If Length(Name) + Length({Person.Pers_First_Name}) + Length({Person.Pers_Last_Name}) + 2 > 255
Then Name := &quot;Too many instructors to list&quot;
Else
Name := Name + ', ' + {Person.Pers_First_Name} + ' ' + {Person.Pers_Last_Name}
Else Name := Name;

Back on the main report, in Report Header b, insert:

//{@ParameterDisplay}
WhilePrintingRecords;
Shared StringVar PrimarySortOrder;

If InStr(PrimarySortOrder,'***') > 0
Then Join({?Name},', ')
Else
Mid(PrimarySortOrder,2)

...and...

//{@InstructorDisplay}
WhilePrintingRecords;
Shared StringVar Name;

Local NumberVar Counter;
StringVar Display;

For Counter := 1 to Count({?Name}) Step 1 Do
(
Display := Display & Name[Counter] + &quot;, &quot;
);

Mid(Name,2);

We're using {@ParameterDisplay} because the order of the parameter values and the order of the instructors derived from the subreport are ignorant of one another. So, we have to sort the parameter values so that the parameter values are alphabetical, as per the subreports group order. Thus, you should avoid instructors being assigned to the wrong location.

All the best,

Naith