cancel
Showing results for 
Search instead for 
Did you mean: 

How can I pull info into a report using just the first two characters of that field?

steverdan
Participant
0 Kudos

I am trying to pull a total cost based upon locations in a particular area(s). Can I get the formula to just do this based on the first two characters of that field?

Here is what I have so far:

if {PACKED.LOCATIONS.NEXT OPERATION} = ["xx"] THEN {Job.Total_Price}

Packed locations is a table I'm pulling from and the column I want just the first two digits is the the Next operation. Once this meets the "xx" (whatever location) then give me the total price from the Job table.

This works for one or two orders, but then blows up when I do 10 or so.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Daniel,

I have attached your report back with the proper fields added to the proper places.

What I did was simply that I suggested above and created my own running total via a formula (TotalValue) and a Parameter (Operation Reference).

Afterward I added the TotalValue onto the details and Report Footer.

//

whileprintingrecords;

global numbervar TotalValue;

if {?Operation Reference} in {PACKED_LOCATIONS.NEXT OPERATION} then

     TotalValue := TotalValue + {@Total};

TotalValue;

//

steverdan
Participant
0 Kudos

Not working. This total is even further off than my original total. The total for the "CL" area should be less that 10,000, but this formula has it at 78,000

abhilash_kumar
Active Contributor
0 Kudos

Hi Steve,

I had a look at the report and I'm not quite sure what you're after.

The formula 'Prem Nut Totals' formula is what feeds the summary and this is the code in it:

if {PACKED_LOCATIONS.NEXT OPERATION} = ["PN"] then {Job.Total_Price}

The total on the Report Footer reads '$16275' which, as far as I can see from the records on the Details section for the group 'PN', is correct.

What value are you expected as the Grand Total for this formula?


What logic are you trying to implement to feed to the summary?

-Abhilash

steverdan
Participant
0 Kudos

Some of the fields are correct, but there were some that were not. Once I found out how to display what was being totaled, I found the problem and fixed it. I figured out that the total that was being pulled in was the order total including the monies for partial shipments. I wanted it without the partials. Formula adjusted and everything works.

abhilash_kumar
Active Contributor
0 Kudos

Great!

-Abhilash

Former Member
0 Kudos

Great! If I helped you out please feel free to mark my answer as helpful or whatever.

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Steve, assuming that Next Operation is a string field...

Create a new formula and place it in the field where you see the {Job.Total_Price} field (Detail or Group depending on your report) with the formula:

//

whileprintingrecords;

global numbervar TotalValue;

if len({? Replace me}) <= len({PACKED.LOCATIONS.NEXT OPERATION}) then(

if left(ucase({PACKED.LOCATIONS.NEXT OPERATION}),len({? Replace me})) = ucase({? Replace me}) then

TotalValue := TotalValue + {Job.Total_Price}

);


TotalValue;

//


You can go ahead and suppress that field.


Create a new formula to place where you want to view the summary using the formula:


//

whileprintingrecords;


global numbervar TotalValue;

//


This method allows for a higher degree of flexibility and you can search for the first x characters of that field instead of only the first 2 characters



former_member205840
Active Contributor
0 Kudos

HI

Use below formula to extract first two characters :

if left( {PACKED.LOCATIONS.NEXT OPERATION},2)  = "xx"  THEN {Job.Total_Price}

else 0


Thanks,

Sastry

DellSC
Active Contributor
0 Kudos

If you want to look at just the first two characters, try something like this:

If left({PACKED.LOCATIONS.NEXT OPERATION}, 2) = "xx" then {Job.Total_Price}

This assumes that {PACKED.LOCATIONS.NEXT OPERATION} is a string.  If it is a numeric field, you'll have to convert it to text like this:

If left (ToText({PACKED.LOCATIONS.NEXT OPERATION}, 0, ""), 2) = "xx" then {Job.Total_Price}

-Dell

steverdan
Participant
0 Kudos

That made the blow up worse. How can I view the orders numbers that this is pulling in?

DellSC
Active Contributor
0 Kudos

How did it "blow up"?

To see the order numbers, put them in the details section on the report.

-Dell

steverdan
Participant
0 Kudos

First off, (blew up) the totals went from a few hundred dollars off (like $900 to $2300) to a few thousand (now almost $800). I re-sorted the details (which are viewable) using the next operation and subtotaled to see what I am missing. I have what it should be adding up with a subtotal temporarily below that section. The report will be sorted by customer when finished.

The problem I seem to be having, is that it appears to be adding totals that are not part of the location in question or not displayed on the report (if that is possible).

BTW thanks for the help!!

DellSC
Active Contributor
0 Kudos

How are you calculating your totals and subtotals?  Are you using the Summary functions (Sum, etc.), Running Totals, or are you calculating them manually?  If you're using Running Totals, please provide information about how you're resetting them.  If you're calculating them manually, please post the formula(s) you're using.

-Dell

steverdan
Participant
0 Kudos

I'm using the summary feature and verified it with manual calculations.

DellSC
Active Contributor
0 Kudos

Can you save the report with data and post it here so that I can look at it?  To post it, change the .rpt file extension to .txt.  Then use the "Advanced Editor" here to attach the file.

-Dell

steverdan
Participant
0 Kudos

Not sure how to change the .rpt to .txt

DellSC
Active Contributor
0 Kudos

Just rename it in Windows Explorer - you'll need to turn off "Hide file extensions..." in the Explorer options.

-Dell

steverdan
Participant
0 Kudos

See if this is what you need.

DellSC
Active Contributor
0 Kudos

Not quite.  You exported the report as text, which is not what I'm looking for.  I need the actual .rpt file which has been saved with "Save data with report" turned on.  However, SCN won't allow you to upload a file with the .rpt extension.  Instead you have to manually reset the extension to ".txt" in order to upload it.  When I download it, I'll reset the extension to ".rpt" and then open it in Crystal.

-Dell

steverdan
Participant
0 Kudos

This should do it.

DellSC
Active Contributor
0 Kudos

Perfect!  Here's what the problem is...

If you have more than one value between the brackets, you need to use "in" instead of "=".  This will cause Crystal to check and see whether the value is in the array.

-Dell

steverdan
Participant
0 Kudos

Totals are still not matching up.