Tue 15 Sep 2020
Exciton Boost - Formula(s) for success
Tue 15 Sep 2020
Pushing harder at the limits of formulas
Mon 14 Sep 2020
Exciton Boost - Secret Agents protecting your data
OpenSesame - Edit view in spreadsheet (50,000 documents in 6 seconds)
Tue 20 Mar 2007, 09:03 AMTweet
by Ben Langhinrichs
Copyright © 2007 Genii Software Ltd.
What has been said:
571.1. Philip Storry (20/03/2007 07:52)
Ben, you're insane.
Insane in a good way, of course. But still insane.
Now I need to think of what I could use this for...
Actually, I already know that. I always wanted to write a Notes C API app that would use a simple query language to make bulk changes on documents. During my meagre spots of developing applications in my career, there's always been a point where someone's handed me an app and said "we've changed our minds about this field and this field - they need to contain this and this".
It's not that the agent is hard to write, nor that it takes long to test. It's that I had to do it too many times - and I dreamt up my C API app as a solution - albeit one I knew I would never have the time to write.
But now, I'd just throw together a view and load it into a spreadsheet, then do a search and replace and update the two.
I'm a little sad that I will now never write that C API app. But the fact that its main purpose is now so easily achievable elsewhere makes me happier than I am saddened by that. :-)
Brilliant work. I hope it gets all the success it deserves!
571.2. Giuseppe Grasso (20/03/2007 08.28)
been there, done that.... or something similar:
notes tabular data editing using ole and excel.
The use of OOo or Productivity Editors is way more interesting.
571.3. Ben Langhinrichs (03/20/2007 08:33 AM)
The basic idea is not new, but this skips all the OLE and COM type calls, or exports and imports, and works directly with the ODF, thus offering a lot more scalability and control. With Notes 8 and the built in ability to know what spreadsheet is available, we can do all sorts of cool new integrations, such as this one.
571.4. Ian Randall (03/20/2007 03:07 PM)
Very interesting idea Ben, now if you could also provide a simple (end user oriented) method to embed the ODF graph object into a Notes document, this could become an ideal platform to create a universal Lotus Notes 8 Executive Dashboard capability.
But why stop there, it would be even better if the integration extended the other way too, by providing a real-time drill down into the underlying Notes source data from the ODF Graph object.
571.5. Richard Schwartz (03/20/2007 06:02 PM)
What happens if you add or delete lines? Or while you are editing the spreadsheet someone else adds or deletes docs, or just edits existing docs?
571.6. Ben Langhinrichs (03/20/2007 06:47 PM)
Richard - Good questions. The first answer is that there is a UNID stored as a key in the row. If the UNID isn't found in the db, the values are kicked out. If the doc was edited by someone else, you have your classic save conflict. Docs that were added to the db don't effect anything, while deleted docs will stay deleted and not get updated. One more thing is that if you don't have edit rights to the document, it won't get updated. Now, if a row in the spreadsheet is deleted, that is a different issue. That could mean that you want to delete the document, or it could mean that you just don't want to deal with those documents. While I have not coded that yet, I think it is likely to prompt the user to decide whether to delete those documents or not. It may not be obvious, but I have change recording turned on, so I can tell which rows have been updated. That way I could even have a new row create a new document, but that raises lots of issues, so I probably won't go there. On the other hand, since it shows which rows have been deleted, I can deal with those.
I am thinking about switching the UNID to a visible link, but my initial testing showed that a Notes URL would not work from the spreadsheet. Shame really, as that would make it even more view like.
571.7. Dan King (21/03/2007 06:48)
I think Ian's point is spot on - if that could be done it really would please the boss (and not just mine I suspect).
With OLE I can do most of what you're saying (although maybe slower) - what you mean by working directly with the ODF?
Is it true OLE isn't possible with the the productivity editors in 8.0?
571.8. Ben Langhinrichs (03/21/2007 07:35 AM)
Dan - I am working with IBM to make sure we can embed the results back into rich text. If that doesn't work, I have other more convoluted ways to turn a graph into an image and embed, so I will likely be able to do what Ian suggests at some point. As for your comments, you can do some of what I am doing with OLE (except that as you say OLE is not available in 8.0 for the productivity apps), but not other parts. For example, can you do this to any view without writing a different agent and have it know the fields and columns to use (and categories and such)? Can you act against a database with a hidden/locked down design? Can you send the spreadsheet to a customer without Notes and let them adjust the values and send it back and update it with a single command? Can you execute your OLE from the web and use the Google calculator to modify the results and then save back to your Notes view? All of those are quite feasible with this tool, if not fully exposed yet (it is still a prototype).
571.9. Stephan H. Wissel (03/23/2007 10:17 AM)
Combine that with webDAV and you automate the spreadsheet part. The sheet would just show up in a web folder and when opened your export magic starts, when closed the import would run.
Can your code be called from Java?
571.10. Ben Langhinrichs (03/23/2007 10:45 AM)
Stephan - The webDAV idea is interesting. As for Java, it can currently be called by using evaluate for the @DbCommand, but I think I will make it directly accessible from Java as well.