Jiwa

in Module "ReadRecordFromCreditorID".

I've had a quick poke around, but can't find any orphaned CurrencyID values; and certainly all creditors with non-null CurrencyID have a valid one.

Any suggestions on where to look?

Assuming it's a fairly recent version, there's not many places to look.

The query which causes the exception is:

Code: Select all

SELECT RecID, Name, ShortName, DecimalPlaces, IsEnabled, IsLocal FROM FX_Currency WHERE RecID = (SELECT CurrencyID FROM CR_Main WHERE CreditorID = @CR_Main_RecID)

If you profile trace you should see the @CR_Main_RecID being used - have a look at the currencyID of the CR_Main record with that @CR_Main_RecID - it might be NULL which could have let it evade your orphan search.

It's 7.1 SR5.

There are lots of null CurrencyID records in CR_Main.

Can I just set them all to our local currency?

pricerc wrote:It's 7.1 SR5.

No it isn't.

pricerc wrote:There are lots of null CurrencyID records in CR_Main.

Can I just set them all to our local currency?

Are there any where the CurrencyID IS NULL AND UsesFX = 1 ?

If so, then set UsesFX to 0 where the CurrencyID is null, or set the CurrencyID to a valid value.
Setting it to the local currency in those cases probably wouldn't do any harm - but it would mean pointless attempts at gain / loss calculations - so setting UsesFX to 0 is better.

My bad - SR6.

Ok, this is still a test database; there are 7 (out of 1000's) where UsesFX == 1 and CurrencyID is null.

I've reset them for now; will be more accurate for the live upgrade.

Thanks Mike.

pricerc wrote:My bad - SR6.

No it isn't.

We didn't have service releases for 7.1!

Mike.Sheen wrote:

pricerc wrote:My bad - SR6.

No it isn't.

We didn't have service releases for 7.1!

Lol.

I've only just got back online. It was about 1/2 an hour later, Trev and I were leaving the client and I said: "Doh!, we're on 7.2, not 7.1!".

In that 1/2 hour, we were catching up with the client.

Turns out they have some creditors who they used to pay in foreign currency, but they changed to local. Apparently they weren't able to change the 'uses FX' option, so just changed their currency to 'NZD'. While we were watching, he tested it in 7, and provided the balance is zero, he can change the FX setting. I don't know if that's something they hadn't tried in 6, or if the option just wasn't available.

Anyway, I know what the problem is. I have a little "pre-upgrade" script I've been working on (I've done several tests now), I'll add this to it.