Genii Weblog
Delving deeper into your data - Part 2
Thu 13 Jun 2019, 11:03 PM
Tweetby Ben Langhinrichs
This is my second post in the series introduced in Delving deeper into your data - Intro in which I show how the Midas LSX can be used to query deep into Notes data and return results in JSON or XML for manual or programmatic purposes. In Part 1, I showed how to search across all graphics in the Body fields of a database, returning only those that met some criteria in a JSON result set. In this, I show how the Midas LSX can expand to handle multiple databases, and how the results could also be XML if that were desired. I expand on the flexibility of formulas using chunk properties. So, let's look at the second question I posed in the intro:
How would I find all doclinks in our three sales dbs that point to our product db? Or the ones that don't?
As before, I am using databases that are available in place of an actual sales dbs and product db. For the purpose of this demo, I use the Business Partner forums for 2006, 2007, and 2008. In place of the product db, I'll use the Fix List database, as people occasionally put links to specific SPRs and such. Results for all three dbs will be returned together. We will cycle through rich text chunks with a target of 'Doclink', and since the replica id for the fix list db is 8525692D0056EDAF, we will make the chunk filter formula (see Part 1 for more details on that) we would use would be @ChunkStr(LinkReplicaID) = "8525692D0056EDAF". (If we wanted all the doclinks that pointed anywhere else, we'd use != instead of =.)
Now, what do we want in our result set? I decided to add the Subject of the document, but then rather than giving you individual pieces, I used a formula to generate the Notes URL. It has to be clever enough to only use the replica id if it is a db link, only the replica id and view id if it is a view link, but use all three if it is a regular doclink. The formula I used was:
"notes:///" + @ChunkStr(LinkReplicaID) + @If(@ChunkStr(LinkViewUniversalID)="00000000000000000000000000000000"; ""; "/" + @ChunkStr(LinkViewUniversalID) + @If(@ChunkStr(LinkNoteUniversalID) = "00000000000000000000000000000000"; ""; "/" + @ChunkStr(LinkNoteUniversalID)))=NotesURL
Now, let's see what this looks like as a simple result set in XML.
or we could return the same result set as JSON:
We could have sorted the results for either. but by default a result set generated from a selection formula is order independent, as both XML and JSON are in this kind of task.
Now, what if we needed each of the results to have more complete document information? For JSON, we'd use the Domino format which included doc information. For XML, we'd set a parameter called DominoMetadata='Yes'. If we generated the same two results with these changes, we'd get:
or the same result set in JSON using the Domino format:
Copyright © 2019 Genii Software Ltd.
What has been said: