Creating a new view or using $VIMPeople ?

Hi there

Need a bit of advice.

I'm a newie - been given advice in general avoid creating views in other applications e.g. address book.

Problem :-
Require these fields for to be shown in a view, so I can use @dbcolumn + @dblookup - extract information.:-

User Name
Title
Company
Telephone
etc...

Suggested I use - $VIMPeople - but this only provides certain fields I require.  Columns shown :-

Full Name
FirstName
Middle Initial
LastName
MailAddress
Comment

Qn:Is there a system view I can use - that provides all fields I need - extract information from ? -

Thanks in advance

ImraneA




ImraneAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sjef BosmanGroupware ConsultantCommented:
Please read the Help-doc on @DbLookup carefully, you can retrieve any field, not just columns in a view. Last parameter of @DbLookup can be a field name.
0
p_parthaCommented:
Hi there
Dblookup can fetch any value irrespective of whether it is there in the view co lumn or not. The field has to be part of the documnet. Here is the syntax for fetching phone number from the $vimpeople

phoneNumber := @DbLookup("Notes" : "NoCache"; "" : "NAMES"; "$VIMPeople"; adjName; "OfficePhoneNumber");

where adjName is the key .

Click on the document properties to see teh field names and respectively substitute instead of OfficePhonenumeber to get teh other details

Hope i am clear

Partha
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HemanthaKumarCommented:
DBLookup can give you either field value or column value.

I believe I have answered this question to you already. But here is how the dblookup will look like

@DBLookup( "Notes":"NoCache"; "Server":"Database"; "ViewName"; "Key"; 3) --- This extracts 3rd column values

@DBLookup( "Notes":"NoCache"; "Server":"Database"; "ViewName"; "Key"; "TITLE") --- This extracts Title Field value

~Hemanth


0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Bozzie4IT ArchitectCommented:
Use @namelookup to do lookups in the addressbook.

to get the phonenumber, do:

@namelookup( [noupdate] ; adjName;"OfficePhoneNumber" )

The field adjName should be a names field, containing a full canonical name.  

Every field in the Person document (well, actually in any document in names.nsf) is retrievable this way.  No need to do @dblookups etc. to get data from the addressbook !

The @namelookup function has 1 very important advantage over @dblookups in names.nsf , and that is in situations where you have multiple addressbooks - @namelookup will search ALL addressbooks, whereas for @dblookup, you will have to code that yourself.

cheers,

Tom
0
Bozzie4IT ArchitectCommented:
So in your situation, you will create a form, with computed fields for every field you wish to show :

eg. field Firstname , with formula @namelookup( [noupdate] ; adjName ; "FirstName" )

Tom
0
qwaleteeCommented:
Everyone is addressing your @DbLookup issue, but not @DbColumn (you must have a relatively small company).

For @DbLookup, as everyone has posinted out, you can use field names instead of column values.  One thing nobody pointed out is that this is significantly slower than column lookup, unless there already is a column with that field.

For @DbColumn, there is no workaround.

Bozzie's second point is also a very good one.  In fact, I'd like to point out that it is even better than @DbLookup in that the lookup is much faster, as it uses the directory cache directly, and does not do a standard view lookup.


-----


If you ABSOLUTELY need to do @DbColumn (no other way for you to work), then you will need a new view.  If performance is critical, and enough to override other technical concerns, at a corporate level, then modify the view.

Otherwise, make do with the views you have.  Try replcaing the @DbColumn with some LotusScript.  Or, if the organization is does not use OU's, use @DbLookup against the ($PeopleGroupsByOrganization) view.
0
ImraneAAuthor Commented:
To -  Bozzie4

Your suggestion is best for this situration - no offensive to other experts - but 2 queries.

1.When I enter all details, on my "Submit" action - it says no User has been entered.  When in fact it has.  Could u explain this ?
2.What is the best way to extract email address of user ?

A.Button - retrieve details

type := "" : "NoCache" ;
db := "" : "names.nsf" ;
view := "$VIMPeople" ;
acol :=1;
aname :=@DbColumn(type ; db ; view ; acol);
selnames := @Prompt([OKCANCELLIST] ; "Select Contact";
"Select a Contact "; "" ; aname);
@If(@IsError(selnames);"";selnames);
Telephone := @NameLookup( [NoUpdate] ; selnames;"OfficePhoneNumber" );
FIELD Telephone := Telephone;
JobTitle := @NameLookup( [NoUpdate] ; selnames;"JobTitle");
FIELD JobTitle := JobTitle;
Company :=@NameLookup( [NoUpdate] ; selnames;"CompanyName");
FIELD Company := Company;
MiddleInitial_D :=@NameLookup( [NoUpdate] ; selnames;"MiddleInitial");
FirstName_D :=@NameLookup( [NoUpdate] ; selnames;"FirstName");
LastName_D :=
@NameLookup( [NoUpdate] ; selnames;"LastName");
User_String :=
@Trim(FirstName_D) +
@If(MiddleInitial_D = "";"";" " ) +
@Trim(MiddleInitial_D) +
@If(MiddleInitial_D = "";" ";" ") +
@Trim(LastName_D);
FIELD User := User_String;
MailServer_D :=
@NameLookup( [NoUpdate] ; selnames;"MailServer");
FIELD MailServer_D := MailServer_D;

B.Submit - action

FIELD status_call_d :=status_call_d;
FIELD Status_call_2_d :=Status_call_2_d;
@SetField("Status_call_2_d";"Unallocated");
@SetField("Status_call_d";"3");
@SetField("Ref_D";@Unique);
@Command([FileSave]);
subject := "Techna Helpdesk - Job No : "+ @UpperCase(Ref_D_1) + "---" +  Ref_D + "---" + ref_d_date;
body := "This call has been logged as Job No \'"  + @UpperCase(Ref_D_1) + "-" +  Ref_D + "\' on " +  ref_d_date + "  " + Ref_D_Time + "."+@NewLine + @NewLine + "This will be dealt with as soon as possible." + @NewLine + @NewLine + @NewLine + @NewLine + @V3UserName + @NewLine + ref_d_date ;
Summary := "New Call been raised with Job No : "  + @UpperCase(Ref_D_1) + "-" +  Ref_D + "";
@If(@Command([FileSave]) ; @Prompt([OK] ; "New Call been raised" ; Summary ) ; @Return(@ERROR) );
@If(@Command([FileSave]) ; @MailSend("i.akram@weir.co.uk";"";"";subject; Body;""); @Return(@ERROR) );
@If(@Command([FileSave]) ; @MailSend("Abigail CC Rennie/GB/LGE/Weir@TECNET";"";"";subject; Body;""); @Return(@ERROR) );
@If(@Command([FileSave]) ; @Command([FileCloseWindow]) ; @Return(@ERROR) )

C.QuerySave

Sub Querysave(Source As Notesuidocument, Continue As Variant)
      Dim flag
      '***AIM-Error Handlers
      '***AIM-Category_A field is populated
      If ( SOURCE.FieldGetText( "Category_A" ) ="" ) Then
            Messagebox "The Category field must be entered", MB_ICONEXCLAMATION, "Reminder!"
            Call Source.GoToField( "Category_A" )
         '***AIM-This line tells the stop executing
            Continue = False
            flag = True
            Exit Sub
      End If
      
      '***AIM-RequestType_A field is populated
      If ( SOURCE.FieldGetText( "RequestType_A" ) ="" ) Then
            Messagebox "The Request Type field must be entered", MB_ICONEXCLAMATION, "Reminder!"
            Call Source.GoToField( "RequestType_A" )
         '***AIM-This line tells the stop executing
            Continue = False
            flag = True            
            Exit Sub
      End If
      
      '***AIM-ServiceType_A field is populated
      If ( SOURCE.FieldGetText( "ServiceType_A" ) ="" ) Then
            Messagebox "The Service Type field must be entered", MB_ICONEXCLAMATION, "Reminder!"
            Call Source.GoToField( "ServiceType_A" )
         '***AIM-This line tells the stop executing
            Continue = False
            flag = True            
            Exit Sub
      End If
      
      '***AIM-Description field is populated
      If ( SOURCE.FieldGetText( "RequestType_A" ) ="Other" )  And ( SOURCE.FieldGetText( "Description" ) ="" ) Then
            Messagebox "The Request Type field you have selected 'Other' must enter details for Description field", MB_ICONEXCLAMATION, "Reminder!"
            Call Source.GoToField( "Description" )
         '***AIM-This line tells the stop executing
            Continue = False
            flag = True            
            Exit Sub
      End If
      If ( SOURCE.FieldGetText( "ServiceType_A" ) ="Other" )  And ( SOURCE.FieldGetText( "Description" ) ="" ) Then
            Messagebox "The Service Type field you have selected 'Other' must enter details for Description field", MB_ICONEXCLAMATION, "Reminder!"
            Call Source.GoToField( "Description" )
         '***AIM-This line tells the stop executing
            Continue = False
            flag = True            
            Exit Sub
      End If
      
      
      
      '***AIM-Email field is populated
      If ( SOURCE.FieldGetText( "Email" ) ="" ) Then
            Messagebox "The User field must be entered (via selecting Contact)", MB_ICONEXCLAMATION, "Reminder!"
            Call Source.GoToField( "Email" )
         '***AIM-This line tells the stop executing
            Continue = False
            flag = True            
            Exit Sub
      End If
      
End Sub
0
Bozzie4IT ArchitectCommented:
A.Button - retrieve details

type := "" : "NoCache" ;
db := @subset(@dbname;1): "names.nsf" ; <------- change this line
view := "$VIMPeople" ;
acol :=1;
aname :=@DbColumn(type ; db ; view ; acol);
selnames := @Prompt([OKCANCELLIST] ; "Select Contact";
"Select a Contact "; "" ; aname);
@If(@IsError(selnames);"";selnames);
Telephone := @NameLookup( [NoUpdate] ; selnames;"OfficePhoneNumber" );
FIELD Telephone := Telephone;
JobTitle := @NameLookup( [NoUpdate] ; selnames;"JobTitle");
FIELD JobTitle := JobTitle;
Company :=@NameLookup( [NoUpdate] ; selnames;"CompanyName");
FIELD Company := Company;
MiddleInitial_D :=@NameLookup( [NoUpdate] ; selnames;"MiddleInitial");
FirstName_D :=@NameLookup( [NoUpdate] ; selnames;"FirstName");
LastName_D :=
@NameLookup( [NoUpdate] ; selnames;"LastName");
User_String :=
@Trim(FirstName_D) +
@If(MiddleInitial_D = "";"";" " ) +
@Trim(MiddleInitial_D) +
@If(MiddleInitial_D = "";" ";" ") +
@Trim(LastName_D);
FIELD User := User_String;
MailServer_D :=
@NameLookup( [NoUpdate] ; selnames;"MailServer");
FIELD MailServer_D := MailServer_D;
0
Bozzie4IT ArchitectCommented:
B.Submit - action & C. QuerySave

AAAHH, no this is not good.   You'll need to change your code so that you only save once.

And why do you use Lotusscript to do validation ?  It's better (in your case) to move all validation to the fields - use Input  Validation.
You whole querysave can be removed that way.

Change the formula for your button to only save once, and you could move the @formula's to the QuerySave that way.  

Submit:
FIELD status_call_d :="3";
FIELD Status_call_2_d :="Unallocated";
FIELD Ref_D := @unique;
@command([filesave]);
@postedcommand([fileclosewindow])

QuerySave:

subject := "Techna Helpdesk - Job No : "+ @UpperCase(Ref_D_1) + "---" +  Ref_D + "---" + ref_d_date;
body := "This call has been logged as Job No \'"  + @UpperCase(Ref_D_1) + "-" +  Ref_D + "\' on " +  ref_d_date + "  " + Ref_D_Time + "."+@NewLine + @NewLine + "This will be dealt with as soon as possible." + @NewLine + @NewLine + @NewLine + @NewLine + @V3UserName + @NewLine + ref_d_date ;
Summary := "New Call been raised with Job No : "  + @UpperCase(Ref_D_1) + "-" +  Ref_D + "";

@MailSend("i.akram@weir.co.uk";"";"";subject; Body;"");
 @MailSend("Abigail CC Rennie/GB/LGE/Weir@TECNET";"";"";subject; Body;"");

This is not the definite code !
0
ImraneAAuthor Commented:
To -  Bozzie4

1."You'll need to change your code so that you only save once" - OK
2."And why do you use Lotusscript to do validation ?" - suggested by another Developer
3.OK - testing purposes
4.Can't use
db := @subset(@dbname;1): "names.nsf" ; <------- change this line
as format would not be agreeable to my line manager e.g. Full Name - nothing else.
5.Email consists of the following

name := @Name([Abbreviate]; @Subset(FullName;1));
@If(MailDomain != "" & MailAddress != ""; @Name([Abbreviate];MailAddress) + " @ " + MailDomain; MailAddress != ""; @Name([Abbreviate];MailAddress);MailDomain != ""; name + " @ " + MailDomain;  name)

I need to extract details from address book - whats the best way..

This what I trying...


FullName_D :=@NameLookup( [NoUpdate] ; selnames;"FullName");
MailDomain_D :=@NameLookup( [NoUpdate] ; selnames;"MailDomain");
MailAddress_D :=@NameLookup( [NoUpdate] ; selnames;"MailAddress");

name_D := @Name([Abbreviate]; @Subset(FullName_D;1));
@If(MailDomain_D= "" & MailAddress_D= ""; @Name([Abbreviate];MailAddress_D) + " @ " + MailDomain_D; MailAddress_D= ""; @Name([Abbreviate];MailAddress_D);MailDomain_D= ""; name_D + " @ " + MailDomain_D;  name_D);
FIELD Email := name_D;





0
ImraneAAuthor Commented:
PS.Just getting name only
0
Bozzie4IT ArchitectCommented:
The best way to retrieve an email address:

@namelookup( [noupdate] ; username ; "InternetAddress" )
and/or (depending on what's been filled in)

@namelookup( [noupdate] ; username ; "MailAddress" )

For Notes users, you'll use the username.  IF you want to retrieve the internetaddress, when it's not set in the persondocument, you'll have to build it yourself according to the rules in the global domain document (eg. firstname + "_" + lastlane + "@mydomain.com")

cheers,

Tom
0
Bozzie4IT ArchitectCommented:
MailDomain is not the internet domain.

the internet domain is quite elusive, so it's best (easiest) to just hardcode it.

that would make :
 inet := @namelookup( [noupdate] ; username ; "InternetAddress" );
@if(inet = ""; @set( "inet"; @namelookup( [noupdate] ; username ; "firstname" ) + "_"+ @namelookup( [noupdate] ; username ; "lastname" ) + "@mydomain.com" ); "");

Field email := inet;
0
Bozzie4IT ArchitectCommented:
Add in the Submit button
@if(@isvalid ; ""; @return(""));
...
@command([filesave])

to first check the input validation formulae for your fields.

cheers,

Tom
0
ImraneAAuthor Commented:
To -  Bozzie4

Your comments most helpful - but I have a major problem.

First, this app is being going to be used on multi-site basis !.  So, Divisional address book which I am using needs to be seen by every users who uses this app.

Willing to increase points - no problem...

Recap:
Type := "" : "NoCache" ;
db :=  "WWLGLA1" : "names.nsf"  ;  ***********
view := "$VIMPeople" ;
acol :=1;
aname :=@DbColumn(type ; db ; view ; acol);
selnames_d := @Prompt([OKCANCELLIST] ; "Select Contact";
"Select a Contact "; "" ; aname);

*****
1.If I leave this blank, get local address book.
2."WWLGLA1" - get this site divisional addressbook.
3."OtherSite???" - path to server not found

What I'm trying to say, is there common path to Divisional Address Book (from different sites) - people can use within this app ???

N.B. Not Notes Admin person !!!

Do u follow ?


0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.