OP Update record

version 1.5

OP Update record (connectionID; bindID) Longint

Parameter	Type		Description
connectionID	Longint		Connection ID with target server
bindID	Longint		Bind list ID
Function result	Longint		Error code result for the function

OP Update record saves the changes made to a record that you have bound. Only the fields defined in bindID are modified.

You must first specify a current record (by using OP Goto selected record or OP Goto record ), load it with OP Load record if necessary, assign new values to the fields you want to modify, and then call OP Update record to save the changes.

After loading the record, remember to check whether or not it is locked.

The bind list that you use to test whether or not a record is locked and the bind list that you use to update the record can be different. For example, you may use a one-field bind for testing the lock status of a record, and then use a ten-field bind list for updating the fields of the record.

If the record is locked, the record is not updated and OP Update record does not return an error. You must check the lockStatus parameter of the OP Load record, OP Goto record or OP Goto selected record function.

If OP Update record executes successfully, it returns 0. Otherwise, this function returns one of the following errors:

Error Code	Description
-108	Not enough memory to perform this operation.
-9967	The record was not modified because it could not be loaded.
-9969	Invalid field type requested.
-9971	Field number is out of range.
-9972	table number is out of range.
-9998	Duplicate index key.
10128	The 4D Open for 4th Dimension package has not been initialized.
10136	The connection does not exist.
10137	The bind list does not exist.
10138	The bind list is not related to this table.
10139	The bind list is not defined.
10153	Unable to convert this type of data.
10154	This command cannot be executed right now.

This example updates the gross sales field of a customer's record to reflect the day's invoices.

   C_LONGINT ($BindInvID;$ErrCode)

   C_LONGINT ($transErr;$lock;vRecords)

   C_STRING (8;vInvDate)
   C_REAL (vInvTotal;vCustSales)
   C_STRING (30;vCustID)

   C_LONGINT (vTableInvoices;vTableCustomers;vRecords;vIsLocked;$unused1)
   C_LONGINT (vFieldAmount;vFieldInvCustID;vFieldInvDate;vFieldInvID;$unused2)
   C_LONGINT (vFieldCustID;vFieldSales)

   ARRAY STRING (30;CustIDs;0)
   ARRAY STRING (30;InvCustIDs;0)
   ARRAY REAL (addToGrossSales;0)
   ARRAY REAL (InvAmounts;0)

      ` Get [Invoices] tableID and [Invoices]Customer ID fieldID   
   $errCode:=OP Get one field number (vConnectID;"[Invoices]Customer ID";vTableInvoices;vFieldInvCustID)
      ` Get [Invoices]Invoice date fieldID   
   $errCode:=OP Get one field number (vConnectID;"[Invoices]Invoice date";$unused1;vFieldInvDate)
      ` Get [Invoices]Invoice total fieldID   
   $errCode:=OP Get one field number (vConnectID;"[Invoices]Invoice total";$unused1;vFieldAmount)

      ` Get [Customers] tableID and [Customers]Customer ID fieldID   
   $errCode:=OP Get one field number (vConnectID;"[Invoices]Customer ID";vTableCustomers;vFieldCustID)
      ` Get [Customers]gross sales fieldID
   $errCode:=OP Get one field number (vConnectID;"[Customers]Gross sales";$unused1;vFieldSales)
      ` Get [Customers]Company fieldID
   $errCode:=OP Get one field number (vConnectID;"[Customers]Company";$unused1;vFieldCustName)

      ` Create the bind list to load and modify a customer record
   $errCode := OP Create bind ($BindCustID)
   $ErrCode := OP Define bind by pointer ($BindCustID;vTableCustomers;vFieldSales;->vCustSales)
   $ErrCode := OP Define bind by pointer ($BindCustID;vTableCustomers;vFieldCustID;->vCustID)

      ` Create the bind list to load invoices amounts and customer IDs
   $errCode := OP Create bind ($BindInvID)
   $ErrCode := OP Define bind by pointer ($BindInvID;vTableInvoices;vFieldAmount;->InvAmounts)
   $ErrCode := OP Define bind by pointer ($BindInvID;vTableInvoices;vFieldInvCustID;->InvCustIDs)
   
      ` Make the day's invoices the current selection
   vInvDate:= String (Current date)
   $errCode := OP Single query (vConnectID;vTableInvoices;vFieldInvDate;"=";->vInvDate;vRecords)

      ` Load the invoices values in an array, along with the customer IDs.
   $errCode := OP Selection to array (vConnectID;$BindInvID)
      ` Make an array of UNIQUE customer's ID to be updated
   $errCode := OP Distinct values (vConnectID;vTableInvoices;vFieldInvCustID;CustIDs)
      ` Make the day's sales the same size
   ARRAY REAL (addToGrossSales;Size of array(CustIDs))
   
      ` Loop through the invoices array to cumulate invoice amounts into unique customer's gross sales entry
   For ($i;1; Size of array (InvCustIDs) )
      $uniqCustEntry := Find in array (CustIDs;InvCustIDs{$i})
      addToGrossSales{uniqCustEntry} := addToGrossSales{uniqCustEntry} + InvAmounts {$i}
   End for
   
      ` Create a selection of customers for those invoices
   $errCode:=OP Many to one join (vConnectID;vTableInvoices;vTableCustomers)

      ` Find out how many records were found, even though we already know from the size of the CustIDs array.
   $errCode := OP Records in selection (vConnectID;vTableCustomers;vRecords)

      ` Set the table to Read/write in order to lock the record
   $errCode := OP Set access mode (vConnectID;vTableCustomers;1)

      ` Browse through all customers records
   For ($i;1; vRecords)
      $errCode := OP Goto selected record (vConnectID;vTableCustomers;$i;BindCustID;$lock)
         ` Let's assume the customer record is safely loaded and locked
         ` The OP Goto selected record command was also used to load the record values

         ` Find the entry for the current customer record in the addToGrossSales array
      $uniqCustEntry := Find in array (CustIDs;vCustID)

         ` Accumulate into the existing gross sales value
      vCustSales := addToGrossSales{$uniqCustEntry}
      
         ` UPDATE the customer record
      $errCode := OP Update record (vConnectID;$BindCustID)
   End for 
   
      ` All previously loaded (and locked) record were automatically released by loading onother record
      ` The last processed record has to by unloaded 'manually'
   $errCode := OP Unload record (vConnectID;vTableCustomers)
   
   $ErrCode:=OP Delete bind ($BindInvID)
   $ErrCode:=OP Delete bind ($BindCustID)

OP Goto selected record, OP Load record, OP Set access mode, OP Unload record, SAVE RECORD.