Solved

@Dowhile? - Help creating a loop

Posted on 2007-12-03
10
219 Views
Last Modified: 2013-12-18
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!
0
Comment
Question by:shuboarder
  • 4
  • 4
  • 2
10 Comments
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 20395460
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

0
 
LVL 21

Author Comment

by:shuboarder
ID: 20395653
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
0
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 20395890
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

0
 
LVL 21

Author Comment

by:shuboarder
ID: 20395953
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.
0
 
LVL 22

Expert Comment

by:Bill-Hanson
ID: 20396233
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

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 21

Author Comment

by:shuboarder
ID: 20396527
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
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 20396663
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

0
 
LVL 22

Expert Comment

by:mbonaci
ID: 20396719
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

0
 
LVL 22

Accepted Solution

by:
Bill-Hanson earned 500 total points
ID: 20397351
Ok, that was not mentioned before.  You just need to make a slight adjustment.

In ViewB, add a 2nd column that contains the whole number, so column 1 will show just the part we are searching for and column 2 will contain the whole number.

Now we just modify the lookup to use the first column as the lookup key, but return the contents of the second column.

The only difference between this code and my last post is the column number in the @DbLookup.
lu_key := "67891";

lu_data := @DbLookup("":"NoCache";"":"";"ViewB";lu_key;2);

lu_count := @If(@IsError(lu_data); 0; @Elements(@Trim(@Unique(lu_data))));

Open in new window

0
 
LVL 21

Author Closing Comment

by:shuboarder
ID: 31412314
Thanks for your help!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Lotus Domino - LKMgr Long Held Lock Dump 10 2,684
Bulk lotus notes account creation 3 74
Lotus Notes Calendar Help 6 98
Lotus Notes Database - Include button in Email Notification 8 59
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

930 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

11 Experts available now in Live!

Get 1:1 Help Now