kspuea
asked on
First occurrence of DBLookup
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!
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!
ASKER
Adjusted points from 30 to 50
ASKER
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.
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.
Put this @Subset(@Dblookup; 1) in the default value formula for the field.
-Gus
-Gus
ASKER
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;@DbL ookup( "" : "NoCache" ; "" : "" ; "ProfileByDept" ; Dept ; "ProfileFC" );@Unavailable)
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;@DbL
what error r u getting?
I tried it works fine...
-Gus
I tried it works fine...
-Gus
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?
@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?
ASKER
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.
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.
you can check on this
http://www1.experts-exchange.com/questions/10186618/set-the-field-to-be-fill-in-by-sequence.html
ghassan99 have solved for me last time.
It might match you case
http://www1.experts-exchange.com/questions/10186618/set-the-field-to-be-fill-in-by-sequence.html
ghassan99 have solved for me last time.
It might match you case
you can check on this
http://www1.experts-exchange.com/questions/10186618/set-the-field-to-be-fill-in-by-sequence.html
ghassan99 have solved for me last time.
It might match you case
http://www1.experts-exchange.com/questions/10186618/set-the-field-to-be-fill-in-by-sequence.html
ghassan99 have solved for me last time.
It might match you case
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
mkngau,
Lee how r u?:) Nice of u to jump in:)
-Gus
ASKER
Hi Gus,
I'm using Notes v4.6.3 and your method doesn't work for my case.
I'm using Notes v4.6.3 and your method doesn't work for my case.
What is the error message you are getting or is it just not returning a value?
Are you doing this through a web browser?
ASKER
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.
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.
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.
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.
ASKER
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.
I just need to use Dept field value to do the @DbLookup.
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)?
ASKER
yes, it's a computed field.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Comment accepted as answer
ASKER
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.
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.
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.