Orders - Table level script

SOLVED

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;

}

  • 0

    One thing to note is that in certain instances, for examples when updating records via the REST api, the vales collection is not available and may result in an error.

  • 0

    I guess that what you want is for the freight person name and address to change when the freight company is changed in the order. The problem is you are getting the FRcompanyID from the saved Orders record, not using the value just submitted by the user. To use the submitted value, get it from the Values() collection, You can ditch the first few lines altogether:

    function UpdateRecord()

    {

    //Get the primary key of the updated record

    var FRcompanyID = Values("orde_freightvendor");

    //Find Company record --------------------------------------------------------------------

    if (FRcompanyID){

    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;

    }

    }

    There's no value in copying all the field values to local variables, then copying the local variables to Values() - just copy the field values to Values() directly it'll be easier to read.

  • 0 in reply to Paul C

    Paul -- Thank you for this.  Sorry for the delayed response.  Some how I missed seeing this. 

    It looks like I need to do some more research on Table Level Scripts and the Values collection as I don't fully understand this statement... The problem is you are getting the FRcompanyID from the saved Orders record, not using the value just submitted by the user. 

    I thought I was getting the value from the record that had just been updated using the WhereClause.

    I know I have used the WhereClause a lot in the TLS's I attempt to create...that must be why I want to throw them against the wall.

    I have updated this TLS to use Values() and it seems to work perfectly.

    I am having an issue with another TLS...therefore, I will see if I am doing the same type of thing there.

    Again...Thank you!

  • 0 in reply to Michele Gaw
    SUGGESTED

    The WhereClause gets the ID of the record and allows you to query the record into an object. However, the record returned is the record pre-updated record not the updated record. The Update function then allows you to amend the values prior to the database record being updated. The values collection holds the values that will be updated and therefore allows you to amend them prior to the save.

    The FormValues collection can also be used to read values from both the form and the querystring.

  • 0 in reply to Daniel Lewis

    Thanks Daniel!  I think I understand what you are saying.  I can use the Where Clause to get the ID of the record being updated.  However, don't use it to get any values that you want to use or "look at" for your update scripts.  You should use the Values or the FormValues collection for that.

    Can I use the Values collection to get the ID of the record being updated?

    var orderID = Values("Orde_OrderQuoteID");

    Sorry, I am having a brain block.  I have been using TLSs for a few years so I don't know why I am having issues with this now.  I must have hit my head on something!

    Thanks!

  • +1 in reply to Michele Gaw
    verified answer

    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. 

  • 0 in reply to Daniel Lewis

    Thank you for the explanations!  I greatly appreciate it.

  • 0 in reply to Michele Gaw

    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.