Forms Expert - API wrapper: AUSEditFieldinCat

If you have worked with scripting and using the Commence API, you will already be familiar with the steps to edit data in a Commence category. You may have also discovered that sometimes it would be nice to have a quick way to edit the value in one field with a single function call. The AUSEditFieldinCat() function is a wrapper function around the Commence API, providing a quick way to edit a single field value.

Commence API

The Commence API (Application Programming Interface) provides tremendous power to the application programmer, by providing a way to programmatically access and edit data in a Commence database. For more information about using the Commence API, refer the online documentation, and the tutorial at www.commence.com/support/files/dboa.exe.

Briefly, the steps you need to follow to edit a field using the Commence API are as follows:

• create the CommenceDB object to access the database
• create the CommenceCursor object to select the category
• create the EditRowSet object and edit the field

Note: this article is not intended to be a tutorial on using the API; if these terms are not familiar to you, you probably need to learn about using the API using the resources listed above.

AUSEditFieldinCat()

This AUSEditFieldinCat() wrapper function is a convenience tool that achieves the same result (to edit one field), in one single step. This is helpful when you only need to edit one field in one item (and the category must not allow duplicates). The listing of AUSEditFieldinCat() is as follows:

   Function AUSEditFieldinCat(ByVal sCat, sItem, sField, sValue)
      ' sCat - target category
      ' sItem - target item name - assume duplicates Not allowed.
      ' sField - field to edit
      ' sValue - data to write to field
      ' Returns 0 on success
      ' Returns ERRFLD (-1) is a field cannot be modified
      ' Returns ERRITEM (-2) if the commit is unsuccesful (this is 
      ' most likely what will need to be trapped).
      ' Convenience function to edit data in one field. Note that 
      ' this does assume no duplicates in sCat
      ' This will be very inefficient if several fields need to be 
      ' edited in the same category. In this case, it is better to 
      ' open an editrowset on the category to edit directly.

      Dim CommenceDB, oCat, rs, x, iErr
      Set CommenceDB = Application.Database
      AUSEditFieldinCat = 0: iErr = 0
      Set oCat = CommenceDB.GetCursor(0,sCat ,0)
      x = oCat.SetFilter("[ViewFilter(1,F,,""" & AUSGetNameFld(sCat) _
         & """,equal to,""" & sItem & """,)]",0) ' filter on target
      If x <> True Then MsgBox("AUSEditFieldinCat(Error:1)") : Exit Function
      If oCat.RowCount() <> 1 Then _
         MsgBox("Error: more than 1 matching item - AUSEditFieldinCat(Error:2)") 
         'only works for no dups allowed
      Set rs = oCat.GetEditRowSet(1,0)
      On Error Resume Next
      x = rs.ModifyRow(0,rs.GetColumnIndex(sField, 0), sValue,0)
      If Err.Number <> 0 Then iErr = -1
      On Error Resume Next
      x = rs.Commit(0)
      If Err.Number <> 0 Then iErr = -2
      If iErr <> 0 Then AUSEditFieldinCat= iErr
   End Function 'AUSEditFieldinCat

The arguments to AUSEditFieldinCat are

• target category
• target item name (must be unique)
• field name
• new field value

AUSEditFieldinCat() will return 0 on success. If the return value is negative, an error has occurred, and the user should be warned.

The way AUSEditFieldinCat() works, is to firstly establish the CommenceDB object, then the CommenceCursor object, using the category name argument. SetFilter is then used to select the required item in the category. An EditRowSet object is then created, the new value set for the field, and the RowSet is then commited to write back to the database.

Example 

Assume you have a category called Contact, and you want to edit the phone number only for a particular contact, Fred Bloggs. To achieve this, you would use AUSEditFieldinCat() as follows: 

   dim result 
   result = AUSEditFieldinCat("Contact", "Fred Bloggs", _
      "Phone Number", "02 9878 4777") 
   If result <> 0 then MsgBox("Warning: edit unsuccessful")  

This will result in the phone number for Fred Bloggs being set to "02 9878 4777".   

Limitations

Limitations of AUSEditFieldinCat() are

• When there are multiple rows or multiple fields to edit, this is a highly inefficient way to edit the data. Instead, the raw calls to the API should be used.
• The category must not allow duplicates, and it must be possible to uniquely identify the required item using the value of the name field.

With careful usage, AUSEditFieldinCat() can save significant time in development of your Commence scripts.