asked on

@Dowhile? - Help creating a loop

Hi,

I am trying to build a query based on the following:

user enters a number in fieldA e.g. 123456
I then want another field to loop through a view and find the number of variations of this number.
All variants will be in the form 123456.1, 123456.2, 123456.3 etc.

I'm guessing I need to create some form of loop coupled with a lookup?

My view "ViewA" displays all number variants in column1

So what I need is when someone enters 67891
a lookup is performed on the view.... lets say it finds:

67891.3
67891.6
67891.7
67891.9
67891.1
67891.2

I want the field to return 6

I was toying around with:

n :=1;
k := wo_no + "." + n;

@DoWhile(

@DbLookup("":"NoCache";"":"";"ViewA";k;1);
n := n + 1);

I'm not sure if I'm even close with this?

Hope this makes sense?

Thanks for any help!

Bill-Hanson

You don't need a loop. You just need to use @DbLookup with the optional [PARTIALMATCH] keyword.

From the Designer Help:

"[PARTIALMATCH] returns a match if the key matches the beginning characters of the column value."

Example:

lu_key := "67891";
lu_data := @DbLookup("":"NoCache";"":"";"ViewA";k;1; [PARTIALMATCH]);
lu_count := @If(@IsError(lu_data); 0; @Elements(lu_data));

Open in new window

shuboarder

ASKER

Hi Bill,

thanks for the quick reply...

Whilst this is returning me an answer, the answer it returns is always incorrect i.e. less than the actually number of partial matches in the view column

For example:

1002068.1
1002068.10
1002068.2
1002068.3
1002068.4
1002068.5
1002068.6
1002068.7
1002068.8
1002068.9

Returns: 5

Bill-Hanson

Hmmm... Well, that's what I get for trusting the documentation (I've never actually used that keyword in production).

In that case, I would create a new hidden view that displays only the part that you want to match. For example:

(1) Create a copy of "ViewA" (I'll call it "ViewB").
(2) Change the first column to display only the part to match. Example: @Left(FIELD_NAME; "."). Make sure the column is sorted.
(3) Change your lookup to:

lu_key := "67891";
lu_data := @DbLookup("":"NoCache";"":"";"ViewB";lu_key;1);
lu_count := @If(@IsError(lu_data); 0; @Elements(lu_data));

Open in new window

shuboarder

ASKER

Hi Bill,

I have already tried this approach.

The problem is that each variation (e.g. 10000.1) can have multiple documents
but I only want to know the number of variations.

If I create the view as suggested I might have:

10000.1
10000.1
10000.1
10000.1
10000.1
10000.1
10000.2
10000.2
10000.3
10000.3
10000.3

In this case I would want to return 3 not 11.

Bill-Hanson

No problem. Just use @Trim and @Unique on the results.

lu_key := "67891";
lu_data := @DbLookup("":"NoCache";"":"";"ViewB";lu_key;1);
lu_count := @If(@IsError(lu_data); 0; @Elements(@Trim(@Unique(lu_data))));

Open in new window

shuboarder

ASKER

Unfortunately @unique will return 1
since I am only performing the lookup on the characters before the decimal point.

i.e.

lu_key := "10000";
lu_data := @DbLookup("":"NoCache";"":"";"ViewB";lu_key;1);
lu_count := @If(@IsError(lu_data); 0; @Elements(@Trim(@Unique(lu_data))));

will look at the above view with @Left(Fieldname;".")

and see:

10000
10000
10000
10000
10000
10000
10000
10000
10000
10000
10000

@unique will then return 1

mbonaci

The best way to do this is by using view lookup with exact match param set to False, like this:

Function CountLookups( ) As Long
	Dim s As New NotesSession
	Dim db As NotesDatabase
	Dim v As NotesView
	Dim c As NotesDocumentCollection
 
	Set db = s.CurrentDatabase
	Set v = db.GetView( "ViewA" )
	Set c = v.GetAllDocumentsByKey( "67891", False )
	
	CountLookups = c.Count
End Function

Open in new window

mbonaci

Or in the better version:

Function CountLookups( key As String ) As Long
	On Error Goto ErrHandler
	Dim s As New NotesSession
	Dim db As NotesDatabase
	Dim v As NotesView
	Dim c As NotesDocumentCollection
 
	Set db = s.CurrentDatabase
	Set v = db.GetView( "ViewA" )
	Set c = v.GetAllDocumentsByKey( key, False )
 
	CountLookups = 0
	If c Is Nothing Then Exit Function
	CountLookups = c.Count
 
leave:
	Exit Function
ErrHandler:
	Msgbox |Function CountLookups| & Error$ & "(" & Err & ") in line " & Cstr( Erl ) & ".", 16, "Error"
	Resume leave
End Function

Open in new window

ASKER CERTIFIED SOLUTION

Bill-Hanson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shuboarder

ASKER

Thanks for your help!