Sage 100

Non-urgent. Have a client running Sage 100 Std - 2016 PU3. I had to import a General Journal Entry. Client has a 17-digit GL Account. The Journal Entry Import requires the account key. In the past, I have given the import either the raw account or the formatted account, and it was smart enough to figure out the account key. This time, with the longer GL account, the import appeared to look at the first 9 characters of the formatted account, and converted some to keys, but not those that had anything other than zeros in positions 10 thru 17 of the formatted account. I could not override the incoming field length (for the formatted GL account) to more than 9 digits. I did a manual conversion with lookups in Excel to get the 9 digit key, and imported that. I was just wondering if I missed something simple that might have allowed me to avoid all the extra work.

Mark the checkbox to skip source data truncation on the configuration tab.

Thanks Larry.

You tried it already? Did that solve the issue? It of course can impact other fields but in many cases it does the trick for that GL issue.

There are two import formats that you can use to import the GL account number. Here is a chart from the on-line help instructions.

Yes, It worked perfectly. I was only importing account and amount. Everything else was default value, so no worries about other data fields . Just 1500 account balances to bring in.

Can't you do a file assign and retrieve the key from the account master?

Doug, I think the file assign would assume I have the account or account key to look up in the first place. The problem was it was only taking the first 9 characters of what I was importing.

Michael, I was using VI.

Yes you would need the full account number. I see the issue now. Glad it is working.

So the import would ignore the field size override in the VI job setting? I've seen jobs where it would lose the max field size and I would have to reset it but it would usually accept it again.

Imports into GL key fields will auto-substitute if you import the account # instead, but if your account number is more than 9 digits it is truncated, because the key field is 9 digits... unless you skip source data truncation, to allow the auto-translation from full account # to key.

What has worked for me in the past was to import into a temp field then import the temp field into the GL account as a calculated value.

In the Import Field Properties for the AccountKey, I have checked the Source, Substring box and changed the Length and Max to what I needed. Problem with this is that if you open the Field Properties again, the lengths reset.

Jane, That's where the ""source data truncation"" checkbox, that Larry suggested, overrides those incoming data limits.