Using UpdateRecord in an entity-level script

This example uses the UpdateRecord function in an entity-level script. The script is triggered when Company is updated. When company type changes from Prospect to Customer, a record is created in an external table called Invoices on a third-party database called External.

The External database and the Invoices table are not supplied with the sample data in Sage CRM.

1. Create a new script. For instructions, see Creating a script.
   When creating your script, do the following:
   • From Script Type, select Entity Level.
   • In Table level script, within the function UpdateRecord section, enter the following script:
   Copy

   function UpdateRecord() 
   {
       var sType = Comp_Type; 
       var sOldType = _HIDDENComp_Type; 
       if ((sType != null) && (sType.toLowerCase() == 'customer') && (sOldType != null) && (sOldType.toLowerCase() == 'prospect')) 
       { 
       // company type changed from Prospect to Customer 
       // so create record in Invoices table
       // must work out what the next id is for the invoice table 
       sql = 'DECLARE @returnkey integer '+ 'SELECT @returnkey = (select count(*) from Invoices)';
       sql+= 'SELECT @returnkey as retkey'; 
       q = CRM.CreateQueryObj(sql,'external'); 
       q.SelectSql(); 
       NextInvoice = Number(q.FieldValue('retkey')) + 1; 
       NewInvoice = CRM.CreateRecord('Invoices'); 
       NewInvoice.InvoiceId = NextInvoice; 
       NewInvoice.customerid = CRM.GetContextInfo('Company', 'Comp_CompanyId');
       NewInvoice.description='New Customer Invoice';
       now = new Date();
       NewInvoice.InvoiceDate = (now.getMonth()+1) + '/'+ now.getDate() + '/'+ now.getYear();
       NewInvoice.VAT = Number('120.00'); 
       NewInvoice.Amount = Number('800.00'); 
       NewInvoice.Currency = 'EUR'; 
       NewInvoice.SaveChanges();
       }
   }

2. Click Save.