Joined: 14 Aug 2008
Location: United States
Online Status: Offline
Posts: 10
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
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
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...
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>
Joined: 14 Aug 2008
Location: United States
Online Status: Offline
Posts: 10
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
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
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>
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
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:
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