Orders - Table level script

Hoping for some assistance to see what I might be doing wrong with my table level script below.

I have been looking at this for a few hours and I cannot see what I am doing wrong. Perhaps I am not handling the Values() piece correct? I have used Values() in the past and I think I am doing it correctly. However, this script is not working at all and I cannot see why not. I am guessing that I am doing something really stupid and I just cannot see it. I am hoping another set of eyes might see the error of my way? :-)

I tested everything in SQL to make sure the "Find Record" was pulling the correct data.

This is on the Orders table and this is Sage CRM 2017 R3.

Any assistance would be greatly appreciated. Thank you!

function UpdateRecord()

{
//Get the primary key of the updated record
var orderRecord = CRM.FindRecord("Orders",WhereClause);
var orderID = orderRecord.Orde_OrderQuoteID;
var FRcompanyID = orderRecord.orde_freightvendor;

//Find Company record --------------------------------------------------------------------
var compRecord = CRM.FindRecord("Company","comp_companyid="+FRcompanyID);
var compPerson = compRecord.Comp_PrimaryPersonId;
var compAddress = compRecord.Comp_PrimaryAddressId;
var compVendor = compRecord.comp_vendorid;
//Find Person record-----------------------------------------------------------------------
var persRecord = CRM.FindRecord("Person","pers_personid="+compPerson);
var persfname = persRecord.pers_firstname;
var perslname = persRecord.pers_lastname;
var persFullName = persfname + " " + perslname;
//Find Address Record----------------------------------------------------------------------
var addrRecord = CRM.FindRecord("Address","Addr_AddressId="+compAddress);
var addrAddress1 = addrRecord.Addr_Address1;
var addrAddress2 = addrRecord.Addr_Address2;
var addrCity = addrRecord.Addr_City;
var addrState = addrRecord.Addr_State;
var addrPostCode = addrRecord.Addr_PostCode;
//Find Email record------------------------------------------------------------------------
var emailRecord = CRM.FindRecord("Email, vAllEmailPerson","ELink_RecordID="+compPerson);
var persEmail = emailRecord.Emai_EmailAddress;

// Handle update record actions here
//Values to Order Record for Freight Supplier
Values("orde_freightcontact")= persFullName;
Values("orde_freightaddress1")= addrAddress1;
Values("orde_freightaddress2")= addrAddress2;
Values("orde_freightcity")= addrCity;
Values("orde_freightstate")= addrState;
Values("orde_freightpostcode")= addrPostCode;
Values("orde_freightvendorcreid")= addrPostCode;
Values("orde_freightcompemail")= persEmail;

}

  • Hi, sometimes yes but you can also use it to get Key values such as Values("Key71"). The values collection generally only hold values they are in the query string or are on the page. Due to this the values collection can be very temperamental depending on the context you are in.

    As an example, if the tablescript is being triggered by an API call the Values collection will only hold data being posted via the API as there is no 'form' of data to retrieve the data from. In this instance the WhereClause should be used as that will hold the query that will allow you to read the record being updated by the API. 

  • Thank you for the explanations!  I greatly appreciate it.

  • hi Michelle - glad you got it working.  I see Danny has explained that the Update() function fires before data is saved in a TLS.  With an Entity Level Script it's different, however - the Update() function is fired after the database is updated, so getting data from a retrieved record provides the new values in that case.