Adding a new column to an Excel XML data table

We use XML feeds from our Domino applications to pull data into Excel. Users can right click on the data tables and they get updated from Domino.

The functionality generally works well but if you need to add a new “column” to your XML feed there is no way in Excel to pull this into the data table. You can either start again and pull in a new table or edit the XML files that make up the Excel file. If you already have lots of charts set up then starting again is not an option.

I needed to add a new column today so I thought that I would document what I had to do.

1) save the xlsm file as *.xlsm
2) change the file name to *.zip
3) unzip the contents
4) edit the file “.\xl\xmlMaps.xml” as shown below
5) rezip the file
6) change the file type back to *.xlsm
7) open the spreadhseet
8) use the developer ribbon bar to open the XML source panel
9) drag the new column onto the worksheet

 

 

 

 

XPages Document Save Error may be a document locking error

Moral of the story – read the logs

This one had us stumped for a while so I though I would post it.

We had an inconsistent error on an XPage application where it threw a save error even though the Author fields  were correct.

The error was

Exception occurred calling method NotesDocument.save()

After much head scratching followed the advice in log.nsf

please consult error-log-0.xml located in /local/notesdata/domino/workspace/logs

whereupon we discovered the message

Could not save the document 5386 NotesException: Notes error: The document is already locked by someone else.

So document locking was turned on which is not really much use in an XPage application ( we rolled our own application scope based scheme )

So I know it is a pain to connect to the server to get the log documents but it is worthwhile. I wonder could we write an XPage app to recover them ? 🙂

 

Scheduling XAgents on a Linux box using Cron

We used scheduled code in our XPage apps to do things like pre-building dashboards and storing the data in the application scope.

There is no simple way to schedule Java code that has been written for use with XPages. The best solution I have found has been to use “XAgents” and to poll the corresponding URLs somehow.

I have tried this using various methods but it has not been straight forwards due to authentication issues and having to deal with SSL certificates.

The best solution I have come up with to date is to use the linux “cron” task and the wget command which is typically used to download data via the web.  This can be configured using the crontab -e command line utility or easier still by using webmin.

Step 1 – create an internet site mapped to localhost. On this site disable session authentication as it seems to cause spurious issues with automated remote calls. Also disable any settings that force traffic to use SSL.

Step 2 – from the command line in linux test your proposed wget command – something like :

wget –user=USERNAME –password=XXXX “http://localhost/apps/aw.nsf/xp_f_process_bad_actors.xsp?source=Cron”

Step 3 – add this as a scheduled task using crontab -e or webmin as shown below

@hourly wget –user=USERNAME –password=XXXX “http://localhost/apps/aw.nsf/xp_f_process_bad_actors.xsp?source=Cron”

2015-10-20_17-52-35

Additional Notes

The download files when you run test downloads from webmin will be stored in /usr/libexec/webmin/cron/.. although if you schedule the task with a user such as Apache ( as shown above ) then no file is actually saved because of a permissions failure

The cron log is at /var/log/cron

Your Domino username and password will be stored in plain text in the crontab files and in the logs

You could also use bash script files and schedule these via Domino Program Documents

Thoughts for a future iteration

Making the trigger urls accessible via an anonymous user using sessionassigner somehow

 

 

Solved : Before Mail Arrives Agent stops inbox refreshing

This was a new one to me and IBM support did a great job of helping us to find the issue.

We modified a customers mail template to add some core business functionality that processed the inbound emails before they appeared in the in-box and provided some quick filing suggestions based on some business rules.

2015-10-09_17-56-36

In order to present the options in the UI we had to add information to the new emails – this is where we go caught out.

If you use document.save as part of the “Before Mail Arrives Agent” then the inbox will not refresh automatically – or the refresh will be delayed. On our customers server the refresh failed in 100% of cases whereas on my dev server it was delayed 60% of the time and failed 40%  of the time. For IBM support it was never delayed or failed.

When you look at the help document for the DocumentContext property it states

For an agent activated “Before New Mail Arrives,” the in-memory document is the email that is about to be delivered. Because the agent is activated instantly for each email as it’s about to be saved into the mail database, each time the agent runs you are working with a single unsaved document. The UnprocessedDocuments property in AgentContext will not return any documents for this agent type.

So if you modify it you do not need to save it – because it is about to get saved.

So we removed our document.saves while session.isonserver = true and the inbox refresh now works.

Many thanks to Djesus Rex C. Cada from IBM support who went though the past tickets and found the work around for us.

Sean