Solved

Counting then adding 2 fields

Posted on 2014-12-29
6
90 Views
Last Modified: 2014-12-30
I have 2 Fields, {CardNumber} and {FacilityCode}. The {CardNumber} field range from 0 - 9999 (4 digits) and I first need a formula to check the count of digits for the {CardNumber} field and buffer an additional 0(s) in front so there is always 5 digits and combine it as {FacilityCode} + {CardNumber} (See examples below) :

{CardNumber}   'BufferedValue'  {FacilityCode}   CombinedResult
29                               00029                         200                     20000029
229                             00229                         201                     20100229
2229                           02229                         200                     20002229
0
Comment
Question by:John-S Pretorius
  • 3
  • 2
6 Comments
 

Author Comment

by:John-S Pretorius
ID: 40522628
The solution is actually embarrassingly easy to mention but I would still like to see what a Crystal reports solution would come to. By adding 2000000 to the {CardNumber} in excel I was able to create a whole column automatically :

2000000 + 2229 = 2002229
2010000 +   229 = 2010229
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 40523080
You could do the same thing in Crystal

10000 * {FacilityField} + {CardNumberField}

Another way would be

CStr({FacilityField},0,'') + Right("00000"& CStr({CardNumberField},0,''),5)

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 40523403
FWIW, I don't see how "adding 2000000 to the {CardNumber}" would produce the results you described.

 mlmcc's suggestion to multiply FacilityCode and then add them together might be the best solution, but, FWIW, another approach would be:

CStr ({FacilityCode}, "#") + CStr ({CardNumber}, "00000")

 James
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:John-S Pretorius
ID: 40523447
Thank you guys, these solutions is what I was hoping to see. James, FWIW - the solution as described truly works as simple as it sounds, it's more work though as I had to sort all Facility Codes (200,201 and 203)

Excel format
0
 

Author Closing Comment

by:John-S Pretorius
ID: 40523449
Thank you.
0
 
LVL 34

Expert Comment

by:James0628
ID: 40523544
The thing is, you said that you added 2000000, but then one of your examples is 2010000 + 229.  Adding 2,000,000 (or, technically, 20,000,000) only works when the Facility_Code is 200 (which it happens to be in every line in your screenshot, so, yes, it works in those particular lines).

 James
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question