Sage Line 500 ERP – CJSmith dot me

As the email field can sometimes not be used for the correct purpose I use a bit of code to look for an @ symbol and then a dot. If more than one email address is in I replace the comma with -t which is required by dbmailsend.
If using Sage Line 500/1000 the CustomerTable will be scheme.slcustm
I expect the DBForms def below to pickup the customer code already so there is only a lookup to the customers email address.

In [INPUT]
 
VARIABLE=EMAIL_ADDRESS
VARIABLE=ISQL_CMD , , "<PATHTOOSQL.exe>"
 
In [EXTRACT]
LET EMAIL_ADDRESS= EXECUTE(((ISQL_CMD + " -h-1 -S <SQLSERVER> -U <USER> -P <PASSWORD> -d <DATABASE> -Q\"set nocount on;select email from <CustomerTable> where customer='") + <CUSTOMERVARIABLE>) + "'")
DEBUG ("Email >" + EMAIL_ADDRESS) + "<"
 
 
In [DELIVERY]
LET EMAIL_ADDRESS= TRIM(EMAIL_RESULT)
                NOTE Check for valid email address; is there an @?
                IF  INSTR(EMAIL_ADDRESS,"@",1) = 0 THEN
                {
                        LET EMAIL_ADDRESS=""
                        DEBUG "Email address invalid"
                }
                NOTE Check for valid email address; are there spaces?
                NOTE Check for valid email address; is there at least one full stop?
                IF  INSTR(EMAIL_ADDRESS,".",1) = 0 THEN
                {
                        LET EMAIL_ADDRESS=""
                        DEBUG "Email address invalid2"
                }
                DEBUG ("Email B >" + EMAIL_ADDRESS) + "<"
                NOTE Format EMAIL_ADDRESS Variable if more than one email address entered.
                IF  INSTR(EMAIL_ADDRESS,",",1) <> 0 THEN
                {
                        LET EMAIL_ADDRESS= REPLACE_STRING(EMAIL_ADDRESS,",","\" -t \"")
                        DEBUG ("Email C >" + EMAIL_ADDRESS) + "<"
                }
 
 
 
In [OUTPUT]
 
OUTPUT=<EMAILOUTPUTNAME>
INVOCATION="<pathtodbmailsend>"
PARAMETERS=
{
        "-f"
        QUOTED <EmailAddress>
        "-r"
        QUOTED <EmailAddress>
        "-c"
        QUOTED <EmailAddress>
        "-s"
        QUOTED <EMAILSUBJECT>
        "-a"
        <ATTACHMENT>
        "-t"
        QUOTED EMAIL_ADDRESS
        OUTPUT_FILE
}

In [INPUT]

VARIABLE=EMAIL_ADDRESS

VARIABLE=ISQL_CMD , , "<PATHTOOSQL.exe>"

In [EXTRACT]

LET EMAIL_ADDRESS= EXECUTE(((ISQL_CMD + " -h-1 -S <SQLSERVER> -U <USER> -P <PASSWORD> -d <DATABASE> -Q\"set nocount on;select email from <CustomerTable> where customer='") + <CUSTOMERVARIABLE>) + "'")

DEBUG ("Email >" + EMAIL_ADDRESS) + "<"

In [DELIVERY]

LET EMAIL_ADDRESS= TRIM(EMAIL_RESULT)

NOTE Check for valid email address; is there an @?

IF INSTR(EMAIL_ADDRESS,"@",1) = 0 THEN

{

LET EMAIL_ADDRESS=""

DEBUG "Email address invalid"

}

NOTE Check for valid email address; are there spaces?

NOTE Check for valid email address; is there at least one full stop?

IF INSTR(EMAIL_ADDRESS,".",1) = 0 THEN

{

LET EMAIL_ADDRESS=""

DEBUG "Email address invalid2"

}

DEBUG ("Email B >" + EMAIL_ADDRESS) + "<"

NOTE Format EMAIL_ADDRESS Variable if more than one email address entered.

IF INSTR(EMAIL_ADDRESS,",",1) <> 0 THEN

{

LET EMAIL_ADDRESS= REPLACE_STRING(EMAIL_ADDRESS,",","\" -t \"")

DEBUG ("Email C >" + EMAIL_ADDRESS) + "<"

}

In [OUTPUT]

OUTPUT=<EMAILOUTPUTNAME>

INVOCATION="<pathtodbmailsend>"

PARAMETERS=

{

"-f"

QUOTED <EmailAddress>

"-r"

QUOTED <EmailAddress>

"-c"

QUOTED <EmailAddress>

"-s"

QUOTED <EMAILSUBJECT>

"-a"

"-t"

QUOTED EMAIL_ADDRESS

OUTPUT_FILE

}

On ocassion I need to look at what is in the EDI IN modules intermediary tables, scheme.eiordtm has an annoying key_code which it combines multiple objects. It also doesn’t always have the same amount of characters. As the key_code on scheme.eiordtm starts with the key_code from scheme.eiorhdm all I do is find out how many characters are in key_code from scheme.eiorhdm and use the SQL LEFT function on the join.
Additional columns can be added but this is just the defaults I use.

SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity
 FROM "scheme"."eiorhdm" t1
  INNER JOIN "scheme"."eiordtm" t2
   ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))

SELECT customer,received_date,customer_order,"filename",warehouse,product,long_description,required_quantity

FROM "scheme"."eiorhdm" t1

INNER JOIN "scheme"."eiordtm" t2

ON t1.key_code = LEFT(t2.key_code,LEN(RTRIM(t1.key_code)))

Category: Sage Line 500 ERP

DBForms: Check and format email addresses from Sage Line 500/1000

Sage Line 500 EDI IN Intermediary tables inner join on sql query