Infuriating! @DBColumn and list subtraction

[fishkake]

Pascal, or anybody else who's listening...!

This is really irritating me. What I have is a list of countries in a profile document, and I want users to be able to create regions made up of countries. But I want this list to only include entries which are not yet in a region.

My methodology, so to speak, is to get hold of a list of all the countries, and then get a list of the taken countries (I have both of these lists already) and then subtract the latter from the former. Any ideas?!

 

[pmonett]

One sure-fire way to deal with this issue is in Script. You can load both lists, and do an element-per-element comparison, keeping only the leftovers. Then write the result into a hidden field that you use for keyword list population.
Another solution would be to change your profile doc to a view with doc entries. You could mark docs which are part of a region, and that way a hidden view would list only those countries that were not yet marked.

Using the subtract operator in Formula would be nice, but it does not seem to work. I'll be looking into that.

Pascal

 

[fishkake]

Hello again...

I'm having a bit of trouble with the above - in Script, I can either create an array (where I need to know the number of elements beforehand, which I don't) or a list, which involves tags and such. All I want is a one dimensional, non-ordered String collection, where I can check the elements in an inefficient but simple way!!

I feel like I'm in over my head here...

 

[fishkake]

OK, another thought - could I write a function that looks at each document and determines the total list of taken countries?

Pseudo-code:

for all documents using form "Region"
add all entries in "Countries" field to a list
return this list

Also, I was thinking about building a list of all countries such as:

"Algeria" Taken
"Albania" Not taken
"Argentina" Taken
etc.

Is there a way to build a list from the contents of a field?

 

[fishkake]

OK, this is very inefficient, but I finally got it sorted, here's how:

Sub GenerateAvailableCountriesList
Dim ws As New NotesUIWorkspace
Dim db As NotesDatabase
Dim profdoc As NotesDocument
Dim ndc As Variant
Dim searchformula As String
Dim uidoc As NotesUIDocument
Set uidoc = ws.CurrentDocument
Set db = ws.CurrentDatabase.Database
Set profdoc = db.GetProfileDocument("ProfileForm")
Dim doc As NotesDocument

Dim countrylist List As String
Forall country In profdoc.Countries
countrylist(country) = "Available"
End Forall
searchFormula = {Form = "Region"}
Set ndc = db.search(searchFormula,Nothing,0)
Set doc = ndc.getFirstDocument
Do While Not(doc Is Nothing)
Forall country In doc.countries
countrylist(country) = "Taken"
End Forall
Set doc = ndc.getNextDocument(doc)
Loop

Forall x In countrylist
If x = "Available" Then
Call uidoc.FieldAppendText("AvailCountries", "; " & Listtag(x))
End If
End Forall
Print "Country List Generated!"
End Sub

It takes about a second to run (thank God there's not more data to deal with!) but it works, so it'll do!

 

[pmonett]

Job well done.
Now, in your place, I would modify the application so that the profile document maintains the updated list at all times. Which basically means that when docs are saved, the countries listed in them are flagged as taken in the profile document.
When you have that, you'll no longer need to recreate the list for each document load.

Pascal.

 

[fishkake]

This isn't terribly urgent, as I have something that works, which will do, but is it possible to store a List object such as the one I created in a document? A field cannot contain something like this, surely?

 

[pmonett]

What is a list but a multi-value field ?
Change your code to create a NotesItem in the document, and use AppendToTextList to add values and you'll be done.

Pascal.