| Author |
Message |
CooleyBe
Newbie
Joined: 25 Oct 2011
Location: United States
Online Status: Offline
Posts: 4
|
  
Topic: Field being repeated
Posted: 25 Oct 2011 at 2:10pm |
First off, I'm using Crystal 10 for Sage (used with the program MAS 90). I trying to change a report we have that prints our Purchase Orders (PO). This field is supposed to be able to tell when a part on the PO has a vendor alias (vendor specific part numbers). The original form prints just our part number no matter who we are buying from. I am trying to change that field so that it will print the alias number if one exists, and to print our part number if one doesn't.
So far, I have the PO table left joined to the Alias table by part number. My field, located in 'Details A' of 'Group 1', consists of this formula:
WhileReadingRecords;
if (trim({IM_04AliasItemNumber.AliasItem_No}) = '' or isNull({IM_04AliasItemNumber.AliasItem_No})) then
{PO_20CRWPurchOrderDetail.ItemNumber} else {IM_04AliasItemNumber.AliasItem_No}
Now, as far as I can tell this is correct. But, what happens is strange. If there is no alias, then the report prints fine with just our part numbers. If there is an alias, then first it prints the entire 'Details A' for our part number, followed by the entire 'Details A' for the alias number. Any help would be appreciated.
|
|
--
Don't Panic
--
|
 IP Logged |
|
|
|
SamD
Newbie
Joined: 24 Oct 2011
Online Status: Offline
Posts: 15
|
Posted: 26 Oct 2011 at 3:51am |
Try removing:
or isNull({IM_04AliasItemNumber.AliasItem_No}))
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 27 Oct 2011 at 3:47am |
always check for IsNull first. The analogy the I use is CR will throw an error if a field is null and you are asking for a value and then checking for the null value. if you check for the null value first, CR will not throw the error and continues as we think it should. either way, CR doesn't let us know that it threw an error, it just processes funny. Thanks to DBlank for the tip HTH
|
| IP Logged |
|
CooleyBe
Newbie
Joined: 25 Oct 2011
Location: United States
Online Status: Offline
Posts: 4
|
Posted: 27 Oct 2011 at 1:37pm |
|
Thanks, for the reply, but neither solution worked. First, I tried with isNull first, but same error. So, I tried removing it and still have the same thing happening.
|
|
--
Don't Panic
--
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 28 Oct 2011 at 11:45am |
the last suggestion/first tip in debugging is start creating multiple formulas that are actually just parts of the one you really want to use and see what they produce...I have been wrong so many times... so: instead of: if (trim({IM_04AliasItemNumber.AliasItem_No}) = '' or isNull({IM_04AliasItemNumber.AliasItem_No})) then... have 2 that look like: (trim({IM_04AliasItemNumber.AliasItem_No}) = '' and: isNull({IM_04AliasItemNumber.AliasItem_No}) see what they are returning (displaying) along with the simplest of all {IM_04AliasItemNumber.AliasItem_No} now you can compare what CR is seeing and what you want, and you can start modifying your formulas until everything is working as you desire. HTH
|
| IP Logged |
|
CooleyBe
Newbie
Joined: 25 Oct 2011
Location: United States
Online Status: Offline
Posts: 4
|
Posted: 02 Nov 2011 at 8:34am |
|
Ok, I tried different bits of the formula with mixed results.
First, I tried just using:
(trim({IM_04AliasItemNumber.AliasItem_No})
to see what happened. I got two 'Details A', one with the part number and one with the alias number, just like I was before. There was no if statement or anything in this test. There was also nothing in the field that had {PO_20CRWPurchOrderDetail.ItemNumber} in it anywhere.
Next, I tried removing the field completely. The rest of 'Details A' printed correctly, without any part number at all, and the 'Details A' did not repeat itself.
Next, I tried with just:
{PO_20CRWPurchOrderDetail.ItemNumber}
The Item number printed and there was no repeat 'Details A'. This is the way the form was originally set up.
Last, I tried setting the field to either 1 or 0 and using the display string to display either {IM_04AliasItemNumber.AliasItem_No} or {PO_20CRWPurchOrderDetail.ItemNumber} depending on the value. Once again, the 'Details A' repeated itself.
From this testing, it seems like Crystal is receiving the both the item number and the alias number when (as far as I can tell) it should only be receiving the alias number. However, it is correctly receiving just the item number. Whether it is Crystal requesting both the alias and item or Mas90 sending both of them, I don't know. But, whenever Crystal receives both, it prints both. Any more ideas would be appreciated.
Edited by CooleyBe - 02 Nov 2011 at 8:37am
|
|
--
Don't Panic
--
|
| IP Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
|
Posted: 02 Nov 2011 at 10:27am |
yeah, now you got me, as I have no idea as to why your data looks as it does... I have found that breaking up the formulas allows me to see what CR is doing and attempt to craft a better solution based on the new information. Luckily for me, all of my reports are from stored procs so I know where the data is coming from. Since you are getting both values when only 1 is expected, it usually points to a join somewhere since you are now getting 2 values instead of 1...2 actual physical rows in the dataset, which is probably where I would start searching...at least as far as duplication is concerned. HTH
|
| IP Logged |
|
CooleyBe
Newbie
Joined: 25 Oct 2011
Location: United States
Online Status: Offline
Posts: 4
|
Posted: 03 Nov 2011 at 11:24am |
|
Ok, so...
After all that work, I finally got it working. I found a field in the PO_20CRWPurchOrderDetail table called VendorAlias_Number. I had looked through the fields before, but apparently missed it. With a simple If_else statement, the whole thing works, without trying to join anything. Thanks for all you guys help!
|
|
--
Don't Panic
--
|
| IP Logged |
|
|
|
