Improve company productivity with a Business Account.Sign Up

x
?
Solved

Create a repeated lookup

Posted on 2006-06-22
20
Medium Priority
?
262 Views
Last Modified: 2013-12-18
Hi,

this is quite a complicated one I think...

I would like to create a form that can perform multiple lookups.
Here's how I would like it to work.

Field 1: Text field - editable
The user types in a value

Field 2: Text field - Computed
This performs a lookup on Field 1 to view 1 and returns multiple values.

Here comes the tricky part....

Field 3: Text field - Computed
This performs a lookup on each value in field 2 individually and returns multiple values

and so on.

Any words of wisdom on this please?
I am thinking I will need to create fields dynamically somehow?
0
Comment
Question by:shuboarder
  • 8
  • 5
  • 3
  • +1
17 Comments
 
LVL 18

Expert Comment

by:marilyng
ID: 16961815
Off hand you would probably need to put the field 3 lookup in the post recalc event, so that it engages after the field 2 lookups.
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 16963141
Just thinking out loud here really, but perhaps mark field3 as "compute after validation" in the field properties.  Otherwise there isn't actually an issue surely with just having them all computed from each other as long as they are top to bottom on a page.

form.  Automatically Refresh.

field1.  editable.

field2.  computed.  @dbLookup( etc.(

field3.  computed.  compute after validation.  multiple @dblookups based on field2 ??

I suppose depedning upon no. of values expected in field 2 it may be easier to write the code in LotusScript to populate it?

Steve

0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16964090
Can be done in LotusScript, of course, but it can also be in Formula language, even without multiple lookups. If that still works, I have to say, because the feature is not very well-know.

Adding to Steve's response:
    field2, computed, multi-value:
        @DbLookup(""; ""; "View"; field1; ...)

    field3, computed, multi-value:
        @DbLookup(""; ""; "View"; field2; ...)

    etc.

Now it used to be possible to use a multi-value key in an @DbLookup, providing that ALL keys exist in the view. The first non-available key in the view terminates the lookup. To make sure you have only available keys, the formula in field3 should be something like
        keys:= @Keywords(@DbColumn(""; ""; "View"; 1); field2; "");
        @DbLookup(""; ""; "View"; keys; ...)

AFAIK, compute after validation isn't necessary. It is important though that field3 be placed after field2 on the form.

For large views this won't work, due to the 64K limit, or it might be very slow.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
LVL 21

Author Comment

by:shuboarder
ID: 16967080
Perhaps I'm not getting you guys... but just to clarify

If user inputs for example the word "fruit" in to field 1 and refreshes

Field 2 should lookup "fruit" in the view and return all the values to the field i.e

Apples
Pears
Oranges
Grapes

Field 3 should then lookup "Apples" "Pears" "Oranges" "Grapes" (4 separate lookups)

and return  all the values to the field.

If it helps it is always the same columns involved i.e column 1 contains the fruit group and all of the fruits, but there are groups under fruits which also exist in this column and groups under that etc.
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16967145
Dragon-it

The issue is that fields are going to contain multiple values and I want the lookup to lookup the individual values within these fields.

Am I missing something?
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 16967259
sjef is suggesting that if you use a multi-value field to do a lookup into a categorised view then all the categories are returned.  I'd never tried this, assuming it would not work and have programmed around this various times by doing multiple lookups and combining them.

Give it a try -- i.e. just use field2 as key into your next view.

If not and if not able to use LotusScript then you're going to have to look at splitting down the field2 into values using @Subset or @Word etc. and hard coding the no. of lookups or potentially using an R6+ loop construct.

It would be nice if the multi-value lookup does work though...

Steve
0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 2000 total points
ID: 16967431
Let's have your formulas for those fields, so we can modify them. Multi-value lookups used to work, so let's try it again, shall we? :)
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16967479
Hi Sjef,

just as you posted I appear to have figured it out....

Here is what I am using:

keys:= @Keywords(@DbColumn(""; "NoCache";"";"replica:id";"GroupMembers"; 5); Level_1; "");
res:= @DbLookup("":"NoCache";"replica:id";"GroupMembers";Level_1;5);
    @If(@IsError(res);"error";res)

This is returning the correct data into field 3.

Now... is there a way to seperate this i.e. so that when the end of the first list is reached a new line is put between that and the next set of results?
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16967513
>> Now it used to be possible to use a multi-value key in an @DbLookup, providing that ALL keys exist in the view. The first non-available key in the view terminates the lookup. To make sure you have only available keys, the formula in field3 should be something like

Sjef, now looking at level_3 field:

If a value in the list in level_2 field cannot be looked up in the view how can I prevent the error and just have it skip to the next value?

i.e. have it return something like "Final Group Reached"
0
 
LVL 21

Author Comment

by:shuboarder
ID: 16967661
Sjef, ignore the last post... that part seems to work.

I notice it adds a new line when it can't find a lookup.
There must be a way perhaps using @newline somewhere to get it to add a blank line after each result set.

I'm working on it, but anyone can think of an easy way to achieve this, please let me know!
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 16967779
If it adds a blank line when it can't find a lookup then I suppose you could have it lookup alternately you list and a dummy value.  Trying to think of a combination of commands to do this without doing a loop... i.e.:

"lookup1":"rubbsih":"lookup2":"rubbish":"lookup3" etc.

If that is the way t o go then you could create a loop, but then in that case you might aswell do n lookups in a loop and combine anyway I suppose.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16968170
To use the NoCache multiple times seems a waste to me. I suggest you put a multi-value computed for display-field on the form, with the @DbColumn-formula.

Your formula seems wrong to me, you missed the essence of the story:
    keys:= @Keywords(@DbColumn(""; "NoCache";"";"replica:id";"GroupMembers"; 5); Level_1; "");
    res:= @DbLookup("":"NoCache";"replica:id";"GroupMembers";Level_1;5);
    @If(@IsError(res);"error";res)

The variable keys isn't even used...

    keys:= @Keywords(@DbColumn(""; "NoCache";"";"replica:id";"GroupMembers"; 5); Level_1; "");
    res:= @DbLookup("":"NoCache";"replica:id";"GroupMembers";keys;5);
    @If(@IsError(res);"error";res)
0
 
LVL 21

Author Comment

by:shuboarder
ID: 17036231
>>For large views this won't work, due to the 64K limit, or it might be very slow.

Not really gonna work then I don't think...
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 17036318
So the view is large? Or you never tried?
0
 
LVL 21

Author Comment

by:shuboarder
ID: 17036403
It all seems to work fine until I pick a value that is going to return a lot of results. Then it just errors out.

I don't think the view is too large, but the data returned by the @DBLookup certainly seems to be...
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 17036444
Back to plan B then... I'd use a LotusScript solution. Not that I don't like Formula, on the contrary, but I have more control with Script.
0
 
LVL 21

Author Comment

by:shuboarder
ID: 17120315
Hi,

the Formula way of doing this works to an extent, but not entirely.
If someone can come up with a Lotus script way of doing this that would be great.

Otherwise, Sjef's comment should be accepted.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

Join & Write a Comment

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
Watch the video to know how one can repair corrupt Exchange OST file effortlessly and convert OST emails to MS Outlook PST file format by using Kernel for OST to PST converter tool. It can convert OST to MSG, MBOX, EML to access them. It can migrate…
Watch the working video to know how to import Outlook PST/OST files to Amazon WorkMail. Kernel released this tool which is very easy to use and migrate single or multiple PST and OST files to Amazon WorkMail. To know more about Kernel Import PST to …

595 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