Solved

Create a repeated lookup

Posted on 2006-06-22
20
251 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
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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now