Lookup CustomerID via external reference number

Hello ​@SimKra ,

You could try mapping a substitution list to change the external reference number values to the corresponding Customer ID’s

Hello ​@SimKra 

Here’s a quick example.

Create a substitution list that includes under Original Value column the external reference numbers and under Substitution value the corresponding Customer ID’s.

 

And map the substitution list for Customer line. In this example, [Customer Ref] corresponds to the Data Provider field that includes the External Reference number values and CUSTOMERS is the name of the substitution list:

 

Hope this helps.

Hello ​@riglesias ,

thank you for the quick response.

Unfortunately a substitution list won’t work here, as our partner needs to import new data every few week or so and thus the ExternalReferenceNumber and the CustomerID in Acumatica need to be variables. I can’t create a substitution list right now for the next imports.

Is there a possibility, to create a formula for a lookup in the ‘source/field value’-column, to lookup the customerID with the ExternalRefNbr-field?

Just for clarification, this is the process:

1. our partner gets a csv with customer and customer-order-data from the third party.
2. our partner imports the customer-data from this csv (external reference nbr included)
3. our partner imports the customer-order-data from the csv
4. our partner gets a new csv the next week → repeat every week
   (Sorry, I wasn’t clear with the last step in my first message) 

I am struggeling with the matching of the customer and customer order via the external reference nbr in step 3.

Hi ​@SimKra were you able to find a solution? Thank you!