Active TopicsMemberlistSearchHelpRegisterLogin

Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Tips and Tricks
Message Icon Topic: Formula Help Post Reply Post New Topic
Author Message
Bryan
Newbie
Newbie
Avatar

Joined: 14 Aug 2008
Location: United States
Online Status: Offline
Posts: 10
Quote Bryan Replybullet Topic: Formula Help
     Posted: 19 Aug 2008 at 10:23am
Hi,
 
  I need a formula to check if a UPC is Valid or Invalid. Here is an example of one of our valid UPC numbers: 664232041103. A UPC is valid if you add digit 1,3,5,7,9,11 multiply by 3 and then add that to the sum of digits 2,4,6,8,10 THEN add digit 12 and the total should be divisible by 10. So if anyone wants to take a whack at this one I would be greatful. Thanks
 
Bryan
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 19 Aug 2008 at 11:13am
haha. This is funny. I had no idea you do use this formula to validate whether a UPC is valid. Cool.
 
Here is a formula I would use. I'm not testing this, so there is probably a small bug. But I'm taking what you said and just writing what I would do off the top of my head. It should be pretty close the final version and you can tweak it accordingly. Here you go...
((((Val(Mid({table.UPC}, 1)) + Val(Mid({table.UPC}, 3)) + Val(Mid({table.UPC}, 5)) + Val(Mid({table.UPC}, 7)) +
Val(Mid({table.UPC}, 9)) + Val(Mid({table.UPC}, 11))) * 3)
+ (Val(Mid({table.UPC}, 2)) + Val(Mid({table.UPC}, 4)) + Val(Mid({table.UPC}, 6)) + Val(Mid({table.UPC}, 8)) + Val(Mid({table.UPC}, 10)))
+ Val(Mid({table.UPC}, 12))) MOD 10) = 0
Man, there's like a hundred parentheses in this. I'm sure I screwed a few of them up. So you'll have to double check that they are all in place and that an openning parentheses has a matching closing parentheses. But overall this should be pretty good.
 
My Encyclopedia book has three chapters dedicated to helping you learn how to write formulas and gives sample code with it. You can find out more about my books at Amazon.com or reading the Crystal Reports eBooks online.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
Bryan
Newbie
Newbie
Avatar

Joined: 14 Aug 2008
Location: United States
Online Status: Offline
Posts: 10
Quote Bryan Replybullet Posted: 19 Aug 2008 at 12:28pm

Thanks Brian,

  I think this is pretty close, except when I put this formula onto the report the only UPC'S that returned a true value were the ones that had a check digit of either 0 or 5. That should not be the case. Let me know if you have any more thoughts. Thanks

Bryan
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 19 Aug 2008 at 12:59pm
Unfortanately, I would have to create a test report with sample data to figure this out. I simply don't have the time to debug this in detail. I suggesst breaking out the formula into separate calculations (one for the odd digits, one for the even digits) and then creating a fomula using those variables. That would make it easy to test which part of the formula is working and which is returning the wrong value.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 28 Aug 2008 at 6:53am
I was looking at the formula, and it looked right, I was just about to close it when it dawned on me what is missing...each of the mids should end , 1.  The current formula is going from say the 3rd int to the end, it should only be adding the 3rd char...so the formula should look like:
 
((((Val(Mid({table.UPC}, 1, 1)) + Val(Mid({table.UPC}, 3, 1)) + Val(Mid({table.UPC}, 5, 1)) + Val(Mid({table.UPC}, 7, 1)) + Val(Mid({table.UPC}, 9, 1)) + Val(Mid({table.UPC}, 11, 1))) * 3)+(Val(Mid({table.UPC}, 2, 1)) + Val(Mid({table.UPC}, 4, 1)) + Val(Mid({table.UPC}, 6, 1)) + Val(Mid({table.UPC}, 8, 1)) + Val(Mid({table.UPC}, 10, 1)))+Val(Mid({table.UPC}, 12))) MOD 10) = 0
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.