<EDIT> I've added the source of a couple of functions to make the source code more complete, and clarified Figure 1. </EDIT>

 

Fields are documented at: http://www.sqlnotes.net/drupal5/index.php?q=node/1071.

Equations are documented at: http://www.sqlnotes.net/drupal5/index.php?q=node/128. Additional information is provided at: http://www.sqlnotes.net/drupal5/index.php?q=node/2070.

 

However, I can find no definitive documentation on what the acceptable syntax for equations is. Things that you might expect to work do not do so. For example, you cannot combine strings using an ampersand character & as you can in VBScript.

As a consequence, I wasted quite a lot of time over the last couple of days until I realised that to combine strings I would need to write a VBScript function.

 

The following screenshot shows what does NOT work. Moreover, it fails silently – the only way to find out that it hasn't worked is to try it and see that it has had no impact on the field cc. It still doesn't work if you explicitly recalculate the field.

The reason, no doubt, is that this syntax is not acceptable in this context: cc = aa&bb. Field cc is highlighted at point 1 in figure 1; the equation is highlighted at point 2.

 

Figure 1: the equation which derives field cc

In the end, I have written VBScript functions:

 

Function combineSubstrings(Separator, firstString, secondString)
' This function assumes that the desired result string consists of two substrings separated by
' a separator substring.
' Mark GREGORY

     combineSubstrings = ftrim(firstString) & separator & ftrim(secondString)
End Function

Function reverseSubstrings(Separator, search_in)

' This function assumes that a string consists of two substrings separated by
' a separator substring. It reverses the two principal elements and returns
' a new string consisting of the second element then the separator substring
' then the first element.
' Mark GREGORY

Dim firstElem, secondElem
     reverseSubstrings = after(separator, search_in, "first") & separator & before(separator, search_in, "first")
End Function

 

These two functions themselves depend upon further functions:

 

Function ftrim(inString)
ftrim = LTrim(RTrim(inString))
End Function

Function includes(look_for, search_in)
'This function returns false if the string is not found at all.
' Mark GREGORY
includes = InStr(1, search_in, look_for)
End Function

Function findlast(look_for, search_in)
'This function returns the start position of the last occurrence of a looked-for string
'in a searched-in string. -1 is returned if the string is not found at all.
' Mark GREGORY
Dim strlen, lastfound, nextfound
strlen = Len(search_in)
If strlen < 1 Then
    findlast = -1
Else
    lastfound = InStr(1, search_in, look_for)
    If lastfound = 0 Then
        findlast = -1
    Else
        nextfound = InStr(lastfound + Len(look_for), search_in, look_for)
        Do While nextfound > 0
            lastfound = nextfound
            nextfound = InStr(lastfound + Len(look_for), search_in, look_for)
        Loop
        findlast = lastfound
    End If
End If

End Function

 

Function before(look_for, search_in, first_or_last)
'This function returns the part of the searched_in string before an occurrence of a looked_for string.
'By default, this is the first occurrence of the string; an optional parameter can be set to indicate that
'the last occurrence is sought - the default is to take the first occurrence.

Dim strlen, found, last_occ
If LCase(first_or_last) = "first" Then
    last_occ = False
Else
    If LCase(first_or_last) = "last" Then
        last_occ = True
    Else
        last_occ = False
    End If
End If

strlen = Len(search_in)
found = InStr(search_in, look_for)
If found <= 0 Then
    before = search_in
Else
    If last_occ Then
        before = Left(search_in, findlast(look_for, search_in) - 1)
    Else
        before = Left(search_in, found - 1)
    End If
End If
End Function

Function after(look_for, search_in, first_or_last)
'This function returns the part of the searched_in string after an occurrence of a looked_for string.
'By default, this is the first occurrence of the string; an optional parameter can be set to indicate that
'the last occurrence is sought - the default is to take the first occurrence.
' Mark GREGORY

Dim strlen, found, last_occ
If LCase(first_or_last) = "last" Then
    last_occ = True
Else
    last_occ = False
End If


strlen = Len(search_in)
found = InStr(search_in, look_for)
If found <= 0 Then
    after = ""
Else
    If last_occ Then
        after = Right(search_in, strlen - findlast(look_for, search_in) - Len(look_for) + 1)
    Else
        after = Right(search_in, strlen - found - Len(look_for) + 1)
    End If
End If
End Function

 

I make use of these functions in, for example, a tagged classification scheme which I am currently working on.

 

The field TagKind uses combineSubstrings - see point 1:

 

 

The field KindTag reverses the order so that kindVal comes before Tagvalthis field uses the function reverseSubstrings:

 

 

We can use these fields in order to implement the many to many relationship between kind and tag. To create a new tag | kind combination, we create a new item and select from the drop-down fields as follows:

 

In the next screenshot, I have already selected a tag value - CITSA 2009 - and I am about to select a kind value:

 

Figure 4: choosing a KindTag

In the next screenshot, figure 5, I show the results after I have selected that kind value and after the field equations have fired; note that I have also reordered the data by ascending KindTag. Point 1 shows the TagKind; point 2 shows the corresponding KindTag.

 

Getting these examples to work has taken about two working days. I hope that by sharing these examples, others will be able to save some of that time.

 

WHAT I AM SUGGESTING NEEDS DOING: