Solved

View column formular

Posted on 2000-03-21
9
667 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
ID: 2640550
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
ID: 2640782
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
ID: 2642880
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
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 
LVL 7

Author Comment

by:Z_Beeblebrox
ID: 2644695
And how do I "select" a document using an agent?

Zaphod.
0
 
LVL 7

Author Comment

by:Z_Beeblebrox
ID: 2644992
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
ID: 2648504
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
ID: 2648720
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
ID: 2649986
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
ID: 2654232
Don't mentioned!
I am happy to help others
besides I also earn points in return :)
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Lotus Notes and SQL Server 2008, ODBC 22 1,345
DAMO - "Overall status" hangs on 0% or 100% even though tranfer has finshed. 3 131
Missing Rules 1 301
Domino Lotus Notes Client 4 71
For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
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
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

770 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