• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Create a repeated lookup

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
shuboarder
Asked:
shuboarder
  • 8
  • 5
  • 3
  • +1
1 Solution
 
marilyngCommented:
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
 
Steve KnightIT ConsultancyCommented:
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
 
Sjef BosmanGroupware ConsultantCommented:
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
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!

 
shuboarderAuthor Commented:
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
 
shuboarderAuthor Commented:
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
 
Steve KnightIT ConsultancyCommented:
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
 
Sjef BosmanGroupware ConsultantCommented:
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
 
shuboarderAuthor Commented:
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
 
shuboarderAuthor Commented:
>> 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
 
shuboarderAuthor Commented:
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
 
Steve KnightIT ConsultancyCommented:
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
 
Sjef BosmanGroupware ConsultantCommented:
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
 
shuboarderAuthor Commented:
>>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
 
Sjef BosmanGroupware ConsultantCommented:
So the view is large? Or you never tried?
0
 
shuboarderAuthor Commented:
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
 
Sjef BosmanGroupware ConsultantCommented:
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
 
shuboarderAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now