Solved

SELECT Statement involving Multiple forms

Posted on 2004-10-14
12
251 Views
Last Modified: 2013-12-18
I am trying to construct a private view that will allow me to work with an extremely large (apx 4 GB) Notes database that I don't have any aministrator privileges over.  I'm running into a problem constructing my SELECT statement.

Here is what I have:

SELECT (form="Form_A" | form="Form_B") & Form_B_Field_1 != "" &
Form_B_Field_2="Text_1" &
(Form_B_Field_3="Text_2" | Form_B_Field_3="Text_3")&
(Form_A_Field_1="Text_4" & @Contains(Form_B_Field_4;"Text_5"))) &
(@TextToTime(Form_B_Field_5) > @TextToTime("09/15/04 07:01 GMT"))

Note that I have labeled each field so as to indicate which form it is a member of (I am new to Notes, so apologies if my terminology is incorrect or concepts are a little bit fuzzy.)  My experimentation seems to imply that the problem seems to lie with the fact that I am trying to construct a SELECT statement that selects on fields of more that one form.

I originally had the following, reduced select statment, which worked fine:

SELECT form="Form_B" & Form_B_Field_1 != "" &
Form_B_Field_2="Text_1" &
(Form_B_Field_3="Text_2" | Form_B_Field_3="Text_3")&
(@TextToTime(Form_B_Field_5) > @TextToTime("09/15/04 07:01 GMT"))

As you can see, the difference is that it only selects form B and includes fields from form B.  When I added the term:

(Form_A_Field_1="Text_4" & @Contains(Form_B_Field_4;"Text_5")))

which contains fields from form A, the SELECT statement would return no records.  I tried to remedy this by adjusting the first term to be:

(form="Form_A" | form="Form_B")

but I have been unable to get this to work.

Is there any way to construct a SELECT statement that filters on fields from more than one form?

Thank you for your help.

MJW
0
Comment
Question by:mjw
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 9

Expert Comment

by:HappyFunBall
ID: 12311890
The Form is just another field on the document.  So you're really selecting a bunch of documents into your view.  I think you're looking for a combination like this:

SELECT (Form = "Form_A" & Form_A_Field_1 = "Text_4") | (Form = "Form_B" & @Contains(Form_B_Field_4;"Text_5")

To answer your question "Is there any way to construct a SELECT statement that filters on fields from more than one form?", the answer is YES.  Basically a view is looking at all the documents in the database REGARDLESS of form.  You don't have to include the form in the SELECT formula, but usually want to.  However, a statement like Field1 = "Yes" is valid and will return documents made with any number of forms provided they contain Field1 equal to "Yes"

Hope this helps!
0
 
LVL 3

Expert Comment

by:Andrea Ercolino
ID: 12311977
The SELECT in a NotesView is very different from the SELECT in SQL!!

In a NotesView the SELECT boolean formula always acts upon all the documents in the database, no matter what form they use. In particular, the form info is just another info, as any other field value, for this purpose.

So you have to be sure that there exist at least one document for which the whole SELECT boolean formula is true, for the view to show documents in it.

0
 
LVL 3

Expert Comment

by:Andrea Ercolino
ID: 12312008
Sorry, HappyFunBall... I'm always so slow at composing!!!
0
 

Author Comment

by:mjw
ID: 12312567
My apologies.  I made an error in typing my own SELECT statement.  All the translation to "Text1" and "Text2" threw me for a loop.  It should read:

SELECT (form = "Form_A" | form = "Form_B") &
Form_B_Field_1 != "" &
Form B_Field_2 = "Text_1" &
(Form_B_Field_3 = "Text_2" | Form_B_Field_3 = "Text_3") &
(Form_A_Field_1="Text_4" | (Form_A_Field_1="Text_5" &
@Contains(Form_B_Field_4;"Text_6"))) &
(@TextToTime(Form_B_Field_5) > @TexttoTime("09/15/04 07:01 GMT"))

The part of interest, I believe, is:

(Form_A_Field_1="Text_4" | (Form_A_Field_1="Text_5" &
@Contains(Form_B_Field_4;"Text_6")))

because it is the only place that involves fields from Form A.  Please correct me where I am wrong:

My current understanding is that this SELECT statement is going to be run again every record in the database.  Some records will be be from form A and they will only contain form A fields, some will only be from form B and will only contain form B fields, some may be from other forms and contain other fields.  When a form A record is being evaluated against the SELECT statement, fields from form B, which don't exist for this record will evaluate to false.  Likewise for form A.  This is why I'm not getting any records as a result.

I get the general idea of HappyFunBall's strategy of splitting up the form A and form B terms, but I'm not entirely sure how to apply that to this statement:

SELECT (form = "Form_A" | form = "Form_B") &
Form_B_Field_1 != "" &
Form B_Field_2 = "Text_1" &
(Form_B_Field_3 = "Text_2" | Form_B_Field_3 = "Text_3") &
(Form_A_Field_1="Text_4" | (Form_A_Field_1="Text_5" &
@Contains(Form_B_Field_4;"Text_6"))) &
(@TextToTime(Form_B_Field_5) > @TexttoTime("09/15/04 07:01 GMT"))

such that

(Form_A_Field_1="Text_4" | (Form_A_Field_1="Text_5" &
@Contains(Form_B_Field_4;"Text_6")))

this part retains its actual meaning and function.

Can you please clarify this further.

Thanks!
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 12312612
If the form B fields don't exist on form A, and teh form A fields don't exist on form B, and you are not trying to include blank values, then you don't even need to check the form.  The trick is to make sure they don't overlap.  So...

construct a view containing only B docs, w/all fields paranethesized):
SELECT Form = "Form_B" & (B_One = "One" | B_Two = "Two" & etc.)

Once you get that working, create another view with just the right A docs:
SELECT Form = "Form_A" & (A_One = "1" & etc)

When that's working, merge the two:
SELECT (B_One = "One" | B_Two = "Two" & etc.) | (A_One = "1" & etc)


Which would be the same as:
SELECT
    (Form = "Form_B" & (B_One = "One" | B_Two = "Two" & etc.))
    |
    (Form = "Form_A" & (A_One = "1" & etc))

0
 
LVL 31

Expert Comment

by:qwaletee
ID: 12312638
Just to clarify and correct your last statement (we were typing at the same time):

If a field does not exist on a document, the field will evaluate to a null string.  You thought it would evaluate to false.  Not so.  A comparison to a non-null value would.

So, for a Form A document, the following are all true:
Form_B_Field_1 = ""
Form_B_Field_1 != "whatever"
@IsUnAvailable(Form_B_Field_1)
!@IsAvailable(Form_B_Field_1)
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

 

Author Comment

by:mjw
ID: 12312721
I think the term:

(Form_A_Field_1="Text_4" | (Form_A_Field_1="Text_5" &
@Contains(Form_B_Field_4;"Text_6")))

Keeps it from factoring into separate groups of A terms and B terms as has been illustrated in the posts above.

This is because:

(form="Form_A" & Form_A_Field_1="Text_4" | Form_A_Field_1="Text_5") | (form="Form_B" & @Contains(Form_B_Field_4;"Text_6")) the term at the top of this post.  I think this strategy worked when I initially posted the select statement incorrectly, but now that I've got this term right, the "&" that binds the field from form A (Form_A_Field_) and that from form B (Form_B_Field_4) seems to make it impossible to represent the same logic while separating the A and B fields.

just doesn't have the same meaning as
0
 
LVL 9

Expert Comment

by:HappyFunBall
ID: 12312800
Sorry, but I'm getting dizzy from reading your last post.  Could you just explain in plain english what you are trying to see in the view?  I think that's an easier place to start and I'm sure if you can state what you want, we can help you translate it into Domino.
0
 

Author Comment

by:mjw
ID: 12313280
Okay, point well taken.  Believe it or not, I was trying to make my question less complicated by abstracting away the details of my particular business.  I'll try to strike a decent balance.

I have a database--it's huge (4  GB), I didn't set it up, and I'm new to it as well as to Notes, so I only understand the pieces I've worked with so far.  A record in this database represents a computer system.  Form A holds one set of fields, form B another.  We have software that periodically scans each system according to some schedule and stores the results in the database.  The systems are aggregated into groups and each group has a name.

I want my view to SELECT systems that meet the following criteria:

1) The record has a current set of results:

Form_B_CurrentResults !=""

2) The server is flagged as okay to process by the software:

Form_B_OkaytoScan="Yes"

3) US and Canadian systems only

(Form_B_Country="United States" |
Form_B_Country="Canada")

4) Systems are classified by category.  I want all category 1 systems.  I also want category 2 systems whose group name has the word "info" in it.

(Form_A_Cat="Cat 1" | (Form_A_Cat="Cat 2" & @Contains(Form_B_GroupName;"info")))

5) I want all systems whose results have been updated since I last opened my view.  (I used 9/15/04 as an example date--ideally I'd like to have the system prompt me for a date, but I don't know how to do this.)

(@TextToTime(Form_B_LastUpdateDate) > @TextToTime("09/15/04 07:01 GMT))

Please let me know if you need further clarification.

Thanks so much for your help.

MJW
0
 
LVL 3

Accepted Solution

by:
Andrea Ercolino earned 250 total points
ID: 12318187
a. you have to understand that in Notes there is no SQL join between different forms, so it's not possible to match corresponding documents

b. @Contains is case sensitive, so a better use is: @Contains( @Lowercase( Form_B_GroupName ); "info" ) )

c. qwaletee suggestion is a good method to help you clarify yourself, follow it

d. about 4th point "Categories", because of this point a. it won't work. After the "OR" will always be @False

e. about 5th point "Cut off date", a better choice it to implement this point through an agent that selects only the documents after a user specified date, and then have the view show only selected documents

f. maybe you will have better luck if you use an agent for selecting documents, instead of a view, and then put all selected documents in a folder

0
 

Author Comment

by:mjw
ID: 12320697
Raputa,

>d. about 4th point "Categories", because of this point >a. it won't work. After the "OR" will always be @False

>f. maybe you will have better luck if you use an agent >for selecting documents, instead of a view, and then >put all selected documents in a folder

Thanks for your response.  So, I am understanding you correctly that a Notes view will not allow me to accomplish what I want to accomplish because there's no way to accommodate logic like part 4 (categories) which involves fields from different forms, and that the only way I can reach my goal is to write an agent?

Thanks,

MJW
0
 
LVL 3

Expert Comment

by:Andrea Ercolino
ID: 12322180
I think an agent is simpler to understand from an SQL background than Notes elements like views and selection formulas. A LotusScript agent is just VisualBasic code that interacts with Notes objects.
A feature of agents that might interest you is that an agent can be configured to operate on all the documents not processed before, in previous runs of the same agent.

On the other side, views are much faster than agents, but do have limitations on the size of their index (130 MB)

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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