Unwanted Multiple Values from the Picklist

HCL Domino Domino Forum
1

Hello everybody,

Here is the issue:

I created a form with a button. When a person clicks the button, it calls a view from another database. When a user picks a selection from this list, it populates other fields on this form. Right now my view consist from 4 columns: last four digits of SSN, Emp. Name, Depart. name and Depart. number. It works well, when last four digits of social security number doesn’t duplicate, but as soon as users chooses one document from the picklist, where last four digits of SSN duplicates, it inserts two entries into according fields.

Field properties are: TEXT, COMPUTED, alow multiple values are not checked.

One more thing: first column on my picklist has to be last four digit of SSN and it has to be searchable as soon as someone starts entering numbers and it has to be sorted.

Following is my code:

REM {Prompt for Employee Information};

dbase := “database.nsf”;

server := “ORG/ORGS”;

dbview := “Lookup view”;

FIELD txt_empName := txt_empName;

FIELD txt_empNum := txt_empNum;

FIELD txt_empDptnam := txt_empDptnam;

FIELD txt_empDptnum := txt_empDptnum;

dname := @PickList([Custom]:[Single]; server : dbase; dbview; “View by SSN”; “Select SSN:”; 1);

@If(dname = “”; @Return(“”);

@SetField(“txt_empName”; @Trim(@Left(dname; “,”) ) + “,” + @Right(dname; “,”) ) );

FIELD txt_empName := @DbLookup(“” : “NoCache”; server : dbase; dbview; dname; 2);

FIELD txt_empDptnam := @DbLookup(“” : “NoCache”; server : dbase; dbview; dname; 3);

FIELD txt_empDptnum := @DbLookup(“” : “NoCache”; server : dbase; dbview; dname; 4);

FIELD txt_empNum := @DbLookup(“” : “NoCache”; server : dbase; dbview; dname; 1);

Please help me.

Hopefully, above makes sense.

2

Subject: Unwanted Multiple Values from the Picklist

The problem would be that if that key returns multiple matches in the lookup, you’re going to get multiple records hitting the fields.

From a coding point of view, you can fix that by changing:

FIELD txt_empNum := @DbLookup(“” : “NoCache”; server : dbase; dbview; dname; 1);

to:

vals := @DbLookup(“” : “NoCache”; server : dbase; dbview; dname; 1);

FIELD txt_empNum = vals[1];

That will just give you the first value back. The problem you’ll have is that the selected document in the picklist may not be the first one returned by the lookup.

What about returning the UNID of the selected document from the picklist and then either do the DBLookups to a view sorted by unid, or use @GetDocField.

HTH

Kiers

3

Subject: RE: Unwanted Multiple Values from the Picklist

Kieran, Thank you so much for your reply.

What about returning the UNID of the selected document from the picklist and then either do the DBLookups to a view sorted by unid, or use @GetDocField.

I don’t think those two solutions will work for me since, i need the picklist to be sorted by last four digits SSN and be searchable based on SSN. Unless, there is a way to do it. If so please let me know how.

Is there a way i can define a key from two fields, for example last four digits SSN and Employee Name. If so, can you help me with a formula.

again Thank you so much for your help.

Ksenia

4

Subject: Unwanted Multiple Values from the Picklist

Let me start with a few side notes, to build up the tension. :wink:

“Initializing” fields like in

FIELD txt_empName := txt_empName;

was required prior to Notes 6, if you were using @SetField later on. It no longer is (and never was, if you set the field using the FIELD keyword).

Currently, you set your field txt_empName twice, using both, @SetField and FIELD, where the second statement overwrites the first.

Doing multiple uncached lookups to the same view to retrieve your field contents is extremely inefficient and unnecessarily hogs network bandwidth. The least thing to do would be to make the first call using the “ReCache” option, and use cached lookups from there on.

There is however a much better alternative, and it even solves the problem you came here for. :slight_smile: Get rid of the DbLookups all together and let the picklist return what you need. Append an additional hidden column to Lookup view, where you concatenate all the values you’re going to retrieve into one String. If your original field names were EmpName, DptName, DptNum and EmpNum, use a column formula similar to this

EmpName + “|” + DptName + “|” + DptNum + “|” + EmpNum

Just make sure, that the separator you use will never appear in one of those fields. Instead of the field names, you can even use the programmatic names of the columns, that are already in the view. I sort of like that approach, in the hope that it was a little faster, but honestly, I have no clue if it makes any difference.

Now modify your @Picklist to return this 5th column, instead of the 2nd. These values will always come straight from the one document selected, so there’s no danger of retrieving duplicates.

results := @PickList([Custom]:[Single]; server:dbase; dbview; “View by SSN”; “Select SSN:”; 5);

Finally, use e.g. @Word to split the values and write them into your fields.

FIELD txt_empName := @Word(results; “|”; 1);

FIELD txt_dptName := @Word(results; “|”; 2);

FIELD txt_dptNum := @Word(results; “|”; 3);

FIELD txt_empNum := @Word(results; “|”; 4);

Edit: I added the missing separator strings to make my example work.

5

Subject: RE: Unwanted Multiple Values from the Picklist

Harkpabst, It worked. It worked. It worked. YEEEEEIIII… I am so excited.

Thank you. Thank you so much. I really appreciate your time and reply.

Have a wonderful holidays!!!

6

Subject: RE: Unwanted Multiple Values from the Picklist

Ksenia,

Sadly, most of the time, people don’t even come back to report, if they could solve their problem. Let alone being excited about it.

So, thank you for telling us, I’m absolutely sure, that your holiday wishes will work for me. :slight_smile: