Solved

View column formular

Posted on 2000-03-21
9
666 Views
Last Modified: 2013-12-18
Hi,

I have the following formula which works perfectly in a field in a document. However I want to use it in the column of a view, but when I try, nothing appears.

@Sum(@DbLookup( "" ; "" ; "($Per Period)" ; @Text(Year)+Office+Name ;4 ))

What is going wrong?

Thanks,

Zaphod.
0
Comment
Question by:Z_Beeblebrox
  • 4
  • 3
  • 2
9 Comments
 
LVL 1

Expert Comment

by:bennyliaw
Comment Utility
It is documented that @DbLookup as well as any other @Db commands will not work in view column formula.

What you can do is create a computed field in the form say "Sum" with the formula you want. And then refer to that field name in the view column formula.

One draw back is that the formula is only calculated when you refresh or save the document. If you by some reason need to recalculated the formula you may want to use scheduled agent to recalculate the "Sum" field.
0
 
LVL 7

Author Comment

by:Z_Beeblebrox
Comment Utility
And how can I refresh just a specific field in a specific document when a document is saved? This database will have hundreds of documents in it so refreshing all is not an option, and the data must be instantly updated.

Thanks,

Zaphod.
0
 
LVL 9

Expert Comment

by:Arunkumar
Comment Utility
Hi Zaphod,

Write an agent that runs on selected documents.

Let the code contain,

@Command([ToolsRefreshSelectedDocs])

Now, Select the docs you want to compute the values and use actions and run the agent you have created.

Good Luck !

-Arun

0
 
LVL 7

Author Comment

by:Z_Beeblebrox
Comment Utility
And how do I "select" a document using an agent?

Zaphod.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 7

Author Comment

by:Z_Beeblebrox
Comment Utility
OK, I can now refresh the specific document by opening a view to a selected document and then doing the refreshselecteddoc command in the query close event. However I have now run into two problems.

1) It beeps when it does the refresh. Is there any way to stop this?

2) I have totals enabled on the column from where I am grabbing the information, but the total displayed is consistently incorrect. If I open the view in design mode, the total is correct, and the formula which also totals the data is also correct. I have tried refreshing all documents, closing and opening Notes and a few other things, but with no success. Even if I create another document, that number is added to the total, which is still short the same amount. Any ideas?

Thanks,

Zaphod.
0
 
LVL 9

Expert Comment

by:Arunkumar
Comment Utility
Hi Zaphod,

Why do you want to use the refresh in the queryclose of the view ?

Try doing it manually by selecting the required documents and running the code from an agent...

Good Luck !

-Arun
0
 
LVL 1

Accepted Solution

by:
bennyliaw earned 20 total points
Comment Utility
I think why the refresh agent is called from queryclose is that Zaphod wants it to be triggered automatically whenever the documents is updated, because it may change the result on the @DbLookup.
But I don't understand why it beeps when refreshing the documents though. Is there any error message you see in the status bar?

Abt the inconsistency of the total, it could because the view or database corruption. Try create a new replica and see whether it fixes the problem. One other thing to check with you is that whether you have any Reader fields in the form. Even you cannot see the protected documents (because you are not in the reader field), they still constitute to the total.

I have another alternative for refresh agent. It is using lotus script instead of formula. Even though the code is a lot more longer it perform much faster than the Refresh agent.

Dim dcol as NotesDocumentCollection
Dim view as NotesView
Dim session as new NotesSession
Dim curdb as NotesDatabase
Dim doc as NotesDocument

set curdb = session.CurrentDatabase
set view = curdb.GetView( "($Per Period)" )

key = curdoc.Year(0) & curdoc.Office(0) & curdoc.Name(0)
dcol = view.GetAllDocumentsByKey( key )

Dim sum as double
sum = 0
doc = dcol.GetFirstDocument
while not doc is nothing do
   sum = sum + doc.ColumnValues( 3 ) 'fourth column value
   doc = dcol.GetNextDocument( doc )
wend

'Now you have the sum, update all affected documents
'you can select the affected documents by another GetALlDocumentsByKey
doc = affecteddocs.GetFirstDocument
while not doc is nothing do
   doc.Sum = Sum
   Call doc.Save( True, True )
   doc = affecteddocs.GetNextDocument( doc )
wend
0
 
LVL 7

Author Comment

by:Z_Beeblebrox
Comment Utility
Hi,

When I came in this morning the total problem was fixed. Hopefully it will not happen again.

As far as the refreshing thing goes, using your script as a model since it doesn't actually compile, I was able to produce the following fully functional script:

Sub Queryclose(Source As Notesuidocument, Continue As Variant)
     Dim view As NotesView
     Dim session As New NotesSession
     Dim curdb As NotesDatabase
     Dim doc As NotesDocument
     Dim curdoc As NotesDocument
     
     Set curdb = session.CurrentDatabase
     Set view = curdb.GetView( "($Per Period)" )
     
     Set curdoc = source.Document
     key = curdoc.Year(0) & curdoc.Office(0) & curdoc.Name(0)
     
     Dim sum As Double
     sum = 0
     Set doc = view.GetDocumentByKey( key )
     
     Do While curdoc.Name(0) = doc.Name(0)
          sum = sum + doc.ColumnValues( 3 ) 'fourth column value
          Set doc = view.GetNextDocument( doc )
          If ( doc Is Nothing ) Then
               Exit Do
          End If          
     Loop
     
     Set view = curdb.GetView( "($Reps)" )
     key = curdoc.Year(0) & curdoc.Name(0)
     Set doc = view.GetDocumentByKey( key )
     If doc Is Nothing Then
          Msgbox "There is no record of this person for this year in this database."
     Else
          doc.YTDProduction = Sum
          Call doc.Save( True, True )      
     End If
     
End Sub

I think the only serious problem with yours was that you tried to use a document collection when summing the values in the columns. Apparently this does not work since the view that you are referring to is not clear. As a result, I converted it to the same format as was suggested in the help file. I could also just have grabbed the field directly, but the end result is the same.

I definately prefer the script solution since it is completely invisible to the user, unlike the formula method which beeps and flashes up the progress bar.

Thanks for your help,

Zaphod.
0
 
LVL 1

Expert Comment

by:bennyliaw
Comment Utility
Don't mentioned!
I am happy to help others
besides I also earn points in return :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
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.
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

728 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