Working with MultiValue (MV) fields in VB

Reading MV data

You read data from an MV field using the GetMVData(index as integer) function. The implied "array" of values is 1-based, that is that GetMVData(1) retrieves the first value. The MV field data is not an array as in VB terms but rather a list of values, stored/read in the order they were created; thus (1) is the first/oldest and (n) the last/most recent. Depending how the data was entered string values may be either single words or phrases (i.e. include spaces).

New values are added - and listed back in a read action - in the order they are added. Note, this is true in both v5 and v6 despite the change in the v6 client UI where items are listed alphabetically. Say you have an MV field and add the values "Ant" "Bee" and "Cat" in that order. Reading the field will pass back value(1) = "Ant", value(2) = "Bee" and value(3) = "Cat".

Now, if you delete "Bee", refresh the field and read it again the values returned will be value(1) = "Ant", value(2) = "Cat" and value(3) = "Bee". "Bee" was the last value added and becomes (3) and "Cat" moves 'left' to become (2).

If you don't know the number of values in the field, you will need to read the MVDataCount property - which returns a Long - to get the total. In searching for values to read, match or alter you will need to use a loop:

' read MV value count and make and integer for loop use
intCount = CInt(PortGal.AllRecords(1).Field("CustomMV").MVDataCount)
For i = 1 to intCount
' Loop through and display
MsgBox "Value #" & i & " = " & PortGal.AllRecords(1).Field("CustomMV").GetMVData(i)
Next

Note that the this loop is likely to be nested inside a loop through an AllRecords() or SelectedRecords() object so set loop counter names accordingly or split out the processes into different routines.

Writing MV Data

Note that you only write data and do not overwrite as such. Overwriting data will be covered separately below as the process is rather counter-intuitive. New values are simply added to the end of the 'list' of values. This is pertinent when scripting alterations to URL field data (also see below). When writing data, you simply set the field's value and the new value is added to the 'list' of field values:

PortGal.AllRecords(1).Field("CustomMV").Value = "New value"

In the above example if there were previously 4 values, with value #4 being "Last value" then calling GetMVData(4) would now return "Last value" and GetMVData(5) would now return "New value".

Deleting MV Data

Calling the DeleteData() function will remove all a field's existing values regardless of whether it is MV or not.

To access and delete a particular value in an MV field, you use the DeleteMVData(value as string). So to delete the value "Last value" set in the example above:

PortGal.AllRecords(1).Field("CustomMV").DeleteMVData("Last value")

Note that if you called GetMVData(4) it would now return "New value", as the existing data in the MV field is parsed on a left-to-right (old-to-new) basis.

Overwriting MV Data

What happens if you wish to replace the word "big" with the word "small" in an MV field. As you can't simply overwrite one value with another, it is a two pass process. First you find and delete the unwanted value and then you add the replacement value.

You know from the section above on reading data how to find the word - loop though the values:

strFind = "big"
strSet = "small"
intCount = CInt(PortGal.AllRecords(1).Field("CustomMV").MVDataCount)
For i = 1 to intCount
   strValue = PortGal.AllRecords(1).Field("CustomMV").GetMVData(i)
   If strValue = strFind Then
      PortGal.AllRecords(1).Field("CustomMV").DeleteMVData(strValue)
   End If
Next

.. but, wait - we've still not written the strSet value! Why? Although the exact reason is not clear, int appears you must refresh (or rather re-read) the underlying MV field data before you can write back to it. One method is to do a second loop:

strFind = "big"
strSet = "small"
recCount = PortGal.AllRecords().Count
For i = 1 to recCount
   intCount = CInt(PortGal.AllRecords(i).Field("CustomMV").MVDataCount)
   For j = 1 to intCount
      strValue = PortGal.AllRecords(i).Field("CustomMV").GetMVData(j)
      If strValue = strFind Then
         PortGal.AllRecords(i).Field("CustomMV").DeleteMVData(strValue)
      End If
   Next
i = 1
Next
For i = 1 to recCount
   PortGal.AllRecords(i).Field("CustomMV").Value = strSet
Next

Where you are only working on a single record you can't force a refresh of the record by shifting record focus in the recordset in use. You can't force a refresh by calling the main app using AppActivate & SendKeys (v4, v5) or the v6 Gallery.RefreshView method. Therefore, unless the Catalogue only has 1 record, read a record from the AllRecords() object - checking that it isn't the one you're trying to work on as demonstrated in the URL field worked example below.

Changing URL field values.

Essentially, a URL field is an MV string field where only the first 2 values are read or used. However, it will still accept more than 2 values. So, if you want to amend one or both URL field values you must delete both values, refresh the record and then add the values back in the correct order - first the text label and then the URL value.

If you just add 2 new values to an existing field with values, the new one become values (3) and (4) and are thus not seen/used. Also if you want to just change the label, you can't just delete value (1) and add a new label as the URL value become (1) and the new label (2) which will not work correctly with the URL field. Consider the example below which uses a custom URL field "url"

In General declarations section:

Dim PortObj As Portfolio_V5.Document
Dim PortGal As Portfolio_V5.Gallery

Add a Command Button 'cmdURL':

Private Sub cmdURL_Click()
' Assumes, catalogue has > 1 record and a custom URL field. to see the effect fully,
' the custom field should be populated with data, "Old Label" and "www.old.com"
' before using this routine.
Dim lngRecs As Long
Dim i As Integer
Dim strDummy As String
 
Set PortGal = PortObj.Gallery(PortObj.GetGalleryIndexFromName(PortObj.GetActive))
lngRecs = PortGal.AllRecords().Count
 
For i = 1 To lngRecs
   If lngRecs > 1 Then
      PortGal.AllRecords(i).Field("url").DeleteData
      ' Now read another record so the above field data
      ' is refreshed before adding the new values
      If i = 1 Then
         ' The value doesn't matter we just need to shift 'focus'
         strDummy = PortGal.AllRecords(lngRecs).Field("url").GetMVData(1)
         ' clear the string just in case
         strDummy = ""
      Else
         strDummy = PortGal.AllRecords(1).Field("url").GetMVData(1)
         strDummy = ""
      End If
      ' Add new data in the order to used, e.g. label then URL
      PortGal.AllRecords(i).Field("url").Value = "New label"
      PortGal.AllRecords(i).Field("url").Value = "www.url.com"
   Else
      ' If there's only 1 record in the
      MsgBox "You cannot use this update method with only a single record in the catalogue."
   End If
Next
 
' Switch to Portfolio and refresh screen view
AppActivate "Portfolio"
' Now Portfolio has focus send Ctrl+U
SendKeys "^+(U)", True
' Return focus (to script called PortTest)
AppActivate "PortTest"
' Tell user the script is finished. Actually you'd need to amend here
' to allow for single record 'error' situation above
MsgBox "URL Field(s) Updated"
 
End Sub

Note - you can't use the AppActivate refresh view 'trick' in the above routine to 'refresh' the MV record date after the delete. I've tried that but it fails to work as you might expect. There seems no mechanism to force a re-read of field data after the delete. The only way I've found to reliably do this is to shift focus to another record and back again. If you don't do the record focus move action then the new data is not written. Actually, though not tested here, in such a case it may get written as MV value(3) and value(4) which is still of no use to you as a URL field only ever displays value(1) and value(2).

Even if you are working with a SelectedRecords() object and not an AllRecords() one you can still interrogate the AllRecords() object to force the record focus change. The only problem to allow for is the rather unlikely event that you're scripting a change in a catalogue with only 1 record in it. Any field exists in all records so that's no problem for referencing nor is the possibility of reading an empty string value.

Question: Working with MultiValue (MV) fields in VB [FAQ00238.htm]
Last Update:- 01 June 2006

This FAQ is created and maintained using

Friday - The Automatic FAQ Maker