Link to home
Start Free TrialLog in
Avatar of captain
captainFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Domino Formula: dblookup query not returning results

Hi

i am having a real dense day and as time pushes along I need some quick help on the formula I created.

I have a need to use a button to populate a field with names from a particular group in the directory. When I use the code below the choices appear but the field is not populated. Field exists and has correct properties.
So I wonder what's going awry...?

thanks capt.
REM {Data options};
      targetFieldName := "Studio";
      sourceViewName := "Groups";
      sourceGroup :="Acc-Managers";
      sourceField := "Members";
      sourceServer := "server";
      sourceDatabase := "Names.nsf";
 
      REM {Prompt options};
      promptTitle := "Studio Select";
      promptMessage := "Assign studio staff from the list:";
      promptError := "No choices are available.";
promptError1 := "Your choice returned no result.";
      promptMultiple := @False;
 
      REM {Get the choices.};
      viewLookup := @DbLookup("":"NoCache"; sourceServer : sourceDatabase; sourceViewName; sourceGroup;sourceField );
      @If(@IsError(viewLookup); @Return(@Prompt([Ok]; "Lookup Error"; promptError)); "");
      choiceList := @Name([CN];viewLookup);
      @If(choiceList = ""; @Return(@Prompt ([Ok]; "No Choices"; promptError)); "");
 
      REM {Display the prompt};
      noSelection := 1;
      selection := @Prompt(@If(promptMultiple; [OkCancelListMult]; [OkCancelList]); promptTitle; promptMessage; @GetField(targetFieldName); choiceList);
      @If(selection = noSelection; @Return(""); "");
 
      REM {Lookup value from the return column};
      selection := @DbLookup("":"NoCache"; sourceServer : sourceDatabase; sourceViewName;sourceGroup; sourceField);
      @If (@IsError(selection); @Return(@Prompt([Ok]; "Lookup Error"; promptError1)); "Y");
 
      REM {Update the target field};
      @SetField(targetFieldName; selection);
      @Command([ViewRefreshFields])

Open in new window

Avatar of madheeswar
madheeswar
Flag of Singapore image

make @If(selection = noSelection; @Return(""); "");
this as @If(selection = noSelection; @Return(""); @success);

You need to debug one lookup at a time and see where the error is.
Is it for Web or notes?

If it is for web, then use server as ""
Avatar of captain

ASKER

Hi

it is Notes only.

Made change, it now returns ALL members from the list, not the selected one.

Thanks
May I ask, what your code needs to accomplish?
Right now, you get list of members of Group "Acc-Managers", then show it as prompt, and when user has selected a name, the field Studio is populated with the same set of names, where selection was made.

If you just want to populate field Studio with members of Acc-Managers group, then code can be simplified:

By the way, your code is working OK, if something is wrong, then it must be with names of design elements.
REM {Data options};
targetFieldName := "Studio";
      sourceViewName := "Groups";
      sourceGroup :="Acc-Managers";
      sourceField := "Members";
      sourceServer := "server";
      sourceDatabase := "Names.nsf";
 
 
      REM {Prompt options};
      promptTitle := "Studio Select";
      promptMessage := "Assign studio staff from the list:";
      promptError := "No choices are available.";
promptError1 := "Your choice returned no result.";
      promptMultiple := @False;
 
      REM {Get the choices.};
      viewLookup := @DbLookup("":"NoCache"; sourceServer : sourceDatabase; sourceViewName; sourceGroup;sourceField );
      @If(@IsError(viewLookup); @Return(@Prompt([Ok]; "Lookup Error"; promptError)); "");
      choiceList := @Name([CN];viewLookup);
 
      @If(choiceList = ""; @Return(@Prompt ([Ok]; "No Choices"; promptError)); "");
 
      REM {Update the target field};
      @SetField(targetFieldName; choiceList);
      @Command([ViewRefreshFields])

Open in new window

If you like to populate the field Studio with ONE name, selected from list, then the second lookup on rows 27-29 is unnecessary.

It resets the variable "selection" and field Studio will be empty.
... or more precisely, if lookup succeeds, the variable and field will  be populated with group member names in canonical format. If the field is empty, the lookup has failed. Suspect server name or database name, if server is on Unix system then "Names.nsf" is not the same as "names.nsf", a default file name for Directory.
Avatar of captain

ASKER

Hi olaraak

you may be on to something...

The user is populating a field to assign a another user with a task. The other user is from a select group which is available for these tasks such as in this case Studio work. There will be more fields in the workflow later on all adhering to the same criteria.

The dialog list sjould present the available choices of which the user should select only one, and the dialog list should make it easier rather than using the address book for choices.

What I noticed is that my code is populating it with the full member list from the group rather than picking the selected member and setting the field value from there. So the code does work just not as I intended...


thanks
capt.
Avatar of captain

ASKER

Hi

Your code now dispenses with the dialog and populates all members straight away...

So I need to modify to select from dialog and retain selection in field.

hope this makes sense
capt.
Avatar of captain

ASKER

I think my thought problem comes from the fact that the members field is just one filed with multiple entries, which are than returned if one of it is selected....

Should I rewrite the code to look up the selection in names.nsf people view and return this value instead?

Surely there must be an elegant way to select users from lists without having to rely on pre-defined dialog lists. I need this to be dynamic so if a staff member moves roles all avalanche selections are updated, as the role change is done via groups in the DAB it seemed a good idea to go down this route...
Avatar of captain

ASKER

gotta love auto correct..

avalanche = relevant
ASKER CERTIFIED SOLUTION
Avatar of olaraak
olaraak
Flag of Estonia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of captain

ASKER

Hi

I will play around with it tonite and feedback tomorrow.

Many thanks so far

capt.
Avatar of captain

ASKER

Tiptop, works first class...

Many thanks
capt.