Customer Numbering with Sage 300 (1 reply)
B, this is prettily easily achieved.
First, if you go through the Sage300 training manual (step 4), there is a scenario exactly like this.
It first checks Sage for an existing record using a lookup.
If there is no matching record a new customer is generated.
What the above doesn't cover, is what if the customer has two orders in a single IMan batch. You can extend the formula like below.
Dim Result
Dim Key
'Perform the initial Lookup
Result = Lookup("CUSTEML", "IDCUST", Array(%Email, %Currency), False)
'When no match
If Result = "" Then
'Generate a key using a combination of the Email and Currency fields.
Key = %Email & "-" & %Currency
'Check for the existence of the value.
Result = GetRegisteredValue(Key)
'If still no result then generate a new customer number.
If Result = "" Then
Result = GetCounterSequence("CUSTNO")
'Now store the value so that any subsequent records can reuse
'the same generated id.
RegisterValue Key, Result
End If
End If
Result
I have a client who purchased Iman with the shopify and sage 300 integration. When a new order comes down they want to check to see if the customer already exists in 300. Unfortunately the customer id is 13 characters (numbers) long and Sage only holds 12 so I can’t use the customer id from shopify as the customer number in sage. So I would basically have to search the customers by name, email and/or phone number to find an existing customer. So let’s say I don’t find a match and I need to create a new customer. In sage you have to make up a customer number. There is no auto-numbering. How do other customers handle this?
We could put a prefix of let’s say ‘SH’, start with 00001 and then write a sql function to get the next number but I’m not sure how that will work in Iman. Let’s say the last customer number is SH00008 and I have 5 orders to download. I do my logic to check if the customer exists. They don’t so I get the next number based on the last number in sage which in this case is SH00009. Now the next order, the customer exists (e.x. SH00004) so I use that number for that order. Now the 3rd order, the customer doesn’t exist. If I get the next number from the database it’s still going to be SH00009 because the first one hasn’t been created yet. Does all this make sense? I’m just having a hard time coming up with the best way to do this.