How to add a field from a 2nd form?

Question

Hi all. I'm modifying an existing Lotus view to include a field from another form.

1. I first appended a new column and set it to the desired field. However, after I refreshed, the new column was blank even though I know it has data.

2. I then updated the View Selection formula from:

SELECT Form = "A" & StatusIndex < "06"
to:
SELECT (Form = "A"| Form = "B") & StatusIndex < "06"

Still no luck. The view is refreshing successfully, but the new field is still blank. What else is there to add this new column to this view?

This is my first time experimenting with Lotus, so if I seem to be missing some major concept, I probably am.

Edit

If I was pulling this data using SQL, the statement would probably be something like:

Select A.* , B.*  
from A inner join B on A.id=B.id  
where A.StatusIndex < "06";  

Which brings up another question: Where is the relationship between these tables/forms defined?

Answer 1

Here's a trick for adding multiple forms. This way you can easily add to the list of forms allowed without lots of OR statements.

@IsMember(Form; "A":"B") & StatusIndex < "06"

What I would try next, though, is to get rid of all conditions in your view and just show Form = "B", assuming the B form has the field you added in step 1. If that works, then you know it is just an issue with the view selection formula.

Also you can use the Document Properties to inspect document items. File > Document > Properties gets you there. I would triple-check that the documents that appear in that view do in fact have some data for the field in step 1.

Lastly, make sure the programmable name for the column in the view is unique. Double click the column header in the view designer, and then click on the last tab (beanie hat). The name that is there usually will be the same as the field you want to show in the column, or it will be a $number if the column value is a formula. You can change that name to something you know is unique just to be safe. The theory here is that if that programmatic name matches another column's programmatic name, then the view will not evaluate the column values and instead will used cached values, which in your case might be blanks. It's rare, but it does happen.

Answer 2

Unfortunately, there is no (intrinsic) "join" functionality available from a Notes view. If you absolutely need the different columns appearing in the same row (document) in a view, then one option is to de-normalize the data, such that upon saving of "Document B" you update the related "Document A" with the necessary field values. (This is also the only real way to get full-text searches to work across "joined" data).

If the view is for display on browsers only, then you may have other options, such as making AJAX calls to load the related data fields, etc.

Answer 3

There is a simpler version of the 'multiple form' trick noted by Ken:

Select Form = "A":"B" & StatusIndex < "06"

or if you prefer:

Select (Form = "A":"B") & StatusIndex < "06"

This formula says: if (form = A or B) AND StatusIndex < "06"

Note: Be sure StatusIndex is Text (as you've quoted it) and the field StatusIndex with a value is included on both forms. If not, you need to fix your logic.

Plus: Documents display in a sorted or chronological order, ONE to a line, so you cannot have A & B data on a single line. It may look like:

A
A
A
B
B
B

OR

A
B
A
B
A
B
A

But never

A & B
A & B
A & B