Sage 100

Are the hyphens consistently in the same position in the source file customer number? 

What is failing exactly?  can you attach a screen shot of the log records and what shows if you double click on one of the failed lines ?

Are the hyphens consistently in the same position in the source file customer number? 

Maybe you cannot use a calculated field on customer number?

Steps

1. Create a temp field & place at the top (I've also placed between Division and Customer No)
2. For CustomerNo, use Calculated for operation and put in Temp field.

Questions 

• The hyphens are not in the same spot on each customer number.
• When I got to import, the Customer No record fails and says "The customer number is required". (see below screenshot)
• The import fails on the customer number, division works fine. 
• I'd call it a day if it was a one off, but this will be a 3 part import (AR Customer, AR Invoice and AR Cash Receipts) referencing the customer number in each source file and ran daily. 

What is failing exactly?  can you attach a screen shot of the log records and what shows if you double click on one of the failed lines ?

Are the hyphens consistently in the same position in the source file customer number? 

What's the purpose of the "^" in the formula SUB(Temp001$,"-","^")?  If you want to remove the hyphens I believe you can substitute with "" not "^" like SUB(Temp001$,"-","").  If you want spaces where the hyphens are then SUB(Temp001$,"-"," ")

Maybe you cannot use a calculated field on customer number?

Steps

1. Create a temp field & place at the top (I've also placed between Division and Customer No)
2. For CustomerNo, use Calculated for operation and put in Temp field.

Questions 

• The hyphens are not in the same spot on each customer number.
• When I got to import, the Customer No record fails and says "The customer number is required". (see below screenshot)
• The import fails on the customer number, division works fine. 
• I'd call it a day if it was a one off, but this will be a 3 part import (AR Customer, AR Invoice and AR Cash Receipts) referencing the customer number in each source file and ran daily. 

What is failing exactly?  can you attach a screen shot of the log records and what shows if you double click on one of the failed lines ?

Are the hyphens consistently in the same position in the source file customer number? 

They didn't want to lose masking length so we just substituted another character that Sage would like, but it doesn't work. 

What's the purpose of the "^" in the formula SUB(Temp001$,"-","^")?  If you want to remove the hyphens I believe you can substitute with "" not "^" like SUB(Temp001$,"-","").  If you want spaces where the hyphens are then SUB(Temp001$,"-"," ")

Maybe you cannot use a calculated field on customer number?

Steps

1. Create a temp field & place at the top (I've also placed between Division and Customer No)
2. For CustomerNo, use Calculated for operation and put in Temp field.

Questions 

• The hyphens are not in the same spot on each customer number.
• When I got to import, the Customer No record fails and says "The customer number is required". (see below screenshot)
• The import fails on the customer number, division works fine. 
• I'd call it a day if it was a one off, but this will be a 3 part import (AR Customer, AR Invoice and AR Cash Receipts) referencing the customer number in each source file and ran daily. 

What is failing exactly?  can you attach a screen shot of the log records and what shows if you double click on one of the failed lines ?

Are the hyphens consistently in the same position in the source file customer number? 

They didn't want to lose masking length so we just substituted another character that Sage would like, but it doesn't work. 

What's the purpose of the "^" in the formula SUB(Temp001$,"-","^")?  If you want to remove the hyphens I believe you can substitute with "" not "^" like SUB(Temp001$,"-","").  If you want spaces where the hyphens are then SUB(Temp001$,"-"," ")

Maybe you cannot use a calculated field on customer number?

Steps

1. Create a temp field & place at the top (I've also placed between Division and Customer No)
2. For CustomerNo, use Calculated for operation and put in Temp field.

Questions 

• The hyphens are not in the same spot on each customer number.
• When I got to import, the Customer No record fails and says "The customer number is required". (see below screenshot)
• The import fails on the customer number, division works fine. 
• I'd call it a day if it was a one off, but this will be a 3 part import (AR Customer, AR Invoice and AR Cash Receipts) referencing the customer number in each source file and ran daily. 

What is failing exactly?  can you attach a screen shot of the log records and what shows if you double click on one of the failed lines ?

Are the hyphens consistently in the same position in the source file customer number? 

Make sure on your TEMP field you set the "Assign Temp On" to "Key Assigns Only".

They didn't want to lose masking length so we just substituted another character that Sage would like, but it doesn't work. 

What's the purpose of the "^" in the formula SUB(Temp001$,"-","^")?  If you want to remove the hyphens I believe you can substitute with "" not "^" like SUB(Temp001$,"-","").  If you want spaces where the hyphens are then SUB(Temp001$,"-"," ")

Maybe you cannot use a calculated field on customer number?

Steps

1. Create a temp field & place at the top (I've also placed between Division and Customer No)
2. For CustomerNo, use Calculated for operation and put in Temp field.

Questions 

• The hyphens are not in the same spot on each customer number.
• When I got to import, the Customer No record fails and says "The customer number is required". (see below screenshot)
• The import fails on the customer number, division works fine. 
• I'd call it a day if it was a one off, but this will be a 3 part import (AR Customer, AR Invoice and AR Cash Receipts) referencing the customer number in each source file and ran daily. 

What is failing exactly?  can you attach a screen shot of the log records and what shows if you double click on one of the failed lines ?

Are the hyphens consistently in the same position in the source file customer number? 

Hi Doug, 

Thank you for the import! I'm working on premium 2023 and for some reason my assign on temp will not stick when I selected Key Records. I'm thinking that might have been the issue? I had to create a new one, copy yours and it worked. Thank you! 

Make sure on your TEMP field you set the "Assign Temp On" to "Key Assigns Only".

They didn't want to lose masking length so we just substituted another character that Sage would like, but it doesn't work. 

What's the purpose of the "^" in the formula SUB(Temp001$,"-","^")?  If you want to remove the hyphens I believe you can substitute with "" not "^" like SUB(Temp001$,"-","").  If you want spaces where the hyphens are then SUB(Temp001$,"-"," ")

Maybe you cannot use a calculated field on customer number?

Steps

1. Create a temp field & place at the top (I've also placed between Division and Customer No)
2. For CustomerNo, use Calculated for operation and put in Temp field.

Questions 

• The hyphens are not in the same spot on each customer number.
• When I got to import, the Customer No record fails and says "The customer number is required". (see below screenshot)
• The import fails on the customer number, division works fine. 
• I'd call it a day if it was a one off, but this will be a 3 part import (AR Customer, AR Invoice and AR Cash Receipts) referencing the customer number in each source file and ran daily. 

What is failing exactly?  can you attach a screen shot of the log records and what shows if you double click on one of the failed lines ?

Are the hyphens consistently in the same position in the source file customer number? 

That's great.  Glad it's working as you intended it to.

Hi Doug, 

Thank you for the import! I'm working on premium 2023 and for some reason my assign on temp will not stick when I selected Key Records. I'm thinking that might have been the issue? I had to create a new one, copy yours and it worked. Thank you! 

Make sure on your TEMP field you set the "Assign Temp On" to "Key Assigns Only".

They didn't want to lose masking length so we just substituted another character that Sage would like, but it doesn't work. 

What's the purpose of the "^" in the formula SUB(Temp001$,"-","^")?  If you want to remove the hyphens I believe you can substitute with "" not "^" like SUB(Temp001$,"-","").  If you want spaces where the hyphens are then SUB(Temp001$,"-"," ")

Maybe you cannot use a calculated field on customer number?

Steps

1. Create a temp field & place at the top (I've also placed between Division and Customer No)
2. For CustomerNo, use Calculated for operation and put in Temp field.

Questions 

• The hyphens are not in the same spot on each customer number.
• When I got to import, the Customer No record fails and says "The customer number is required". (see below screenshot)
• The import fails on the customer number, division works fine. 
• I'd call it a day if it was a one off, but this will be a 3 part import (AR Customer, AR Invoice and AR Cash Receipts) referencing the customer number in each source file and ran daily. 

What is failing exactly?  can you attach a screen shot of the log records and what shows if you double click on one of the failed lines ?

Are the hyphens consistently in the same position in the source file customer number?