Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Counting then adding 2 fields

Posted on 2014-12-29
6
Medium Priority
?
96 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
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 course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

636 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