Sage 100

I have a customer on version 4.20.26 having an issue with an import to AR_OpenInvoice. We are trying to overwrite existing records with varying lengths of invoice numbers. The problem is the import to AR_Open adds however many zeros to the begininng of the invoice number it needs to make the length 7. So, the overwrite we are hoping for won't happen. I have tied my small bag of tricks and haven't found the answer. Has anyone ran into this before and know how to correct?

You could do multiple Assigns on the invoice number where you do a calculated field with the 0'S added yourself, based on the length of the import field. (LEN command)

Thanks @BethBowers, but I think I have the opposite problem. The invoice number on the existing record is 062606. I'm trying to overwrite that record to change the invoice balance and the import file has the invoice as 062606. When I import, the system sees the invoice as 0062606. I tried lowering the length of the invoice number on the VI import and that didn't work.

OH, I get it. How did those 6 character invoices get in there in the first place?

They came in from Retail Pro.

Try this not 100% sure if it will work. Replace the Invoice No into a Temp field. Then choose Invoice No again but it will be a CALC like this PAD(Temp001$,7,$00$) Change Temp001$ to the correct number if needed. What this does is pads the 6 digit number to the right with the Null character If this works and assuming you're doing in a test company do a cash receipt on the invoice, print the register, update it. Make sure it worked right.

Thanks @AlnoorCassim. We gave it a try but the import won't read the Temp field for the invoice number - we get the message ""the invoice number is required"". I tried changing the position of the temp field in the import but that doesn't seem to matter. I've enclosed the import and a sample of the file in case you get the opportunity to take a look. I must have something wrong in the import.

Change your temp field from Standard Record to Key Only.

In addition to this. Business Objects come into play. VI will always add the leading zero (the same as if you manually enter without leading zeros it will fill.

@JaneCavanaugh Will take a look. We may need to temporarily modify dictionary.

Thanks @LarryBradford and @AlnoorCassim . Changing to Key Only did the trick for VI to read the record, but VI still adds leading zeros to the invoice number to make it 7 digits.

Jane looks like it can be done with either Perform Logic or dictionary change. Looks like the latter is the better way but I'm leary of this change too. If you go forward with steps below, test the short invoice number in cash receipts journal and update - verify AR4 and AR6 get updated properly and that it doesn't try to create a new key using a leading zero 7-digit InvoiceNo even though you selected the 6 or less digit numeric Invoice No. What you have to do with AR_OpenInvoice in the dictionary is remove it's association with the Format Type called MASTERNUM - it needs to be blanked out. This can be done with DFDM. If you follow this thread, although it's for a different table and different field it has the same leading zero issue because its similar Format Type called CHARNUM Just follow the steps but in Step 3 use AR_OPENINVOICE and Step 7 use InvoiceNo$ https://90minds-com.socialcast.com/messages/14408130 BTW the part where it mentions to update the physical file you don't need to do that for this.

@AlnoorCassim, you are brilliant! Testing now, but it looks like the dictionary change worked.