Personalization, Customization and Productivity Tools

[csmith]

Our client has Misc. Item Codes for Comments (/C01, /C02 etc).  On the data entry screens the fields look like this:

Contract Date:               12/31/11

Location:          West Side of Building C

Service Agreement:               Y277789838388

PON:                    LRVN-0009-KJFJ888-2709

Subscribers:          27

and so on.

On the Crystal invoice, I need to list the portion of the comment  up to & including the colon separately from the info on the right side of the colon (block style) :

Contract Date:                                                         12/31/11

Location:                                       West Side of Building C

Service Agreement:                                 Y277789838388

PON:                                        LRVN-0009-KJFJ888-2709

Subscribers:                                                                       27

I have tried a number of formulas (left, right, mid, etc), but haven't been able to come up with the right one.  I would greatly appreciate any help.

[rmikolainis]

I'm not totally following what you are trying to do, but I think I might have done a similar formula.

I had names formatted as "Smith, John" as a names field.

 and i wanted to break them out in Crystal into First name and Last Name columns.

Are you trying to break out the "12/31/11" from the "contract date:"?

[csmith]

Yes, that's exactly what I'm trying to do. 

[Gator]

Is there any consistency in the format of the data?

[Gator]

Also, do you have to use a variable width font or can a fixed width font like Courier be used?

[csmith]

The only thing that is consistant is there is always a colon after what I consider the Description (Contract Date:, Location:, etc.) and there are never any colons in the data section (12/31/11, etc.)

[csmith]

The client wants the font to be consistant throughout the form, which is currently Times New Roman, font size 9

[connex · Accepted solution]

I would split the comment description into two formulas and set their alignment left and right.

The first formula could be:

If {SO_InvoiceWrk.ItemType} = "4"
    Then (
            Local NumberVar ColonPosition := Instr({SO_InvoiceWrk.ItemCodeDesc},":");
            Left({SO_InvoiceWrk.ItemCodeDesc}, ColonPosition)
          )
    else ""

 ..and the second could be:

If {SO_InvoiceWrk.ItemType} = "4"
    Then (
            Local NumberVar ColonPosition := Instr({SO_InvoiceWrk.ItemCodeDesc},":");
            Right({SO_InvoiceWrk.ItemCodeDesc},Len({SO_InvoiceWrk.ItemCodeDesc})-ColonPosition)
          )
    else ""

 I would then use a separate formula for non-comment descriptions.

[rmikolainis]

Here is how I did it, You should be able to figure out how to get this to work for you. For example, in my @step1 formula, instead of the InStr({@Full Name}," "), you would use InStr({@Full Name},":") , and of course use your own field/formula instead of @Full Name.

@Step1

WhilePrintingRecords;
InStr({@Full Name}," ")

//this step looks for the first blank character in the @full name

@Full Name

if {PA Profile Contacts.Name}="" then "No Name" else TrimLeft ({PA Profile Contacts.Name})
//Basically, this trims the name field to eliminate blank spaces before the name

@First Name

WhilePrintingRecords;
{@Full Name}[1 to ({@Step1}-1)]

//this creates the first name field

@Last Name

WhilePrintingRecords;
TrimLeft ({@Full Name}[({@Step1}+1) to length ({@Full Name})])

//this makes the last name field

[csmith]

Thanks to all for your replies.  Dan, you replied first & I tried your solution and it worked perfectly.   I appreciate everyone's prompt responses.