Solved

First occurrence of DBLookup

Posted on 2000-04-23
22
543 Views
Last Modified: 2013-12-18
Hi,
Does anyone know how to show the first occurrence of @DbLookup result in a field?

For example, in a keyword field, I have a @Dblookup formula and I want to display the first occurrence of the lookup result in the same field.

Does anybody know how to do it?

Thanks a lot in advance!
0
Comment
Question by:kspuea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 4
  • 3
  • +3
22 Comments
 
LVL 2

Expert Comment

by:amit_dutta
ID: 2742845
Hello kspuea,

If u want the first occurence of Dblookup then use the Dlookup formula inside a @Subset formula.
Example: @Subset(@Dblookup; 1)
This will return the first occurance. For the last occurance use -1 instead of 1 in the @subset parameter.

Hope this will serve ur purpose.
0
 

Author Comment

by:kspuea
ID: 2746854
Adjusted points from 30 to 50
0
 

Author Comment

by:kspuea
ID: 2746855
Thanks, amit dutta!
Maybe I didn't make myself clear.
I still need the rest of occurrences of my @DbLookup while showing the first occurrence without clicking on the keywordfield.

However, @Subset only return the first occurrence but not the rest too.


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:ghassan99
ID: 2746976
Put this @Subset(@Dblookup; 1) in the default value formula for the field.

-Gus
0
 

Author Comment

by:kspuea
ID: 2747001
Thanks, ghassan99!
However, I need to reference a field (called Dept) for my DBLookup.
Thus, if I put the @Subset(@Dblookup; 1) in the default value event, an error occurs.

Here is my @DBLookup in the keyword field:
@If(@IsDocBeingEdited;@DbLookup( "" : "NoCache" ; "" : "" ; "ProfileByDept" ; Dept ; "ProfileFC" );@Unavailable)
0
 
LVL 6

Expert Comment

by:ghassan99
ID: 2747105
what error r u getting?

I tried it works fine...

-Gus
0
 

Expert Comment

by:mkngau
ID: 2753638
I think what ghassan proposed is correct. But you need to do some modification. Here is it:

@If(@IsDocBeingEdited;
    @subset(@DbLookup( "" : "NoCache" ; "" : "" ; "ProfileByDept" ;Dept ; "ProfileFC" );1)  
;@Unavailable)

you put this in the default value formula for the field, it should work. you know the 'default value formula for the field', right?

 
0
 

Author Comment

by:kspuea
ID: 2753745
Yes, I know. But this formula works if the Dept field has a value already, right?

My workflow is as follows:
1) When user first creates the doc, all the fields are empty.
2) Then he needs to select the Requestor name, and the Dept will have values based on the Requestor field.
3) After that, the editable keyword field called "TCFC" should display the first occurrence of @DbLookup.

0
 

Expert Comment

by:mkngau
ID: 2753859
you can check on this

http://www1.experts-exchange.com/Computers/Groupware/Lotus_Notes/Q_10186618.html

ghassan99 have solved for me last time.
It might match you case
0
 

Expert Comment

by:mkngau
ID: 2753936
you can check on this

http://www1.experts-exchange.com/Computers/Groupware/Lotus_Notes/Q_10186618.html

ghassan99 have solved for me last time.
It might match you case
0
 
LVL 6

Expert Comment

by:ghassan99
ID: 2754301
Which Notes version r u using?  I tried in R5, it works fine actually even if Dept is empty.

mkngau,
Lee how r u?:) Nice of u to jump in:)

-Gus
0
 

Author Comment

by:kspuea
ID: 2757706
Hi Gus,
I'm using Notes v4.6.3 and your method doesn't work for my case.
0
 
LVL 5

Expert Comment

by:snocross
ID: 2769539
What is the error message you are getting or is it just not returning a value?
0
 
LVL 5

Expert Comment

by:snocross
ID: 2769838
Are you doing this through a web browser?
0
 

Author Comment

by:kspuea
ID: 2771564
No, I'm doing it through a web browser.
It's in a Notes form.
I get this error message something as follows:
"Entry not found in index"
if I put the formula in the Default Value of the field.

And I get only first occurrence of the @DbLookup in the field, if I put the formula inside the "Choices" property of the keyword field.
0
 
LVL 5

Expert Comment

by:snocross
ID: 2773403
Assuming I'm understanding you correctly I have got it to work:

Steps to recreate:

1) Create a keywords field called ReqName.  
- Make sure the field allows multi-values.
- Use formula for choices... I used a dblookup to return a multivalue text list of names.

2) Create a compute field called Dept which will show you all the departments the Requestor name belongs to.
- @If(ReqName != "";@DbLookup( "" : "NoCache" ; "yourserver" : "yourdb" ; "System Configuration" ; ReqName ; "Logging" );"")
- This field can eventually be hidden if you want it to be.

3) Create a third field that will display the first dept in the list.  I called my field FirstDept
- Use this formula: @Subset(Dept;1)

IMPORTANT:  For this to work without having to save the document (after you have selected a requestor) then you will need to select AUTOMATICALLY REFRESH FIELDS in the form properties/defaults options.

I hope this is what you're looking for.
0
 

Author Comment

by:kspuea
ID: 2780317
Thanks, snocross. However, what I want to @Subset is the @DbLookup result in keyword field, not the Dept field.
I just need to use Dept field value to do the @DbLookup.
0
 
LVL 5

Expert Comment

by:snocross
ID: 2780488
Ok, so what type of field is your DEPT field?  Is it editable (does the user just type a dept) or is it a keywords field (does the user pick a dept from a list) or computed (the dept is automatically put in the dept field)?
0
 

Author Comment

by:kspuea
ID: 2784959
yes, it's a computed field.
0
 
LVL 3

Accepted Solution

by:
Joep8020 earned 50 total points
ID: 2841571
I would do it like this:

Make the ReqName field computed when composed. In the value use the formula @PickList([Names]). Then make the Dept field also computed when composed.

Create a hidden computed field named TCFClist containing the formula (this is for performance reasons, you only have to do 1 dblookup this way):

tList:=@If(@IsDocBeingEdited;@DbLookup( "" : "NoCache" ; "" : "" ; "ProfileByDept" ; Dept ; "ProfileFC" );@Unavailable);
@If(@IsError(tList);"";tList)

This field should be below the DEPT field.

Finally in your TCFC field use this formula for your keywords:
TCFClist

and this one for your default value:
@Subset(TCFClist;1)

Hope this helps you

0
 

Author Comment

by:kspuea
ID: 2902826
Comment accepted as answer
0
 

Author Comment

by:kspuea
ID: 2902827
Thanks for your enlighten, Joep8020!
I did something like your method.
But I didn't create a new field to store TCFC. I just coded something in the Exiting Event of the previous field before the TCFC field. And it works for me, finally.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question