Solved

Create a repeated lookup

Posted on 2006-06-22
20
257 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
[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
  • 8
  • 5
  • 3
  • +1
20 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
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!

 
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 500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

632 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