/ EVComment - Text Commentary
Provides a vehicle for submitting, storing, and retrieving text commentary in an Outlooksoft database.
Document last updated 5/22/2007 / Brought to you by PushOfAButton

EVComment Overview

Functions

EvCom

EVCGT

EvComNeedsRefresh

Menu Selections

Retrieve Comments and Data

Retrieve Comments

Submit Comments and Data

Submit Comments

Clear Excel Comment Popups

EVCom Options

EVComment Help

About EVComment

Macros

Client Setup

Web Server Setup

Troubleshooting

Client Log File - EVComClientLOG.Txt

Server Log File – CustomEVCommentLog.txt

Server Log File – EVComSvrLog.txt

Update Links

FAQ

Modification History

Most Recent Modification ↑

EVComment Overview

This document describes the installation and usage of EVComment, a custom solution that allows users to send and retrieve text commentary to the OutlookSoft database. This solution contains components on the client and server, which work in conjunction with the Outlooksoft components to retrieve comments rather than data.

Functions

The EvCom function is designed to send and retrieve comments from the database, like EVSND.

The EVCGT function retrieves comments from the database, like EVGET.

The keys for submission and retrieval are the same as those to submit and retrieve data values, so that each comment is associated with a particular current view (CVW) in the application.

The EVComNeedsRefresh function returns TRUE if the user needs to Refresh Comments, false if the comments are up-to-date for the current view.

EvCom

Function: EvCOM(CommentCell, "Application", "member1","member2","memberN")

What does it mean? Read/Write text comments to the database.

What does it do? During retrieve, it retrieves a text comment based on the specified members, and the remaining members in your current view (CVW). During send, it sends the value specified in CommentRange. If the option “Within Cell” is used, the comment is read from/written to the Comment cell. If the option “As Excel Popup” is selected, the comment is read from/written to an Excel comment associated with the Comment cell.

Parameters:

Application / Application Name
CommentCell / A range specifying comment source/destination. The comment results will appear in the current cell containing the function as a return value, and will additionally appear in this range you specify, either within the cell or in an Excel comment popup, depending on your options setting.
Member1 / The first member specified (at least 1 required)
MemberN / The last member specified

Example 1: EvCom($D11, "Budgeting","Sales","NorthAmerica","Jan.2002","Actual")

Result 1: “North American Sales exceeded expectations” is returned from the database, and cell $D1 has an Excel popup comment which says “North American Sales exceeded expectations”

Example 2: EvCom($A$1,"Budgeting", "Sales")

Result 2: “Argentina sales is down due to government unrest” is returned from the database (assuming the current view is set to Argentina

Note: Any dimension that is not specified in the formula will be taken from the [K]CurrentView, but at least one dimension must be specified.

EVCGT

Function: EVCGT("Application", "member1","member2","memberN")

What does it mean? Reads text comments from the database. This function is comparable to EVGet().

Parameters:

Application / Application Name
Member1 / The first member specified (at least 1 required)
MemberN / The last member specified

Example: EVCGT("Budgeting", "Sales","NorthAmerica","Jan.2002","Actual")

Result: “North American Sales exceeded expectations” is returned from the database, and the cell containing the EVCGT formula returns “North American Sales exceeded expectations”. No popup comments are created, and the results are not written to a separate cell destination.

Note: Any dimension that is not specified in the formula will be taken from the CurrentView, but at least one dimension must be specified.

EvComNeedsRefresh

Function: EvComNeedsRefresh()

What does it mean? It returns TRUE if the comments need refreshing, typically because you have just opened a workbook or have changed the current view (CVW) and the comments in the worksheet currently apply to the previous CVW.

What does it do? Reminds the user that they should click the RefreshComments menu option.

Parameters: none

Example 1: =IF(EvComNeedsRefresh(),”Please click the ‘EVComment \ Refresh Comments’ menu option”,”No need to refresh comments”)

Result 1: If the user has changed CVW since refreshing or sending comments, the EvComNeedsRefresh option will return TRUE, and the cell will display the message “Please click…”. If the CVW has not changed, the cell will display the “No need to …”. message.

Example 2: =IF(EvComNeedsRefresh(),”Please click the ‘EVComment \ Refresh Comments’ menu option”,””)

Result 2: If the user has changed CVW since refreshing or sending comments, the EvComNeedsRefresh option will return TRUE, and the cell will display the message “Please click…”. If the CVW has not changed, the cell will display a blank string.

See Also:

Retreive Comments menu selections

Menu Selections

When the EVComment addin is installed within Excel, there is an EVComment menu selection off the main Excel command bar. The selections here initiate submission and retrieval of comments specified in the functions.

Retrieve Comments and Data

Retrieves Outlooksoft data first, then comments are retrieved as in the “Retrieve Comments” menu option below.

Retrieve Comments

Retrieves comments to the cells containing EvCom() and EVCgt() functions. If Excel Popup option is chosen, an Excel popup comment is written to the cell specified in CommentRange; otherwise the comment is written directly to the cell specified in CommentRange

This behavior is intended to mimic that of the Everest EVSND() function, which writes results of the current view directly into the worksheet once a source reference is created with the EVSND() function.
Cells containing EvGet() and other Outlooksoft functions are not affected.

Submit Comments and Data

Sends comments as in the Submit Comments menu option below, then submits Outlooksoft data.

Submit Comments

Sends comments as specified in the cells containing EvCom() functions to the database. If the “Read From” option is set to “Within Cell”, then the comments are sent from the text in the cell specified in the 2nd parameter. If the “Read From” option is set to “As Excel Popup”, then the comments are sent from the text in the Excel Comment Popup associated with the 2nd parameter in the EVCom() function.

Clear Excel Comment Popups

This removes all the Excel comment popups in the sheet or workbook, as specified in the “Send and Refresh Schedules” option above. It does not remove the EvCom() functions, but clears the Excel popups associated with them.

EVCom Options

The Options menu item invokes the options dialog:

Workbook Read/Write options

These options allow a user to specify, at a workbook-level, where to find the source/destination for the comments. The comments are read from the spreadsheet from either the cell or the excel popup comment, and can be retrieved into the cell specified, the excel popup, or both. This option pertains only to the EVCom function as follows:

EVCom(App, CommentRange, Member1, Member2, …)

Read From (applies to Sending Comments):
  • Within Cell – The text in the cell specified in CommentRange is sent to the database
  • As Excel Popup – The text in the Excel popup associated with the range specified in CommentRange is sent to the database.

For example, If cell AA46 has the EVCOM() function and references cell L46 for the comment:

If the “Read From” option is set to Excel Popup, the comment sent to the database will be read from the excel popup associated with cell L46 (e.g. “Comment for Revenue”)

If the “Read From” option is set to “Within Cell”, the comment sent to the database will be read from cell L46 (e.g. “Revenue”).

Write To (Applies to retrieving comments):

This option specifies where you would like to see the comments retrieved from the database.

If “Within Cell” is selected, the cell data in the range CommentRange will be replaced with the comments retrieved from the database.

If “As Excel Popup” is selected, an Excel Popup will be created associated with the cell specified in CommentRange.

Client Options

The client options allow you to specify how comments will be submitted / refreshed from the server. These option settings apply to all workbooks for the current client user.

Prompt During send data (shows "Cells Sent" confirmation):

When the user selects “Submit Comments and Data” or runs the EVComSubmitCommentsAndData macro, data is submitted in one of two ways:

  • If the option is un-checked (cleared), all the data in the workbook is sent, and neither prompts nor confirmation dialogs appear.
  • If the option is checked, the user is prompted to send data from either the entire workbook or current worksheet, and the dialogs confirming number of cells sent is displayed. Note that although the user may select to send data from only the current worksheet, the comments in all worksheets are submitted regardless of the option setting.
Auto-refresh Initiated by

The Auto-refresh initiation flag tells which process should detect when EVComment needs to refresh the comments in a workbook.

The choices are:

EVCOM – Original method. This checks if the current view has changed during retrieval, and tries to refresh based on that detection.

OutlookSoft – New method available with WebExcel v4.2 SP2 (aka build 381). Using this method, OutlookSoft tells EVComment when it needs to refresh. This will allow for comments to be published using book publishing. The Client must have the appropriate supporting WebExcel code (v4.2 SP2 or later) or comments will not automatically be refreshed when the workbook opens, or current view changes.

Refresh Mode

The Refresh Mode tells how EVComment should notify the cells that they should be recalculated. These options are provided to allow the user to choose the fastest option for their environment.

The choices are:

Search/Replace – Original method. This does a search/replace of EVCOM and EVCGT. It’s simplistic but foolproof.

Calculate – New method as of EVComment 1.17. Using this method, Excel’s calculation is used to attempts to identify which comment cells require calculation. Depending on your workbooks, this method may be faster. If your cells are not being refreshed properly, switch back to Search/Replace mode.

None: - In this mode, Excel causes a refresh during its own normal calculations. In some cases, this method works well, in others it does not (data will neither be sent nor retrieved). This mode is the fastest, if it works for you, so try it.

Cache Size

The Cache Size setting is provided primarily for use with book publishing. When using EVComment, it retains a local cache of comments retrieved for quick access during re-retrieval. However, during book publishing the old comments are not needed once the current view is changed. If never purged, by the end of the book you would have all comments retrieved for each instance of each report run in memory. These comments are not needed in memory, so they should be purged. This setting allows the cache to have

No Maximum – cache is never automatically purged. To purge it, manually, you can click the “ReInitialize” button.

Small, Medium, Large – at the beginning of a refresh, the cache is checked to see how many records exist. If the number of records already existing in the cache meets or exceeds the setting, the cache will be purged before retrieval. In this way, older unused comments will be automatically removed from memory.

ReInitialize

The Reinitialize button resets the locally cached information.

It is analogous to the “Refresh from Server” option on Outlooksoft’s eTools menu.

EVComment Help

Opens this document.

About EVComment

Displays version information.

Macros

You can attach the following macros to Excel buttons to run the macros that send and receive both data and/or comments:

  • OnMnuReadCommentData – retrieves data and comments
  • OnMnuReadComment – retrieves comments
  • OnMnuWriteCommentData – sends data and comments
  • OnMnuWriteComment – sends comments

The macros which read comments will retrieve existing comments from the local cache if available. It does not go back to the server for the latest comments. If you would like to clear the local cache first, you can create a macro to do so and attach it to a button. The macro should look like this:

Sub RefreshAllCommentsFromServer()

Application.Run “EVComTerm”

Application.Run “EVComInit”

Application.Run “onMnuReadComment”

End Sub

Client Setup

  1. Run EVComClientSetup.exe

Note: If you are installing on a CITRIX or Temrinal Services client, then you should first run

change user /install

before setup, then

change user /execute

after

  1. If it doesn’t automatically show up on your Excel menu, close and restart Excel. If you still do not have the EVComments menu, Install EVComment.xla as an Addin per directions below:
  • In Excel, use Tools \ Addins \
    browse to find EVComment.xla (it will be installed to the same folder which contains your Outlooksoft WebExcel addin),
    and check EVComment on.

A new menu, EVComment should appear on the menu bar

Web Server Setup

  1. Run EVComSvrSetup.exe.
    Note: In a multi-server environment, you should install EVComSvr on your WEB server(s).
  2. In IE, navigate to the correct appset/app for which you want to create a comment table using
  3. Run this once from Internet Explorer:
    CustomEvComment.asp
    note: replace text within braces with your server and app name
    e.g.
    CustomEvComment.asp
  4. Make sure the proper appset/app is selected. Click the link to initialize the comment table.
    It will create the tabletblComment{App} in the appropriate appset.
  5. Make sure the page says "Init=OK" when complete.

Troubleshooting

Both the client and the server create log files, which can help in troubleshooting. If you have a problem which is not resolved by the FAQs, then check the log file created.

File which flags Debug on if it exists / Debug file
Client
(program files\Outlooksoft folder or wherever client program is installed) / K2ClientDebug.log / Program files\Outlooksoft CPM\ Userid\
EVComClientLog.txt
Server
(Websrvr\bin folder) / K2ServerDebug.log / Websrvr\Bin\EVComSvrLog.txt
Server (less-detailed version) / Always written / User’s Private Publications folder
CustomEVCommentLog.txt

Client Log File - EVComClientLOG.Txt

Should be in \Program Files\Everest (or wherever you installed EVComment)

If you create an empty file called K2ClientDebug.log there, the EVCom will create a subfolder with the current userid and append to the EVComClientLog.txt file.

Server Log File – CustomEVCommentLog.txt

The CustomEVCommentLog.txt

Can be found in the

\Everest\Webfolders\Appset\App\PrivatePublications\Userid folder

Server Log File – EVComSvrLog.txt

For more detailed information, if you create an empty file called K2ServerDebug.log in the \Everest\Websrvr\bin folder, EVCom will create and append to EVComSvrLog.txt file in that same folder.

Make sure you remove or rename the K2ServerDebug.log file as the debug file is continually appended and can get quite large

Update Links

If you are constantly prompted to update links in your template, open the template and select Edit \ Links from the Excel menu.

Click the “Startup Prompt” button.

Select “Don’t display the alert and update links, and save the template. As of EVComment v1.17, the links should update automatically.

Prior to EVComment 1.17, the following description applies:

If someone creating the template has their EVComment.xla in a different path than another user, the path to EVComment.xla can become embedded in the workbook functions, and buttons which link to EVComment macros.
For example, if the author’s path is

C:\program files\everest

And the user’s path is

D:\program files\everest

Then the user will be prompted to update the links.

This can be seen but turning EVComment off (tools \ addins – un-check EVComment), then open the workbook. GO to a cell with EVComment and you will see the path. Excel is saving the path there automatically, so we need to remove the path. I have provided a utility to remove the paths in multiple templates, RemoveEVComPaths.

RemoveEVComPaths.xls

As of version 1.16, a utility workbook, RemoveEVComPaths.xls, is supplied to automate that process. It should be run after you make a change to a template, so all users with EVCOM can open the template cleanly without being prompted for links.

Steps:

  1. Backup current templates which exhibit this behavior
  2. Open the RemoveEVComPaths.xls workbook (also installed where EVComments is installed, default directory is program files\Everest). Click the “Go” button and select the templates which have these links. The code will open each of the selected templates and attempt to remove the paths, then save the workbooks

FAQ

  • Comments are not refreshing into the source cells
  • Check the EVCom Options – make sure “write to cell” is clicked on
  • After changing CVW, you must refresh comments in order to get them to appear in the source cell
  • Comments are not refreshing when opening the workbook, or comments are continually refreshing and “hanging” the machine.
  • If eTools \ Workbook Options has “Recalc on open” or “Expand on open” checked, then EVComments will try to refresh after a slight delay. Depending on your network latency, this delay may not be proper for your environment. If opened via EVHOT() function, events are disabled and the refresh will not be automatic. It is recommended that you put a button on the sheet, and assign it to the OnMnuReadCommentData macro
  • Comments are not being published during book publishing
  • If your WebExcel version is prior to v4.2 SP2, use the EVCGT() function in your book report templates, not EVCOM(). If your WebExcel version is v4.2 SP2 or later, you should set your EVCOM option for “Auto Refresh Initiated by” to “OutlookSoft”
  • Are there macros available to send and refresh comments
  • Yes, see the “Macros” section of this document
  • Can you write to parent values?
  • Yes
  • Someone else sent a comment, but I do not see it on my computer
  • EVComments uses a local cache for performance. It checks the cache before hitting the server to retrieve the values. If you have had WebExcel opened while other people are sending comments, you will not see them if you have already pulled the same current view, until you either
    Exit Excel or run eComment \ EVCom Options \ ReInitialize
  • If I send a comment, and my neighbor sends a comment to the same intersection, which one is saved?
  • He who saves last saves best
  • I get a protect /unprotect error – invalid password
  • EVComment’s protection is simplistic. It assumes that each sheet in the workbook has the same password, as indicated in the eTools \ Workbook options. You cannot have varying passwords on each sheet. That is, all sheets must have the same password.
  • I’m retrieving 4000 comments and it’s very slow. How can I improve performance?
  • If the performance is related to retrieval, you can try putting an index on the sKey column of the table, and try deleting blank records. If the performance problem is on writing comments, then adding an index will make it worse.
  • Try changing the EVCom options from RefreshMode = “Search/Replace” to “Calculate”
  • I have a very large workbook (many sheets) but only a few comments. It seems slow to calculate comments before actually requesting them from the server. What is going on?
  • The addin is searching all the sheets in the active workbook for EVCOM and EVCGT. If there are many possible cells, this could take a while. Excel will look for the last cell used in each sheet. To find that cell, in Excel’s menus select Edit \ Go To \ Special (button) \ Last Cell (option button). If it is way beyond your data area you should delete the extra cells. View \ Zoom \25% on each sheet to see if there are any extraneous values or formats in the sheet beyond where you normally look (remnants of old functions, etc). Delete those cells. If that is not enough, try to separate your comments to another workbook.
  • Alternatively, try using the EVCom option RefreshMode = “Calculate” rather than Search/Replace.
  • Excel keeps asking me to update links to EVComment.xla. How can I stop this?
  • Read the section on “UpdateLinks”
  • I get the following error message when trying to send:

Error During Compress