[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Counting then adding 2 fields

Posted on 2014-12-29
6
Medium Priority
?
98 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 101

Accepted Solution

by:
mlmcc earned 2000 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 35

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
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 

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 35

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

865 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