Queryopen, @Prompt, and FIELD value setting

HCL Domino Domino Forum
1

Greetings all. I have recently rolled out my first database application, and it has met rave reviews. There have been requests for additional functionality (when is there NOT), one of which I am trying to fulfill right now.

My application is a Work Order Request System, in which our “customers” can request that my group perform tasks. The addition I am working on is the ability to open a Work Order Request for a customer (based on a phone call, etc). When the user opens their own Work Order, a series of fields is automatically populated with information they provided upon registering within the application:

Requestor Name

Requestor Manager

Requestor Phone #

Requestor Team

Requestor Mailcode

Requestor Notes Address

Requestor SMTP Address

Request Number (attained by seeing how many requests the person had already submitted, adding 1 to the total, and appending the # to their initials)

What I want to have is a form that we (admins) can use to open Work Orders for the users, but fill in all their information for them so that they are the owner, and it counts towards their Request Number count. I have added some code to my QueryOpen event which is supposed to prompt with a list of registered users to open the Work Order for, and upon selecting the name the rest of the above fields would fill in automatically. Here is what I have so far that is not working:

USERS := @DbColumn(“”:“noCache”;“”:“”;“AllUsersByName”;1);

WOR_REQ := @Prompt([OkCancelList]; “Select A User”; “Select The User You Are Opening This Work Order For”; “”; USERS);

FIELD RequestorName := @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 1);

FIELD RequestorManager := @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 3);

FIELD RequestorPhoneNumber := @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 5);

FIELD RequestorTeam := @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 4);

FIELD RequestorMailcode := @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 6);

FIELD RequestorNotesAddress := @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 7);

FIELD RequestorSMTPAddress := @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 8);

@Success

When I preview the form in the Notes client, no Prompt box appears and it goes to the first line in the Tab Order. So I am apparently missing something. Given that I am a newbie at this whole Notes/Domino development thing, I am sure it is my own ignorance preventing this from working. Any help would be greatly appreciated! Thank you!

Chris Ivey

Verizon Data Services

Enterprise Network Support Services

Network Management Tools Group

Temple Terrace, Florida

2

Subject: Queryopen, @Prompt, and FIELD value setting

Use InPreviewPane property

3

Subject: RE: Queryopen, @Prompt, and FIELD value setting

Thank you for your quick reply, Vivek. However, I have read the help on “InPreviewPane”, and I can not see how this will help me. The document is not in the preview pane, as it is being accessed by an outline entry. Also, that is a LotusScript command, and I am wanting to use @Commands. Thanks again, though!

Chris Ivey

Verizon Data Services

Enterprise Network Support Services

Network Management Tools Group

Temple Terrace, Florida

4

Subject: RE: Queryopen, @Prompt, and FIELD value setting

Hi ChrisHave to say first AAAAARRRRGGGHH!

OK, got that off my chest 8^)

What you want to use is a Picklist pointing to a view of people in your button

That will return for you a whol slew of values that you can stick into the various fields on your form

OR you can do a regular prompt ([OKCANCELLIST]) and then do ONE lookup

Set up your lookup view to have all the valus you want concatenated by (say) #~#~#

Now when you do a lookup put it into a single field (say, lookupValues) and get just the one column; Now each field can split it out using @word (e.g. @word(lookupValues;“#~#~#”;1)

Give either of those a whirl, and see how you go

5

Subject: RE: Queryopen, @Prompt, and FIELD value setting

Hi Chris. Well, let me start by saying I understand the “AAAAARRRRGGGHH”, as my novice coding is certainly not a thing of beauty. I have found that most of it is effective, however, which is what matters… right? :o)

Directly related to this posting, however, I got around it late yesterday afternoon. I was trying to make it too complicated (K.I.S.S. anyone???)… so I simplified. I moved my code behind a button, and just have my group click that button first. Duh! :o)

Although, there does seem to be an issue with Queryopen and @DBColumn. I moved the identical code behind a button, and it works. But putting that code into the Queryopen caused it to not be happy each time. Bizarre…

By the way, here is the code behind my button:

===============================================

USERS := @DbColumn(“” : “nocache”; “” : “”; “AllUsersByName”; 1);

WOR_REQ := @Prompt([OkCancelList]; “Select A User”; “Select The User You Are Opening This Work Order For”; “”; USERS);

@SetField(“RequestorName”; @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 1));

@SetField(“RequestorManager”; @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 3));

@SetField(“RequestorPhone”; @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 5));

@SetField(“RequestorDept”; @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 4));

@SetField(“RequestorMailcode”; @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 6));

@SetField(“RequestorNotesAddress”; @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 7));

@SetField(“RequestorSMTPAddress”; @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 8));

USERNAME := @DbLookup(“” : “nocache” ; “” : “” ; “AllUsersByName”; WOR_REQ ; 1);

@If

(@IsNewDoc;

	@SetField("RequestNumber"; @Left(USERNAME;1) + @Middle(USERNAME;" ";1) + @Left(@RightBack(USERNAME;" ");1) + @Text(1 + (@Elements(@DbLookup("":"noCache";"":"";"RequestsByName";USERNAME;2;[FailSilent])))));

	@SetField("RequestNumber"; RequestNumber)

)

===============================================

I have taken your suggestions above and filed them away in my ever growing file of “Notes code snippets that I may need later on”, so I certainly appreciate the help!

Chris Ivey

Verizon Data Services

Enterprise Network Support Services

Network Management Tools Group

Temple Terrace, Florida

6

Subject: RE: Queryopen, @Prompt, and FIELD value setting

"most of it is effective"Um No

To have 8 or so lookups instead of one is not only inefficient, it is going to turn around and bite you in the bum one day.

A @dblookup is about the slowest thing you can do, so reducing the numebr for any one action is the kkey to writing cleansimple efficient code

Also, nowhere in your code to cope with the potential error of the record not be found

This is the normal method

list:=@dblookup(all good stuff here)

@if(@iserror(list);“some error value”;list)

Now if you were getting all that from one lookup and putting it in one ComputedForDisplay field it would now look something like this

john#~#smith#~#the company#~#the address#~#the email

so the firstname field is

@word(myfield;"#~#;1)

the lastname is

@word(myfield;"#~#;2)

etc. etc.

Can you see how easy your debugging will be later?

All you do is make that a non-hidden field to check what’s in it, and !sorted!

Chris