Concatenate Prefix, First, Last, Suffix, Title, Organization

[FranS37]

Need a formula to concatenate Prefix, First Name, Last Name, Suffix, Title and Organization. Want to strip out commas where necessary. For example, if there's no first and last name, then organization, if there is a first and last name but no suffix or title or organization. There are many permutations. Has anybody done something like this?

Thanks.

 

[butkus]

I thing I may have done something similar. I had address elements to call into some letters. The elements were street number / street direction / street name / apartment number.

Most, but not all elements contain data all the time - so I had to test for null and assign an empty sting ("&quot when test waas true. All of the elements were reassigned to shared variables and then I contatenated to the shared variables in another formula

Example of main formula (assigns variables):
street_num :={VCMAST.VC_M_ADR_STNO};
If IsNull({VCMAST.VC_M_ADR_DIR1}) then
street_direction := "" else
street_direction :=({VCMAST.VC_M_ADR_DIR1} + ". "
street_name :=LeadingCaps({VCMAST.VC_M_ADR_STR1});
If IsNull({VCMAST.VC_M_ADR_APT}) then
apart_number := "" else
apart_number :=(",# " + {VCMAST.VC_M_ADR_APT});
city :=LeadingCaps({VCMAST.VC_M_CITY});
state :={VCMAST.VC_M_ST};
zip :={VCMAST.VC_M_ZIP};

Example of concatenting formula:
Shared StringVar vic_name;
Shared StringVar addressee;
Shared StringVar street_num;
Shared StringVar street_direction;
Shared StringVar street_name;
Shared StringVar apart_number;
Shared StringVar city;
Shared StringVar state;
Shared StringVar zip;
(addressee) + chr(13) +
(street_num) + " " + (street_direction) + " " + (street_name) + " " + (apart_number) + chr(13) +
(city) + " " + (state) + " " + (zip);


Hope this helps.
James Carruth
Network Administrator

 

[FranS37]

Thanks for the help. This is what I did, but no results are returned:

First Formula:

If isnull({ScheduledPeople_ttx.Prefix}) then
stringVar Prefix:="{ScheduledPeople_ttx.DisplayName}" else
{ScheduledPeople_ttx.Prefix} & " " & {ScheduledPeople_ttx.DisplayName};

If isnull({ScheduledPeople_ttx.Suffix}) then
stringVar Suffix:="" else
", " & {ScheduledPeople_ttx.Suffix};

If isnull({ScheduledPeople_ttx.Title}) then
stringVar Title:="" else
", " & {ScheduledPeople_ttx.Title};

If isnull({ScheduledPeople_ttx.Organization}) then
stringVar Organization:="" else
", " & {ScheduledPeople_ttx.Organization};

Second Formula:

Shared StringVar Prefix;
Shared StringVar Suffix;
Shared StringVar Title;
Shared StringVar Organization;
(Prefix) & " " & (Suffix) & " " & (Title) & " " & (Organization)


Grateful for any help.

Fran Smith

 

[butkus]

The only thing I see that is different, is that my formula references the string variable twice for assignment and yours does not (oh and the "+" vs. "&" - but that should'nt matter).

YOURS:
If isnull({ScheduledPeople_ttx.Title}) then
stringVar Title:="" else
", " & {ScheduledPeople_ttx.Title};

MINE (using my logic):
If isnull({ScheduledPeople_ttx.Title}) then
stringVar Title:="" else
stringVar Title:=", " & {ScheduledPeople_ttx.Title};

This may have something to do with it - but I'm not sure.

James Carruth
Network Administrator

 

[rogar]

James is right - you must repeat "stringVar Title:=", " in the else clause of your if..then..else statements.

 

[Ngolem]

Hi Fran

You don't have to do this in 2 formulas ...and you have the variables declared as "shared"...this is only supposed to be done when you are 'sharing' variable values between a subreport and a main report...and I don't think that is what you want.

Also you wrapped double quotes around string fields or didn't assign anything to the variables and that is not kosher

So let us redo your formula and I will call it

@information

Whileprintingrecords;
//initialize the variables to null to start
StringVar Prefix := "";
StringVar Suffix := "";
StringVar Title := "";
StringVar Organization := "";

//the additional test is for users who put a blank space
//in for a field so they can get around a "NOT NULL"
//condition in the database.

If isnull({ScheduledPeople_ttx.Prefix}) or
length(trim({ScheduledPeople_ttx.Prefix})) <> 0 then
Prefix := {ScheduledPeople_ttx.DisplayName}
else
Prefix := {ScheduledPeople_ttx.Prefix} + &quot; &quot; +
{ScheduledPeople_ttx.DisplayName};

If isnull({ScheduledPeople_ttx.Suffix}) or
length(trim({ScheduledPeople_ttx.Suffix})) <> 0 then
Suffix := &quot;&quot;
else
Suffix := &quot;, &quot; + {ScheduledPeople_ttx.Suffix};

If isnull({ScheduledPeople_ttx.Title}) or
length(trim({ScheduledPeople_ttx.Title})) <> 0 then
Title := &quot;&quot;
else
Title := &quot;, &quot; + {ScheduledPeople_ttx.Title};

If isnull({ScheduledPeople_ttx.Organization}) or
length(trim({ScheduledPeople_ttx.Organization}))<> 0 then
Organization := &quot;&quot;
else
Organization := &quot;, &quot; + {ScheduledPeople_ttx.Organization};

Prefix + &quot; &quot; + Suffix + &quot; &quot; + Title + &quot; &quot; + Organization;

that should work...not I removed the brackets from the variables...they aren't necessary. this formula could be bullet proofed a bit more by testing {ScheduledPeople_ttx.DisplayName} for being null...but I'll leave it for you to do that if necessary
Jim Broadbent

 

[FranS37]

Jim:

Thanks for your response and help on this. I now have DisplayName displaying but not the other fields. I have to work on this further, but I got sidetracked on something else.

We have a client with an old VB version 3 application which runs Crystal Reports. It looks like they're Crystal Version 3. They want changes to the reports, small stuff, but of course when I make the changes, and save the reports they're converted to the version of Crystal I'm running and then the app. won't open them.

Short of rewriting the whole app. is there anything else I can do? We don' know anybody with an old version of Crystal.

Thanks again.

Fran

 

[Ngolem]

version 3 is pretty old (at least 4 years old, I think) You were pretty limited in a lot of ways back then.

My suggestion to you is to try to get them to upgrade their crystal to a more current version if you are redoing their reports...Aside from the fact that you may use functions that are not compatible with CR3 I am not sure what happens when you save a cr8.x version when run on cr3...even if you don't use anything new to CR3

There is a potential negative to the upgrade though. Be warned that many of thier existing reports may not run in a CR 8.X environment...this is not a problem if you are upgrading all of their reports since you would have to massage them anyway....but if the client is expecting all their reports to run better in the new environment...well that may not happen....this is because many older CR functions have changed or been discontinued (for the better) but that means you must test all of the old reports and then resave in the newer format.

the cost of the upgrade would probably be much less than the time you would have to charge to work on older CR3.0 versions...as well you would be able to give them more functionality....but of course that all depends on money and the scope of the project.

AT THE VERY LEAST...I would get a listing of the functions and operations that CR3 has to work with and try to stay within those constraints...but like I said...there are no garuantees. Jim Broadbent

 

[rogar]

Fran says her client has &quot;...an old VB version 3 application...&quot; which displays the old Crystal Reports, probably with an old version of the OCX (or maybe with the old CR API). The revised reports saved as CR 8.5 are apparently incompatible with the old CR integration.

Fran, have you tried saving the revised reports as Version 7? Or possibly the app is using the CR version (4.6?) that used to come with VB - there are still copies of that around. If the client has the old VB project source code, you would be able to upgrade CR and recompile the VB app after upgrading the OCX. If the old source code is unavailable, look into getting a cheap report viewer.