Create a repeated lookup

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.

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

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.

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.

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?

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

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?

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

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!

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.

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)

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

So the view is large? Or you never tried?

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

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.

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.