Table level script - Orders. Cannot get it right.

Table level script not working for me.

I have a table level script on the orders table that is intended to assign a "Dispatcher" to the order based on the Order's "Region".  This assignment is intended to automatically populate once the order stage (orde_stage) is "Approved".  Regions are associated with the Price List.  Dispatchers are assigned by Region.  If you get the value of the Price List field, the "name" of the Price List will be equal to the name of the Region.  Therefore, the system can match the Price List name to the Region name and then get the Dispatcher associated with the Region.

Based on this, I came up with the following table level script.  However, it does not appear to be working and I do not know why.  I have a feeling I am doing something stupid and I just cannot see it or I just don't understand how to apply table level scripts.

I walked through this theory in SQL and I came up with the correct value for the dispatcher.  Therefore, what am I doing wrong in this table level script??  I don't get any errors and I don't see anything in the logs.

Any assistance is greatly appreciated.

function UpdateRecord()

//Get values from updated record
var priceListID = Values("orde_PricingListID");
var orderStage = Values("orde_stage");

//if state is Approved, assign dispatcher based on Region
if (orderStage == 'Approved'){

//Find Price List record and name
var plrecord = CRM.FindRecord("PricingList","prli_PricingListID="+priceListID);
var plName = plrecord.prli_name;

//Find Region based on priceList name
var myRegion = CRM.FindRecord("Regions", "regi_name="+plName);

//Assign Dispatcher associated with Region
Values("orde_dispatcher") = myRegion.regi_dispatcherid;


  • So what bit is not working?

    Does the fetch of the PricingList work? Do you get a value in the variable 'plName'?

    Does the fetch of the Regions work? Do you get a record returned for 'myRegion'?

    And does regi_ispatcherid contain a value that can be assigned to order_dispatcher?  Have you hard coded a value in here?

  • Mr. sagecrm :-)

    I got this sorted out.  I had tested in SQL and everything "appeared" to be working.  Based on your response, I assumed that it looked good on the surface.  Therefore, I went to the OrderSummary screen and copied what I had to the create script area and then displayed the variables on the screen.  Everything looked good until I got to these lines.

    var myRegion = CRM.FindRecord("Regions", "regi_name="+plName);

    Values("orde_dispatcher") = myRegion.regi_dispatcherid;

    myRegion.regi_dispatcherid was coming back undefined.

    Therefore, I popped back over to SQL to test with the following query as plName was equal to Georgia.

    Select * from Regions
    where regi_Name = 'Georgia'

    As soon as I typed 'Georgia', I knew what I had done wrong!  My variable needed ' ' around it.

    Happy to have found it...but it was a bang my head on the desk moment.  95+% of the time, I am using an integer and therefore don't need the single quotes.

    I updated that line to this...

    var myRegion = CRM.FindRecord("Regions", "regi_Name='"+plName+"'");

    The table level script is now working.  :-)

    Thank you for the reply!  I appreciate it as it let me know that my theory and layout were at least on the right track.

    Hoping this might help someone else! DOH!

  • DOH!  Well done on sorting the issue!

Reply Children
No Data