Business Object Interface

[BigLouie]

On the sales order data enty panel it pulls the e-mail address from the default address of the customer master. I would like to create a button that the user can click that will select the email address of the contact selected in the Confirm To box and over write with that address. However I receive and OLE Error 43 Object Required: AR Customer Contact. It points to the line SO_SalesOrder_bus_EmailAddress = "AR_CustomerContact.EmailAddress" Here is the script.  Can anyone see what the problem could be with the script. It points to the line

strConnectionString="DSN=SOTAMAS90;UID=Louis|SAF;PWD=target"
Set Connection =CreateObject("ADODB.Connection")
Connection.Open strConnectionString

Set RS=CreateObject("ADODB.Recordset")
strSQL="SELECT * FROM AR_CustomerContact WHERE  AR_CustomerContact.ARDivisionNo = '"&SO_SalesOrder_bus_ARDivisionNo&"' And AR_CustomerContact.CustomerNo = '"&SO_SalesOrder_bus_CustomerNo&"' and AR_CustomerContact.ContactCode = '"&SO_SalesOrder_bus_ConfirmTo&"'"
RS.Open strSQL, connection

SO_SalesOrder_bus_EmailAddress = "AR_CustomerContact.EmailAddress"



RS.Close
connection.close

Set RS = nothing
Set connection = nothing

 

[mjpolk]

Try this.

SO_SalesOrder_bus_EmailAddress = RS("AR_CustomerContact.EmailAddress")

[BigLouie]

Thanks tried that plus another changed that I commented out. Same return same error message. See below.

MS Script Link Error Information

User...  ACCOUNT
Date...  10/23/2009
Time...  10:34:34

OLE Error Number...  3265
Description........  Item cannot be found in the collection corresponding to the requested name or ordinal.
Language...........  VBScript

Script Line........  41
Script Column......  0


   1 : '
   2 : ' Sage MAS 200 system variables
   3 : '
   4 : MAS_SCR_CMP = "CPY"
   5 : MAS_SCR_USR = "Louis"
   6 : MAS_SCR_MOD = "S/O"
   7 : MAS_SCR_DTE = "20091023"
   8 : MAS_SCR_HWD = "d:\account\best\MAS 200\Version4\MAS90\home"
   9 : MAS_SCR_LWD = "d:\account\best\MAS 200\Version4\MAS90\soa"
  10 : MAS_SCR_LIB = "d:\account\best\MAS 200\Version4\MAS90\SO\Y\SO_SALESORDER.M4L"
  11 : MAS_SCR_PNL = "PHEADER"
  12 : MAS_SCR_OBJ = "BT_LINK_1"
  13 : MAS_SCR_MSG = ""
  14 : MAS_SCR_PFM = ""
  15 : MAS_SCR_CS   = 1
  16 : MAS_SCR_DBG  = 0
  17 : MAS_SCR_ERR  = 0
  18 : '
  19 : ' form variables passed in by Customizer
  20 : '
  21 : SO_SalesOrder_bus_EmailAddress = "John.Doe@address.com"
  22 : SO_SalesOrder_bus_ConfirmTo = "John Doe"
  23 : SO_SalesOrder_bus_ARDivisionNo = "00"
  24 : SO_SalesOrder_bus_CustomerNo = "ADDRESS"
  25 : '
  26 : ' language  : VBScript
  27 : ' file name : m:\best\mas 200\version4\mas90\cm\script\contactemail.vbs
  28 : ' script begin
  29 : '
  30 : 
  31 : strConnectionString="DSN=SOTAMAS90;UID=Louis|SAF;PWD=target"
  32 : Set Connection =CreateObject("ADODB.Connection")
  33 : Connection.Open strConnectionString
  34 : 
  35 : Set RS=CreateObject("ADODB.Recordset")
  36 : strSQL="SELECT * FROM AR_CustomerContact WHERE  AR_CustomerContact.ARDivisionNo = '"&SO_SalesOrder_bus_ARDivisionNo&"' And AR_CustomerContact.CustomerNo = '"&SO_SalesOrder_bus_CustomerNo&"' and AR_CustomerContact.ContactName = '"&SO_SalesOrder_bus_ConfirmTo&"'"
  37 : RS.Open strSQL, connection
  38 : 'Contact_EmailAddress = RS.Fields.Item("EmailAdress").Value
  39 : 
  40 : 'SO_SalesOrder_ bus_EmailAddress = Contact_EmailAddress
  41 : SO_SalesOrder_bus_EmailAddress = RS("AR_CustomerContact.EmailAddress")
  42 : 
  43 : 
  44 : 
  45 : 
  46 : 
  47 : RS.Close
  48 : connection.close
  49 : 
  50 : Set RS = nothing
  51 : Set connection = nothing
  52 : '
  53 : ' script end
  54 : '

Message Edited by ebjelovs on 10-26-2009 09:14 AM

[lyoung]

BigLouie,

Try this:

strConnectionString = "DSN=SOTAMAS90;UID=Louis|SAF;PWD=target"
Set objCN = CreateObject("ADODB.Connection")
objCN.Open strConnectionString

Set objRS = CreateObject("ADODB.Recordset")
strSQLQuery = "SELECT * FROM AR_CustomerContact WHERE  ARDivisionNo = '" & SO_SalesOrder_bus_ARDivisionNo _
& "' AND CustomerNo = '" & SO_SalesOrder_bus_CustomerNo _
& "' AND ContactName = '" & SO_SalesOrder_bus_ConfirmTo & "'"
Set objRS = objCN.Execute(strSQLQuery)

SO_SalesOrder_bus_EmailAddress = objRS.Fields("EmailAddress")

objRS.Close
objCN.close

Set objRS = nothing
Set objCN = nothing

Tested it on mine and it worked so I assume it should work on yours. You may want to add in some sort of error catching for null values. Also be careful that you never have two or more contacts with the same name on one customer account or you may not get the email you want. You can't compare  ContactCode to ConfirmTo because ConfirmTo is actually the ContactName. Other than that, I made a couple syntax changes. It's always good to create variables in vbs with a data type abbreviation at the start, because it can become confusing with long scripts and late binding as to what data type a given variable is. Hope this helps.

[mjpolk]

That should have worked, so it sounds a bit like a configuration problem.  Try replacing every "createobject" with "wscript.createobject".

[mjpolk]

Hey BL,

I meant to tell you that you have your clients email address in your error message Log.  You might want to remove it so that a Bot doesn't scavange it for spam.

[BigLouie]

lyoung, thanks a lot. Your example worked perfectly. Wonder why mine would not work since it has worked in other scripts.  Now they want to have the company code to be dynamic *ugh*

[BigLouie]

Michael, saw that too late. For some reason the edit feature is not there for this thread.

[mjpolk]

I think you have like 24 hrs before the edit is unavailable.

Send Erica an email and ask her to remove it.

[tmac]

I know this is a bit of an old thread and I appologize ahead of time for digging it up, but this script is exactly what we are looking for.  Why Sage doesn't think that haveing the email address pull from the confirm to field is is beyond me...

We just rolled to 4.5 and have the ability to play with some scripts, but it is comepetly new to me.  I took a look at the script that lyoung posted and used script maintenacnce to put it in, but I am lost as to what I need to do next to get it to work...

I know I probably could get my reseller to do it for me, but I like to learn these things so I don't have to run to them every time I need somehting.  This forum has been a HUGE learning resource for me!

Thanks,

Troy