Sage 100

Something just doesn't seem right. Having issue on both the client's system (4.20.0.9) and my local ABC data (4.30.0.22). Using Crystal 10 for the client's custom Crystal Report request and having trouble, so I decided to dumb things down a bit and try to isolate the issue. So I'm trying to simply get data from IM_13: Cust Div #, Cust #, and Ship-To Code - along with Ship-To City from SO_ShipToAddress. So I'm linking div #, cust #, ship-to-code from IM_13 to Ship-To file. And I'm using a Left Outer Join (LOJ) because I still want to see the IM_13 data even if no respective Ship-To exists. The problem is that the City data won't print (or any other Ship-To file data), but it does if I happen to remove the div # or cust # link. Somebody please tell me I'm just overlooking something. Been looking at this way too long and have gotten quite punchy.

What is your selection criteria?

None

Very basic report. No selection, no parameters, no sorting, no formulas.

The ShipTo code is maintained differently between these two tables so you will need to remove your link between the ShipToCode fields and use a criterial to soft link them.

Use this in your criteria instead of linking by the ShipToCode: if not(isnull({SO_ShipToAddress.ShipToCode})) then Trim({IM_13CustomerSalesHistory.ShipToCode}) = Trim({SO_ShipToAddress.ShipToCode}) else True

Was this fixed in V4.4? If so, might be time to upgrade them to V4.4 (or even v4.5). What about just pulling the data from the AR_invoicehistory table?

@DanBurleson is right. It's about how the ShipToCode is maintained differently not what I said (and have since deleted) . <walking back slowly sneaking away>

Why not just pull the data from AR_InvoiceHistory with Crystal or use Pivot tables?

IM_13 is already summarized so on Crystal it should be way faster than totaling details I would think.

That is what I figured when I looked at the file spec. Dan or Alnoor, how is the data different? I don't see it just looking at the file spec.........

IM_13 is the old summarized file where each record contained a field for each period and could easily get out of sync, whereas ItemCustomerHistoryByPeriod has a field for year and a field for period which usually requires the report be re-written. I sometimes use a crosstab query to recreate the IM_13 layout from the ItemCustomerHistoryByPeriod table to avoid the re-write.

Sweet! Thanks, all!