domino question

[bufhal2]

I hope someone can help me with a piece of Lotusscript.
The code pulls names from a database that are both active or inactive in accordance with another table. I am trying to get the dropdown on the form to just show the active sales people. I have the code and more detail in a Word doc I can send if someone offers a look.
Thank you in advance.

 

[pmonett]

You could post the essential code here and the Tek Tips community would easily help you out.

Pascal.

 

[bufhal2]

<b>In a nutshell:
Here is the code. This code needs to access a field in another table called ARPAS
Within ARPAS is a field named “SWACTV” which contains a 1 or 0 depending on whether a salesperson is active(1) or not(0). I need the dropdown to show only active sales people.(1)
On the form a dropdown shows nameemp (salesperson name)
And codeslsp (salespersons initials) with a list of both active and inactive salespeople
Also: the dropdown field "SPFULLNAME" is type: name and editable and the view is ARPAS</b>



Sub Click(Source As Button)
Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.CurrentDatabase
Dim workspace As New NotesUIWorkspace
Dim view As NotesView
Dim doc As NotesDocument
Dim j As Integer
Dim k As Integer
Set uidoc = workspace.currentdocument
Set currentdoc = uidoc.document
Dim Number As Integer
Number = 1
SPSHORTNAME="SALESPER"+Cstr(Number)
SPFULLNAME="SPFULLNAME"+Cstr(Number)
SALESPLT="SALESPLT"+Cstr(Number)

'Check if SALESPER is empty
If currentdoc.SPFULLNAME1(0) ="" Or currentdoc.SPFULLNAME1(0) = Null Then
Messagebox "Please type in a salesperson entry", 0, "STOP"
'uidoc.GotoField("SALESPER1")
Exit Sub
End If

'Lookup the SalesPerson
Set view = db.GetView( "ARPAS" )
'One variable to store SalesPerson Name and one to store SalesPerson Number
Dim allcustomers() As String 'SalesPerson Initials
Dim allcustomers2() As String 'SalesPerson FullName

'Prompt User to select a field to find by then do a search based on that field.
Dim count As Integer 'Used to sync array element with workspace prompt selection.

j = view.FTSearch( {field NAMEEMPL contains "}+uidoc.Fieldgettext(SPFULLNAME)+{"}, 0 )
' Messagebox {NAMEEMPL = "}+uidoc.Fieldgettext(SPFULLNAME)+{"}, 0, "STOP"
Set doc = view.GetFirstDocument

Messagebox "CODESLSP = "+Cstr(doc.CODESLSP(0)), 0, "STOP"
Redim allcustomers(j-1)
Redim allcustomers2(j-1)
For i = 0 To j-1
allcustomers(i) = doc.CODESLSP(0)
allcustomers2(i) = doc.NAMEEMPL(0)

Set doc = view.GetNextDocument(doc)
Next


Call view.Clear
Call uidoc.Refresh
End Sub

 

[pmonett]

Why not create a hidden view that lists all active sales people ? You could then link the keyword field to that view in design, rather than picking them out in code. You don't seem to have any additional selection criteria, so a view will remove the need to do an FT search on every query, plus you won't need to code the collection, just tell the keyword field to get its values from the view column you want.

If that is not possible, then I have a few other suggestions. Create a dummy document with a notesitem. Use the notesitem to store the names of sales people you find active. When finished sorting, use the notesitem as keyword list.

Pascal.

 

[bufhal2]

Thanks Pascal;
I created a 2nd view of the table ARPAS1. Within the table I added the column that contains the values I seek called SWACTV. That is where the "0" or "1" 's are stored for the active or inactive people. I am not touching the code I posted. The view selection code I am using is:
SELECT Form = "ARSAP1" & SWACTV = 1
It is not working. Any ideas?
Thank you.

 

[pmonett]

Is the value stored in SWACTV a string or a numerical ?
Other point to check : is the view sorted on the first column ? Lookups do not work in views that are not sorted.

Pascal.

 

[Troodos]

Try the following select instead:

SELECT ((Form = "ARSAP1") & (@Text(SWACTV) = "1"))

Then there will be solved any integer/character 'conflicts' anyway...

Another tip: Use @FORMULA language instead of LotusScript if this is inside a form (Drop down etc.)

The best 'practice' is to create a field 'Computed for display' here called SalesLookup and do something similar to this for lookup (THIS IS R6 SYNTAX!):

@DbColumn("":"ReCache";@DbName; "<ViewName>"; 1) + "|" + @DbColumn("":"ReCache";@DbName; "<ViewName>"; 2);

In the drop down field (or whatever potential multivalue type of field) then use 'Select by formula' and type in SalesLookup in the value column...

Works for me! Less maintenance, easier to find errors.

TrooDOS

 

[bufhal2]

Thank you both for your help.
I have tried with no success to get this view to show active salespeople. {Please here is a step by step)

This is the code in the DB:
'Lookup the SalesPerson
Set view = db.GetView( "ARSAP1" )

Here is the View Selection criteria:
uses 'ARSAP' form AND field SWACTV1 is equal to 1

The view shows all the data in Designer of the three columns: CODESLSP, NAMEEMPL, SWACTV1 for the ARSAP1 view

The old view(ARSAP) with the columns: CODESLSP, NAMEEMPL
shows up on the form whether the code is:
Set view = db.GetView( "ARSAP" )
or
Set view = db.GetView( "ARSAP1" )

Just got back on this and it would seem it is a simple thing to get this view to work but I cannot get it going.
Please, any input is greatly appreciated...

 

[Troodos]

Check your aliases on your 2 views so they are not equal, just to be sure that there is no similar alias names there ... Can easily happen if a view is copied from the othter view...

The purpose of the LS code is to FIND and SELECT one salesperson? If 'Yes', I still think a @Formula solution would be better than the LS, using a DialogList field for sales person where you can TYPE the name or use the 'helper button' to select from a list:

Populating values (In the second tab of the field, select 'Use formula for choices' and try to type in something like the code below):
@DbColumn("":"ReCache";@DbName; "ARSAP1"; 1) + "|" + @DbColumn("":"ReCache";@DbName; "ARSAP1"; 2)

Then the concatenated value when one (or more) is selected the value will be used to:
a) <b>display</b> the name
b) <b>store</b> the salesnumber

Try it with a separate field without doing any changes on your button code. If there is more to it than LOOKUP and SELECT then ignore this posting...

Brgds,

TrooDOS

 

[bufhal2]

Thanks TrooDOS,
I found alias to be a copy or similar word to the script language. Can you please tell me how to check if alias may be an issue? I did copy and paste to create ARSAP1 view.
I am unclear on what words may be aliases.
Thank you

 

[Troodos]

Chek in Domino Designer, open the 'View' tab (Using R6 designer as guide here, so if other version it is only small differences, if any).
In the first column it is the view NAME and COMMENT list, and the second column display ALIAS'es. The column does not always tell the whole truth though; There can be multiple ALIAS names pr view.

Open the view in designer. Open View preferences through menu Design | View properties. On the first tab and second box the ALIAS'es are stated, and are separated with | if there are multiples.

Check both views for this, and I suggest to make the aliases names unique to avoid potential lookup conflicts...


Brgds,

TrooDOS

 

[bufhal2]

I tried your suggestions in the last post and still have the old view. This is strange. The code I posted:
'Lookup the SalesPerson
Set view = db.GetView( "ARSAP1" )
still gets the ARSAP view. Could the other code I posted originally be interfering with this?

 

[Troodos]

We're running out of options here as far as I can see. Try the following 2 changes in your code:

In the lookup LS:
Set view = db.GetView("ActiveSalesPerson")

Open the ARSAP1 view in Domino Designer. Change the name to "ActiveSalesPerson" and remove any aliases. Try again...

Another question: How much differences are there between these views, physically?

Brgds,

TrooDOS

 

[bufhal2]

Did not work. As far as the physical difference between the two views, I cut and pasted to create the second view.
Even though I cannot get this working, thanks for helping..

 

[Troodos]

Re-create the view, and don't copy and paste from the other view, as in 'copy column from one view to the other'. Sometimes when doing that it has some strange effects, and many times I have wondered why views have not been functioning as expected. No wonder why there is hardly no hair left on <b>my</b> head

You can copy the <b>formulas</b> from the other view after the column is created, but avoid column copy if you can. As said, SOMETIMES strange things happens, and then is has a tendency to stay strange (at least until all columns are removed, the view saved and columns re-created).

I know this might sound strange, but I have seen it happen so many times that I avoid doing 'column copy' now. It is <b>better</b> in R6, but it happens sometimes there too...

Brgds,

TrooDOS