logo

Towards the Perfect Web View

Well, that's an ambitious title for an article. It is what we would like to write for you though. Views are one of the two main design elements in Notes, the other being forms. There are many tips for both of them, but they are scattered over various websites. In this one article we would like to describe how to:

  • Create lightweight HTML views
  • Make the columns sortable
  • Make them exportable to Excel

The larger the views, the larger the benefits of the above features. But designing all views in your database this way makes your views load faster for users and easier to maintain for yourself. As an example database we use a simplified version of Names.nsf, a contact manager, which is attached at the end of the article. If you have more tips, please include them in your feedback.

You can see a demo of all this functionality in this live database.

Lightweight HTML Views

Native Notes webviews are effective, but they consume a lot of bandwith. Notes creates HTML tables for views with rows that contain more characters than needed. A typical row in a Notes generated view could be (adapted from Names.nsf contacts view):

<tr valign="top"><td><font size="2" face="Arial"><a href="/names.nsf/85255e01001356a8852554c200753106/
79903c820b4d95fec1256e110000a759?OpenDocument">Jake Howlett</a>
</font></td><td><font size="2" face="Arial">jake@codestore.spam</font></td><td><font size="2" face="Arial">Office phone: +44 (1) 71</font></td><td><font size="2" face="Arial">Codestore - CEO</font></td></tr>


The light version of this row could look like this, about 1/3 of the code Notes uses.

<tr><td><a href="0/79903c820b4d95fec1256e110000a759">Jake Howlett</a><td>jake@codestore.spam<td>Office phone: +44 (1) 71<td>Codestore - CEO


So how to get from the Notes generated view to a lightweight view?

1. CSS

The greatest improvement can be achieved by using CSS. All the yellow marked code are describing the style and can be replaced by:

TD {
font-size : 11 px;
font-family : arial;
}

You can see in the attached sample database how to connect a style sheet to the view.

2. Using COL

Using COLs you can set a variety of properties, such as the alignment of columns. We haven’t found a nicer way to align columns yet, adding code in the table like <col align="right"> is an easy way to align columns with numbers. Read more about it in this article.

3. Eliminate unnecessary html

In a table the start tags <tr><td> are required according to W3C, but the closing tags are optional, so skipping </td></tr> saves bandwith as well programming time.

4. Shortening the URL

Now we have shortened everything besides the actual url opening the document.. It is quite a long url. By adding <base href="http://www.yourdomain.com/yourdb.nsf/"> in the header of your pages, (e.g. in the $$html field), there is no need for the name of the database in the urls anymore. All urls are opened from the base you defined. Read more in Jake’s article on this.

Documents can be opened by their unique ID. Since this is unique, it is independent of which view the document is in. Notes will open the right document, no matter what view name you use. So even without an actual view named “0”, the view id in the url “85255e01001356a8852554c200753106” can be replaced by “0” or any other figure or alpha numeric combination. Read more on Notestips.

Skip the ?opendocument in the url. Notes simply opens a document if there are no commands after the question mark. Mike Golding has written about it here.

Rumour has it that skipping the ?opendocument might open the document slightly slower. We have not experienced it at all. Anyhow, most users will prefer to open a view faster and then open documents slightly slower from that view.

5. Minimizing calculations in views

Great tips for this can be found in the redbook “Performance Considerations for Domino Applications”.

Let us mention two view related tips from there:

Calculated data is not stored as part of the summary information, but instead is calculated each time the view is accessed. Read More...

When you do have to do complex calculations to display in a column, it is often better to add an extra field to the form that contains the result of the calculations. The calculations happen only when documents are changed and the view suffers no performance impact. In the attached demo database, we have added a hidden Field called URL, that calculates the url to the document.

@If((NameDisplayPref = "1" | NameDisplayPref = 1 | @IsUnavailable(NameDisplayPref)) & (LastName !="" | FirstName != "");
@If(FirstName = "";@Trim(@Subset(LastName;1));@Trim(@Subset(FirstName;1)) + " " +
@Trim(@Subset(LastName;1)));
NameDisplayPref="2" | NameDisplayPref = 2 & (LastName!="" | FirstName !="");
@Trim(@Subset(LastName;1)) + @If(FirstName!="";" , "+@Trim(@Subset(FirstName;1));"");
CompanyName)


This formula could easily be added to the form as a field called e.g. DisplayName.

Place all constants in separate columns. For example place the opening “<tr><td>” tags in the first column. These constants are generated extremely fast as they don’t become part of the view’s index. You can see this for yourself when you open a view in Designer. The columns with the constants are rendered before the columns with the fields or formulas.

6. Minimizing calculations of views

Each time a document is modified, all related view indexes are recalculated. In the case of a database containing a large company directory, say 10.000 people, changing one document may make all related views open in seconds instead of in hundreds of a second. If all the earlier recommendations didn’t speed up the views enough, the last resort may be to update the view’s index less often. You can set this at the View Options at the Propelerhead tab. Read More...

7. Validate your html

Internet Explorer and, to a lesser extent, other browser will correct any errors in your html, but it takes processing time to do so. You will be amazed how many small errors you have created when you validate your HTML with this free online tool http://validator.w3.org. Correct any errors you can find and your page will load that little bit faster.

Sortable Columns

Many developers have done their best to create the sorting functionality that the Notes Clients offers in it views. We have evaluated many of these javascript routines and have found only one that is close to perfection. It is bunch of brand new JavaScript functions created by Erik Arvidsson of WebFX. Why is it better than others? It is stable, it is easy to implement, it is well documented, it works with different browsers and it uses the fastest sorting algorythms for each browser. Other code we tested was often slow on large numbers of rows.

You can check his site for the full documentation and the unaltered code. We will only discuss the changes we have made to his code. Indifferent of the changes we have made, Erik’s code and our adapted code can be used in the same way as shown in the example database.

We have added one line to his code, of which we informed Erik. This line makes it possible to also sort columns containing numbers with delimiters (dots and comma’s), by removing them from the sort method with sText = sText.replace(/[\.,]/g,'')

Erik’s script is written to also do the initial sort of a view. But Notes views can already be sorted, so having Erik’s script do the initial sort only slows the opening of the view. To avoid this, we have created the rather inelegant solution of adding a fifth sort type (next to Date, Number etc) called “Nothing”. It doesn’t do anything, except display a down triangle image next to the column by which Notes initially sorted the view.

Make views exportable to Excel

This is done by using simple sandard functionality, that we only discovered after years of using Excel. Most export methods make use of CSV, comma separated files, that usually require some extra user interaction before opening properly in Excel. But Excel is able to open a special kind of file called query files. Read More...

These are simple plain-text files. The file has the extension “.iqy” and would contain somthine like this:
WEB
1
http://www.codestore.net/apps/perfview.nsf/ContactsExportToExcel
Selection=AllTables
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
This file simply contains settings that you can manually add in Excel to a worksheet by selecting Data > Get External Data > New Web Query.



Just place this file somewhere in your database and create a link to it near the table you want to export.

If you have more tables on a HTML page it is possible to import just one table by stating it’s ID. You could also create special views for these exports with simple viewtemplates only containing one table. In the example database, these special views not only contain the contents of the table in the HTML page, but also all the other fields that users may be interested in. E.g. in a Company Directory your HTML view would just show a couple of fields as user, email address and telephone number, but in the Excel file you could also put fields like Position, Date of Birth etc. etc. Thus you leave it up to the user to select the information he wants in the Excel file.

A nice feature of Excel is that you can place complete URLs in to cells, linking back to the original document in your web-enabled database. So if users want to see the picture of a person, or download a CV of that person in Word format for example, they can simply click on the URL from Excel.

Conclusion

We hope this article can be of help for you when designing views. You can download an example database below (Notes 6 only. If you don’t have a Notes 6 Designer, you can download a fully functional trial version here.

Please feel free to add your feedback. We hope that one of you can write the “Towards the perfect form” article.

Your Authors

Article written by Redouan and Laurens, with some slight editing from Jake.

Feedback

    • avatar
    • Mark Gottschling
    • Thu 11 Mar 2004

    4. Shorting the URL ...

    You can shorten the url better by creating a real view with a short name ("0", "unid", etc.) and assign a new key\id to each document by the @Unique function. This way you're using an 11-digit id instead of a 32-digit id. You can see Jake already does this in the URL of this article.

    1. Re: 4. Shorting the URL ...

      There are two drawbacks to that idea. The first is that the document is returned by using the equivalent of GetDocumentByKey, which is slower and more "expensive" than GetDocumentByUNID, so it might not be appropriate for high-volume applications. The second is also that the document id returned by the equivalent of GetDocumentByKey. It may seem absurdly obvious, but a document actually has to appear in a view in order to use a key value. It doesn't have to appear in the view in order to use the UNID. Why would that matter? Two words -- FORM FORMULAS.

      It is not at all uncommon in a Domino web application to make extensive use of form formulas in order to restrict access to data. Who has access to what data is determined by security settings on views that have form formulas. A user may have access to a document with a unid like 467CC5B0001BF79A86256E54004A77F0, and can read the document using this URL:

      http://www.codestore.net/comments/467CC5B0001BF79A86256E54004A77F0

      The same user would not have access to the same document using this URL:

      http://www.codestore.net/admin/467CC5B0001BF79A86256E54004A77F0

      When you use a technique like that, you need to have the view that is used in the URL in order to use its form formula -- but that view does not have to contain even a single document if you use the UNID. If you use the key, the document must, of course, appear in the view.

    2. @Unique is dangerous in web applications

      According to Developer Help (at least for R5) @Unique is not supported for web applications. I'm not sure why but I suspect it has something to do with the fact that the server is standing in for the user in some situations. We have been able to create situations where @unique yielded non unique keys. (As far as I can make out the version with a parameter - which has a confusingly different meaning - works just fine on the web)

      • avatar
      • laurens
      • Fri 12 Mar 2004

      Re: 4. Shorting the URL ...

      Shortening the url with @unique has above mentionede disadvantages. Problems people have with them are described in the R4 & 5 forum on Notes.net. But building further on your thoughts, creating one's own unique number may be more reliable.

      This formula may decrease the change of assigning one "unique" number to more than one document. Add a computed when composed field called UNID to the form with the following formula:

      all_chars := "A":"B":"C":"D":"E":"F":"G":"H":"I":"J":"K":"L":"M":"N":"O":"P":"Q":"R":"S":"T": "U":"V":"W":"X":"Y":"Z":"1":"2":"3":"4":"5":"6":"7":"8":"9":"0"; @For(n := 1;n <= 8; n := n+1; FIELD UNID := @If(n = 1;@Subset(@Subset(all_chars; @Round(35 * @Random) + 1); -1);UNID + @Subset(@Subset(all_chars; @Round(35 * @Random) + 1); -1))); UNID

      This formula creates 2.821.109.907.456 (unique) numbers. Depending on the expected number of documents in your database and the risk you want to take, you can in or decrease the number "8" in the formula to get a longer or shorter alphanumeric string.

    • avatar
    • Johan Känngård
    • Thu 11 Mar 2004

    End of cell/row tag

    Note, that when using XHTML, the end tags /td and /tr are required. Non-empty elements must include end tags.

    - Johan, http://dev.kanngard.net

    • avatar
    • Jerrith
    • Thu 11 Mar 2004

    Column sorting

    Just FYI: the sortable headers don't work in your little demo database. I can't even click the headers :-)

      • avatar
      • Stephen Neal
      • Thu 11 Mar 2004

      Re: Column sorting

      It works here... I'm running IE5 under Windows NT

      Show the rest of this thread

      • avatar
      • Steve
      • Thu 11 Mar 2004

      Re: Column sorting

      This article has some very good ideas and concepts. I will likely be using them in my work in the future.

      But I am concerned about real world application of some of these concepts:

      1. Sortable Columns - This will only sort data on the current screen. Many Domino applications have hundreds (if not thousands) of documents. In order for the sorting to work the table would need to include all the documents. In the real world, it often takes too long to generate and download a single view page that shows all the documents. Thus the use of pages with limited number of documents is necessary (using the next/previous concept). When you do the client side column sorting in that environment, you are only sorting a portion of the data when the user expects to be sorting all the data.

      2. Minimizing calculations in views - this is a good idea to keep the views running fast. But in some cases pre-calculating causes other problems. For instance, in your example you pre-calculate the URL on the Contact Form. If this application was both a Notes and Web application a user could easily copy and paste a document (or if this application had a web method of copying documents). If that happens, you end up with a view entry that will open the wrong document (new doc has URL field calculated on the old doc). You could argue that the person who copied and pasted the document would normally edit and save the new document thus recalculating the URL. But if that user leaves the new copy untouched and edits the original document not the new document, you still have a view entry pointing to the wrong document.

      3. Exporting to Excel - While this works on a Internet application with no security, it has a major problem on a Intranet that is secured. Excel doesn't appear to properly get or use security information from the Browser window (cookies), thus requiring the user to Log in again to retrieve the page. I have seen with some SSO add-ons that the view data can never be retrieved in Excel since Excel is not a browser and doesn't act appropriately.

      4. Categorized Views - The methods described here don't take into consideration that many applications have views that are categorized. A solution might be to use a Single Category View with a choice list to change the category but that gets tough when the view has more then one level of categorization.

      Show the rest of this thread

      • avatar
      • Sean
      • Fri 17 Dec 2004

      Re: Column sorting

      When using the sortable columns on a large view my users tend to re-click the header before the results are returned. Any ideas on how to implement a "Loading please wait.." message that can be displayed in conjucntion with the sort function. I already have one running on the page load event, but since the column sort does not refresh the page that doesn't help, thanks..

    • avatar
    • Val
    • Thu 11 Mar 2004

    Sorted

    Guys,

    Do you know of a good way to retain the column sort in the view after opening a document?

    I use the older verion I was thinking of using a cookie but Iwas wondering if you had any other or better ideas ?

    Regards,

    Val.

      • avatar
      • Val
      • Thu 11 Mar 2004

      Re: Sorted

      Sorry, I meant to add that I use the first version of the table sort in my application.

  1. MSDN Link to web queries

    Combined with the link already in the article, this can be a very usefull article:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_xl2003_ta/h tml/odc_xlwebquery.asp

  2. Well done.

    Nice article, gents. It's very succinct, which I think a lot of us who spend too much time reading blogs appreciate.

    Cheers!

    • avatar
    • Mer
    • Thu 11 Mar 2004

    I'm missing Notes 6 Designer!

    ..I do have Domino Designer though..

  3. Nice ideas!

    I like some of the ideas, I use a few similar ones myself.

    A few other options people might want to consider...

    1. Using computed text and dbcolumns to retrieve computed HTML from documents and nest this inside passthru html tables on a form or page. As mentioned, computed values need to be recalculated if copy/paste. Just use a paste agent to solve that one.

    2. Create a view that shows the data as xml and treat this as an xml data island by using a lookup/dbcolumn etc... You can play around with the data, binding it to HTML tables and paging through the data for example.

    3. If you have lots of data you'll probably hit a text limit when a dbcolumn/lookup is used or you just might hit the maximun size of text/computed text for a document. In which case you can use a WebQueryOpen agent to write HTML to a computed RTF.

    4. The other idea I use sometimes is just using an agent to build a view like page, based on the results of a search or similar. Obviouslly there are possible performance issues when doing this, so take into account the size of the database, number of users etc...

    I like the Excel export idea, will be playing with this soon I should think! I have used the previous Excel export method Jake posted a few times now.

    Also, using CSS with views is a great idea, I use this frequently and not only is the download smaller, it's much easier to change the look of a whole load of views in one go by storing the CSS as a page or attachment (whatever takes your fancy)

  4. Can't find version for non-Notes 6 Designer users

    Is it in the Sandbox? if so where?

    Ta v. much

    1. Re: Can't find version for non-Notes 6 Designer us

      Sandbox??? This ain't LDD.

      One of the elements in the database (the .iqy "file") requires Domino 6 because of the new "Treat content as other" feature available for pages and forms. If that's not an issue, then I can send you an R5 copy if you write me at stan[dot]rogers[at]cgi[dot]com.

      Show the rest of this thread

      • avatar
      • laurens
      • Mon 22 Mar 2004

      Re: Can't find version for non-Notes 6 Designer users

      Stephen's solution is really nice for R5. If you are not to familiar with agents, you can also just create a file in Notepad, with the same contents as shown in the article. The file must have the extension .iqy. Place this file somewhere on a page or form or where-ever in your database. Create a link to the file with <a href=.... etc.

  5. My Dynamic View Builder

    Here is a link to a sample database which uses XML data Islands to allow the End User to build a view via Internet Explorer. It features Dynamic number of columns and their Field Values.

    http://free.corefusion.net/Free/wausau/home.nsf/dvb?OpenForm

    Any ideas about how to handle categories are welcome.

    I would like to credit Mike Golding and Jack Ratcliff for my inspiration.

    P.S. I also have a idea about a dynamic view builder that doesn't use data islands.

  6. Superb

    This is excellent. Only the other day was I asked to sort columns in my HTML views and I had to say no.

    Thank you!

  7. SSL

    Hi All,

    I have used the principles displayed in the perfview.nsf database and it works like a dream on my development box...however Im not having much luck on the secure enviroment. Keep getting the error "Unable to open HTTPS://... Cannot download the information you requested"

    Any ideas how I can resolve this issue??

    Thanks

    1. Re: SSL

      I stumbled upon this method of exporting to excel on the web and thought it was so elegant in its simplicity. Unfortunately, I found out the hard way that web queries apparently do NOT work with SSL. You can read about it here: http://support.microsoft.com/kb/290347/en-us

  8. Why add HTML to the view?

    Adding HTML to the view column or the form field to be looked up in a view seems a waste of bandwidth and encrouches on the 64k lookup limit if you are doing dblookups (and its still there in v6 apparently) so why add the HTML?

    I find that packing a string with delimiters in the field (column) means that you can look it up faster and add the html later using @formula.

    Something like:

    unid~Title~Date(text)

    then for an anchor tag in a table-cell:

    "<TD><A href=/" + @word(List; "~"; 1) + ">text</A></TD>"

    Al the static text gets added after and helps separate out data from presentation.

    I accept that doing "plain" HTML view embeds cant use this technique, but I find that I have a lot less control with view embeds.

    Just my 2pence worth - cheers - Tim.

  9. Fetching as required

    I've been experimenting with ways of loading a view in as required. This is how it currently 'sort of' works....

    The 1st 30 entries of a view are shown to a user, as the user scrolls/moves down this view, the position within this view is tracked, if the user is going to reach the bottom of these entries the next 30 are loaded in dynamically via an IFrame.

    To put a bit more detail behind it, I should mention that the view is a standard HTML view as generated by Domino, that is wrapped in an 'auto' overflow DIV that generates the scrollbars.

    Whilst it all works fine if the user gently clicks to scroll down, everything goes pear shaped if the user drags the scrollbar.

    Anybody else working on this, or something similar, I could do with some help ?

  10. Applications Secured with Session Authentication

    I tried to implement this in a database secured from anonymous access & was only able to open a table from the login page. Is there a work-around that I'm missing?

  11. Awesome...

    Excellent article...however it would be even better if the export would coincide with the sort that you have chosen....

    Thanks again for a great article! Ger

  12. On Disk Structure

    I'm assuming this is in R6 format since I cannot open in my R5 client. Does anyone have a version that will open in R5?

      • avatar
      • Jake Howlett
      • Fri 16 Apr 2004

      Re: On Disk Structure

      Eric. Most of the NSFs I upload now are in R6 format. It's getting to the point where this is the "standard". If you really want to open it then the easiest way is to download yourself a copy of the R6 Designer client.

      Jake

  13. Problems using 0 as your view name in the URL

    We've had problems using 0 as your view name and not having that view in the database. Our problems was a user would click on the link with the 0 as the view name and they would lose their authentication. We would use the same link with an existing view and everything would work fine. Has anyone else seen this problem? I would suggest maybe having a very short named view that exists.

    • avatar
    • zomek
    • Mon 26 Apr 2004

    What about internationalization?

    Unfortunately, this JavaScript method of sorting doesn't work properly with non Western European characters. I'm not sure if even switching between zilions of combination of OS/browser's locale settings can get JavaScript "greater than" operator working. And writing your own JavaScript method of comparing strings can be painfull... So maybe other-than-english speaking developers have to stick to server side sorting.

  14. URL stored in form

    Great article, I've referenced it about 3 or 4 times now in performing my daily duties.

    A thought though on point number 5.

    Adding fields to the form to calculate some of the information is a great idea, especially if all of the information in that field pertains ONLY to the document.

    With a URL, you're taking now the server name and the file path / db file name and storing it, at calculation time, into the document.

    As soon as the database is migrated to another server, you'll need to refresh all of those documents. You can either bear that in mind and build in a tool for it, or you can simply leave the URL code up in the view column, where it will be freshly calculated each time.

    Either approach has it's advantages, but it seems to me that a good rule of thumb is only calculate and store those things the document is accountable for in its own right. Information external to that should likely be considered volatile.

    Cheers,

    Jerry

      • avatar
      • laurens
      • Thu 6 May 2004

      Re: URL stored in form

      Thank you for your compliments Jerry.

      With the url, you can store the RELATIVE url in the document. To open the url you can set a base href as described in Jakes article: http://www.codestore.info/store.nsf/0/3B7BCAAA8F3EF10280256AD0006BBD84. Not just on the web this works, but also on intranets. For some or the other reasons I seldom see developers use subdomains in intranets, and thus easy to remember urls. E.g. a contract database could easily be called: contracts.companyname.com as an internal url. The database could be called "Contracts" and the base href could be http://contracts.companyname.com.

  15. Using CSS instead of tables in view

    We're looking to use CSS to build views (particularly when embedded in forms) to show data rather than using the tables views are usually rendered with. Why? Tables that are not used for tabular information, ie only used to make it look good on the screen, are not hot in DDA compliancy land (see http://www.w3.org/TR/WAI-WEBCONTENT/wai-pageauth.html#gl-table-markup).

    So- we are using views that display in the web in HTML and use CSS to format them nicely without tables. Is anyone else doing this sort of thing?

    Something to think about...

    Caroline

      • avatar
      • Jake
      • Thu 6 May 2004

      Re: Using CSS instead of tables in view

      Hi Caroline,

      I think there's lots of using CSS with views. This site uses the technique extensively. Is there something specific you were wondering about?

      Jake

      Show the rest of this thread

    1. Re: Using CSS instead of tables in view

      The dynamic view builder works fine except it does not display more than 30 docs in the view template ...It is limited to the standard lotus 30 docs in a view...My guess is some is missing in the navigation function of the sample database provided by Butcher....please do advise

    2. Re: Using CSS instead of tables in view

      tables are for tabular data.

      notes views can be displayed excellent as tabular data.

      so no use to use lots of css here for displaying purposes. just a waste of energy (read time)...

  16. Storing images through CSS

    I have a table where all the cells are images. I use the same table with the same images on a number of my forms. When loading, the image always has to reload which makes the loading of the page slow. Is there a way to store the images in CSS and call it on the form?

    1. Re: Storing images through CSS

      I have looked over the sample database, and have built a test database of my own. I have the links working, but not the sorting any ideas? Do you have to modify the java script for a new view?

      Show the rest of this thread

      • avatar
      • laurens
      • Tue 3 Aug 2004

      use website rule expiration header instead

      Dear Sonny,

      Positioning some images through CSS is fine, positioning many is from a designing point view quite cumbersome.

      Never thought of using this sort-javascript on images. It is strange that the images reload, the javascript to sort the table doesn't call for a reload of the page, it only sort the current html page.

      One solution may be the use of website-rules. Through website rules you can set the browser caching of pictures and the like. http://www.lotus.com/ldd/today.nsf/0/2aa6ecf8f295019085256b9c006fd147

      This will make your whole website faster, as you can set the expiration header for all the images.

      laurens

  17. Excel export

    After spending way too much time trying out various example agents for exporting to Excel, I somehow ended up here.

    This solution is just way too easy -- even using standard Notes/web views.

    Amd I almost missed it because it's just a sub-section of the main article.

    Thanks again, Jake. It's stuff like this that helps make Domino development so much fun.

      • avatar
      • Roopa
      • Mon 23 Aug 2004

      Re: Excel export

      I keep getting a authentication screen in the excel worksheet. The link you have mentioned does open up excel but it seems to require a authentication. If the authentication prompt appeared at the link level it would have been fine but it's no good when the screen appears in the worksheet. Is there something I am missing. Please help.

      Thank you Roopa

      Show the rest of this thread

    • avatar
    • laurens
    • Sat 25 Sep 2004

    another way of sorting views

    I have to admit the following link provides a good way of sorting views as well:

    http://annodomino.nl/archive/20040915-1059?OpenDocument&count=-1

    Two disadvantages to this approach: - user has to reload the page in order to sort - the history of visited pages gets longer

    • avatar
    • Lucy
    • Tue 19 Oct 2004

    Web Query (.iqy)

    Help me please...I'm trying to use web query to export a web view to Excel following the instructions of this article and looking the example database...but every time that I launch the query it says "No documents found" even if my view is not empty. Have you got any suggestions? Thanks

      • avatar
      • lucia
      • Wed 20 Oct 2004

      Re: Web Query (.iqy)

      Thank you laurens I was sending you my database when I realized that the problem was in the ACL. So now the problem is solved....you give me the inspiration so... thank you very much Ciao Lucia

      Show the rest of this thread

      • avatar
      • Onur
      • Fri 19 Jan 2007

      Authentication Web Query (.iqy)

      Help me please...I'm trying to use web query to export a web view to Excel following the instructions of this article and looking the example database...but every time that I launch the query gets our entry page Which is user name and password screen even if my database's acl has anonymous. I think this problem is about ssl. Have you got any suggestions?Thanks

  18. "Hide details" for Categorized view column

    Running Notes R6 server.

    For a view with categorized column(s) the excel web query adds the text "Hide details" to each line which corresponds to the Notes categorized column.

    Can this be filtered out by a .iqy file parameter?

    thanks, Glenn

    1. ACL setting for export

      Is the a way to get the export to work without giving access to anonymous?

  19. Problems with Sorting

    Hi,

    I'm very happy with the methods described in the Article, they give me much more possiblities in designing Web Views and I don't have stupid problems with the standard Java-Applets.

    But I have some problems with the sorting. I used an R5 Designer, so I had to built in CSS and the JavaScripts in a different way. For the CSS I used Pages which treat the content as HTML, the JavaScript I put in the JS-Header of the Mask for my HTML-View.

    The Problem is, when I open the View in Browser, the sorting is not correct. The potential first Document appears at the end of the View, the second and the third Document are twisted. The rest of the View is sorted correct.

    When I sort the view manually two times, the Documents are sorted correct.

    Is this a Problem with an R5-Environment or is there a chance to fix it ?

      • avatar
      • Jake Howlett
      • Thu 10 Mar 2005

      Re: Problems with Sorting

      Hi,

      This was a problem with the way the .sort() function was being called and I've fixed it in v1.2 and the live demo.

      The third parameter being passed was "Nothing". It should either have been "None" or null or a valid sort-type. I changed it to the sort-type of the first column and it works now.

      Jake

      Show the rest of this thread

    • avatar
    • Jake Howlett
    • Thu 27 Jan 2005

    How to work it when Anonymous has no access

    See this blog:

    http://www.codestore.net/store.nsf/unid/BLOG-20050128

    1. Re: How to work it when Anonymous has no access

      How about creating the iqy text file on the fly with an agent - and get them to save it to local machine..

      then prompt the user for their password (username is already available).

      I already build my view on the fly with an agent. So for me the url line would be ...

      http://<host>/<DB>/<agent>?openagent&username=<username>&password=<pass>

      you then prompt them that the file should be deleted after use blah blah own responsability...

      then you can get them to use excel to open the iqy file locally....

      (hack on top of a hack on top... ) not very clean I agree - but excel mac and excel 2000 doesnt like my SSO setup.

  20. Unable to use "Export To Excel" action

    hi all,

    I am not able to use the Export to Excel functionality, when i trigger the action it is just opening form "Contacts.iqy?ReadForm" form in read mode, but it suppose to be launch the excel to object. If i run same action in a online version it si working fine.

    i am using 1.2 database and my server is 5.0.2

    Regards Sadhik

      • avatar
      • Jake Howlett
      • Wed 13 Apr 2005

      Re: Unable to use

      R5 doesn't let you set the content-type for the page/form. This is probably the problem. You need R6 to use the form. Otherwise you'll need an Agent and use the Print "Content-type: XXX" trick.

      Show the rest of this thread

    • avatar
    • larry lizzard
    • Tue 26 Apr 2005

    Insufficient memory - thread local heap is full

    Hi Jake,

    When I try to use the Export to Excel on large views, I get the following error:

    Error 500 HTTP Web Server: Lotus Notes Exception - Insufficient memory - thread local heap is full

    In the Help file of 6.0.5 it says:

    "JavaMaxHeapSize Syntax: JavaMaxHeapSize=number of bytes Description: Specifies the maximum--not initial--size the Java heap can reach. The Java Virtual Machine (JVM) starts out at 16MB of heap space and most of it is uncommitted. If the JVM needs more heap than it currently has, it will expand the heap in increments but will not exceed the maximum. Exceptions such as "java.lang.OutOfMemoryError" indicate that a heap has reached its maximum size..."

    I have put this on 1Gb (having 4Gb of memory available, W2K server), but to no avail. Do you know what to do?

    Larry

      • avatar
      • Jake Howlett
      • Tue 26 Apr 2005

      Re: Insufficient memory - thread local heap is ful

      No idea. Sorry Larry.

      Hide the rest of this thread

      1. Re: Insufficient memory - thread local heap is ful

        Jake / Guys,

        Having exactly the same problem here - anyone find a fix for this?

        Thanks

        Chris

          • avatar
          • walt
          • Tue 11 Aug 2009

          Re: Insufficient memory - thread local heap is ful

          xp - insufficient memory - heap - see: http://linove.blogspot.com/

          fix as suggested by linove.blogspot.com:

          "

          HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\SubSystems\Windows

          We will see in “windows” contains long string

          %SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows SharedSection=1024,3072,512 Windows=On SubSystemType=Windows ServerDll=basesrv,1 ServerDll=winsrv:UserServerDllInitialization,3 ServerDll=winsrv:ConServerDllInitialization,2 ProfileControl=Off MaxRequestThreads=16

          On 32 bit, recommend to replace with these value

          SharedSection=1024,4096,2048

          On 64 bit, recommend to replace with these value

          SharedSection=1024,20480,2048

          "

  21. To change order of documents manually, possible?

    Hi,

    Good article, it's clear, even for me which am not very good in English. That gives to me some ideas to improve my developments.

    Thank you.

    I'll like to know if it's possible to change manually documents order (Web), I explain me :

    Let us imagine three documents in view : A B C

    I'll like to take the document B and to put it Before document A (Drag and Drop)

    Like this : B A C

    and naturally, the order of this document will be really changed in Notes view...

    Any ideas?

    Thank you,

    Thierry

    • avatar
    • Nick S. Karetnikov
    • Sat 7 May 2005

    a bit futher...

    Hi Jake! This is a great job you are doing on this site. Thank you! I've tried with my little knowledge improve Excel export technique. Towards a bit more flexible export of every view I have on web. With $$ViewTemplateDefault form and @ViewTitle formula we can pass to the .iqy form which view we're working on. That's good - write code once and it works with different views, but when the thing comes to $$SearchTemplateDefault - 2 questions arise: 1. How to pass the name of the searched view to .iqy form 2. How force Excel to get data from the filtered view. In other words - how to force Excel to do fulltext searching again. The first question addressed this line here http://www-10.lotus.com/ldd/nd6forum.nsf/DateAllThreadedweb/6479565380addede8525 6ffa002fed97?OpenDocument if no answer is still there - here is kind of the one. I use viewname parameter when do a search and parse it with @urlquerystring and pass then into .iqy You are still with annoying me? :) Then let's continue... This gives us ability to read from an original view but Excel knows nothing about search result we've already got, so for him necessary to do a search again.

    Here is a part of search string I use "/People?searchview&[query=[Fullname]=*arsen*]" when using http://server_name/people?searchview&query=*arsen* Excel reads data fine, but when trying http://server_name/people?searchview&query=[Fullname]=*arsen* , the search string which suits both Notes and browser, Excel says: "MSExcel could not open or read this query file. Either the file has been damaged or the file format is not valid."

    As has been said, with http://server_name/people?searchview&query=*arsen* it works though, but of course results are different from those displayed in $$SearchTemplateDefault. Any ideas?

    Regards, Nick

      • avatar
      • Jake Howlett
      • Sun 8 May 2005

      Re: a bit futher...

      Have you tried escaping the brackets? Try the URL like so:

      &query=%5Bfullname%5D=*arsen*

      Show the rest of this thread

  22. sortabletable disables other JS functions

    The sortable table works like a charm! It is especially usefull for single category views, where there is no other possibillity for sorting the columns. Thank you! However I encountered a little problem. I added the sortabletable.js to an existing $$ViewTemplate that contained a button with a call to an other JS function. The JS function was defined in the JSHeader. After adding the sortabletablej.s, the other function stoped working. I moved the other function around the form and discovered that when the function is defined in the BODY of the form it works OK, when it is included in the HEAD of the form, the browser does not recognize it as a function. WHY? Can somebody shed some ligth on this problem? Is there some simple "trick" to avoid the situation?

    Eva

      • avatar
      • Jake Howlett
      • Wed 1 Jun 2005

      Re: sortabletable disables other JS functions

      When you added the script to the form, did you make sure to close the tag, like so:

      <script type="text/javascript" src="sortabletable.js"></script>

      Notice the close </script> tag.

      Show the rest of this thread

  23. Authenticated Excel Queries don't work in 6.0.5

    Recently moved to Notes server version 6.0.5. SSL User Authenticated queries which worked prior, don't anymore. Get message :

    "Unable to open <url>. Cannot download the information you requested.

    Anonymous works ok.

    Any thoughts?

  24. NSF Version

    Hi Jake, I downloaded your Demo version and just wondered what I can find inside :-) But was not able to open Err Msg Invalid NSF. I'm using LN Version 5.0.12. Was this DB created by a later version?

    Thx 4 your usefull tipy & tricks

  25. Count

    Hi, I am able to export maximum 1000 records only even if I have 4,00,000 records in the view. I gave the count parameter in IQY file and in URL but still it exports only 1000 records and not more than that

    is there any solution to it?

  26. Table built from AJAX return. Not sortable

    I am building a table in an agent and returning it to the browser. I have reviewed the returned html and verfied that it is in the correct format. Next I am appending the code below and using it as the innerHTML of a div. What I am finding is that the columns are not sortable. It appears as if the class sort-table is not being applied to the column. The graphics are not added to the header cells, and they are not click-sortable. If I build the table html by hand on the form, it works fine. Has anyone had experience with this? Help would be greatly appreciated.

    This code is in the callback function and I have verified that the table id is spelled the same as it is listed in the function:

    var jscriptdata = '<script type="text/javascript">var st2 = new SortableTable(document.getElementById("table-1"), ["CaseInsensitiveString", "CaseInsensitiveString", "USCurrency", "Date", "Number", "Number", "Number"]); st2.sort(1, false, "Nothing");</script>'

    $("tableoutput").innerHTML = req.responseText + jscriptdata;

    1. Re: Table built from AJAX return. Not sortable

      Solution:

      Add a function to build the table and call it from the AJAX callback function:

      function buildSortableTable( id ) { var el = document.getElementById( id ); window.alert(el.innerHTML); var st = new SortableTable( el, ["CaseInsensitiveString", "CaseInsensitiveString", "USCurrency", "Date", "Number", "Number", "Number"]); st.sort( 1, false, "Nothing" ); }

      and call it like so.. from the response handler: buildSortableTable("table-1");

  27. Column with Time value is not sortable :-?

    I have a column which has as formula:

    @TextToTime(Dt_Time_Hours + ":" +Dt_Time_Minutes )

    The style of the column is set to date/time and show time only. If I select hours and minutes the sorting goes wrong, if I select hours only the sorting works :-?

    Does anyone has a clue?

    1. sort it as string, not date or anything else...

  28. What did I do wrong?

    I took the 1.2 version and copied it to my web server. When I try to access it, I get an error when the page loads: Line:18 Char:1 'SortableTable' is undefined. It does not appear as if the CSS is applied and the view is not sortable.

    • avatar
    • David
    • Sun 10 Jun 2007

    Variant of Excel Export Method

    I found the web query methodology problematic when using authentication on the notes server since Excel would force you to login to get access to the data. I found that by modifying the $$ViewTemplate form for the exported view to have the content type of application/vnd.ms-excel, I get the same export capability without needing the web query or having to relogin.

    This solution works with Excel 2003 and we have used this setting for some time with our agents for exporting excel data.

    1. Re: Variant of Excel Export Method

      Where in the Viewtemplate do you put that code?

Your Comments

Name:
E-mail:
(optional)
Website:
(optional)
Comment:



Navigate other articles in the category "Views"

« Previous Article Next Article »
Processing multiple documents from a view   None Found

About This Article

Author: Laurens and Redouan
Category: Views
Hat Tip: Jake Howlett
Keywords: Views; lightweight; export; excel; sortable; columns;

Attachments

perfview-v1.0.zip (165 Kbytes)
perfview-v1.1.zip (256 Kbytes)
perfview-v1.2.zip (260 Kbytes)

Options

View Online Demo
Feedback
Print Friendly

Let's Get Social


About This Website

CodeStore is all about web development. Concentrating on Lotus Domino, ASP.NET, Flex, SharePoint and all things internet.

Your host is Jake Howlett who runs his own web development company called Rockall Design and is always on the lookout for new and interesting work to do.

You can find me on Twitter and on Linked In.

Read more about this site »