Genii Weblog
Data from Domino 1: Rows ≠ Documents
Mon 26 Oct 2020, 05:05 PM
Tweetby Ben Langhinrichs
At Genii Software, we help a lot of companies get data from Domino applications, whether for migration (using the Midas LSX) or for use in mobile/web apps (using Exciton Boost, one of our Midas products, or a bespoke addin created for the customer). While we are best known for our high fidelity rich text rendering, a lot of application data is not rich text. But that doesn't mean it isn't complex. In this series, I will show ways that the complex is handled simply by both the Midas LSX and Exciton Boost.
A couple of weeks ago, we had a migration customer ask if we could change the way their data was extracted. In this case, we were going to CSV. I can't show their data or structure, but it contained a feature I lot of you will recognize: many, many fields correlated with each other by table rows. Since the customer wanted to retain the row-like association of the various items, we used Repeat By Item, a feature which allows you to export the same document multiple times with each element of a multi-value field or formula, matching up parallel items. We can include the UNID on every line or not, depending on whether we want to maintain the association the data had in Domino.
To show how this often looks in a Domino application, I created a simple form and view to display the box scores used to record player stats in baseball. (You could do the same with cricket or another sport, of course.) Here's a game played recently with a small set of box scores. This is from the Cleveland Indian's point-of-view, and there is a second document for the same game from the Chicago White Sox point-of-view. The goal might be to create a spreadsheet such as that above or to export the data into SharePoint or almost anything else. CSV imports are fairly ubiquitous.
In our bare bones Domino document, we have the general fields followed by a table of the stats for each player as they entered the rotation.
Domino document
In our bare bones Domino document, we have the general fields followed by a table of the stats for each player as they entered the rotation.
Domino form
In our form, we have a sight all too familiar to long-term Notes/Domino developers. Each row has individual fields, sometimes more than one to a table cell. The names are related by some naming convention. In our customer's form, there were over 700 named fields.
Using the Midas LSX with Midas Exports, we accomplish this by filling out a simple form. No scripting is required, though formulas are allowed and are used in this case. The key parts of the form are here:
and below that, the split by item (though this version of the db has a spelling error. I mean "spliting"? Yikes.):
If we run the agent in our database, we get this CSV
Opened in Excel and tweaked slightly so we can see it better, we get the desired spreadsheet shown at the top of this post.
This worked well for the customer, generating several million rows of data to import into their new system. We also generated high fidelity renderings of the forms, but that was for a different purpose.
Request a Midas LSX evaluation license and see if it meets your migration needs.
As it happens, we can do this exact same rendering using Exciton Boost's REST API. Instead of an agent, we used a GET request to display one of the views:
In the request, we set the Accept header to text/csv. We set the X-Items-List to the same formula as in the form for the Midas export:
Team+@If(Home="";" at ";" (h) vs. ")+OppTeam+" on "+@Text(GameDate)=Game,(Num:Num_1:Num_2:Num_3:Num_4:Num_5:Num_6:Num_7:Num_8:Num_9:Num_10:Num_11:Num_12:Num_13)=Num,(Player:Player_1:Player_2:Player_3:Player_4:Player_5:Player_6:Player_7:Player_8:Player_9:Player_10:Player_11:Player_12:Player_13)=Name,(Pos:Pos_1:Pos_2:Pos_3:Pos_4:Pos_5:Pos_6:Pos_7:Pos_8:Pos_9:Pos_10:Pos_11:Pos_12:Pos_13)=Position,(AB:AB_1:AB_2:AB_3:AB_4:AB_5:AB_6:AB_7:AB_8:AB_9:AB_10:AB_11:AB_12:AB_13)=AB,(Rs:Rs_1:Rs_2:Rs_3:Rs_4:Rs_5:Rs_6:Rs_7:Rs_8:Rs_9:Rs_10:Rs_11:Rs_12:Rs_13)=R,(Hs:Hs_1:Hs_2:Hs_3:Hs_4:Hs_5:Hs_6:Hs_7:Hs_8:Hs_9:Hs_10:Hs_11:Hs_12:Hs_13)=H,(BB:BB_1:BB_2:BB_3:BB_4:BB_5:BB_6:BB_7:BB_8:BB_9:BB_10:BB_11:BB_12:BB_13)=BB,(RBI:RBI_1:RBI_2:RBI_3:RBI_4:RBI_5:RBI_6:RBI_7:RBI_8:RBI_9:RBI_10:RBI_11:RBI_12:RBI_13)=RBI
The response is:
201 OK
Game,Num,Name,Position,AB,R,H,BB,RBI
Chicago White Sox at Cleveland Indians on 09/24/2020,7,Tim Anderson,SS,4,0,0,0,0
,10,Yoan Moncada,3B,4,1,1,0,0
,24,Ysmani Grandal,C,4,0,0,0,1
,79,José Abreu,1B,3,1,0,1,0
,74,Ely Jiménez,LF,3,1,1,0,0
,15,Adam Engel,LF,0,0,0,0,0
,33,James McCann,PH,1,0,0,0,0
,23,Edwin Encarnación,DH,3,0,0,0,0
,30,Nomar Mazara,RF,3,0,1,0,2
,5,Yolmer Sánchez,2B,2,1,1,1,1
,32,Jarrod Dyson,CF,3,0,0,0,0
Cleveland Indians (h) vs. Chicago White Sox on 09/24/2020,12,Francisco Lindor,SS,3,0,0,1,0
,7,César Hernández,3B,4,1,3,0,3
,11,Jose Ramirez,3B,3,0,2,1,2
,41,Carlos Santana,1B,4,0,0,0,0
,32,Franmil Reyes,DH,4,0,1,0,0
,8,Jordan Luplow,RF,2,0,0,0,0
,31,Josh Naylor,PH-LF,2,0,1,0,0
,35,Oscar Mercado,LF,2,0,0,0,0
,30,Tyler Naquin,PH-RF,2,1,1,0,0
,55,Roberto Pérez,C,2,1,1,0,0
,6,Mike Freeman,PH,1,1,1,0,0
,17,Austin Hedges,C,1,0,0,0,0
,0,Delino DeShields,CF,3,1,0,0,0
Or we could switch to JSON or XML and use those instead, simply by changing the Accept header. It's that simple.
Request an Exciton Boost evaluation license and try it out in your modern web/mobile apps.
Copyright © 2020 Genii Software Ltd.
What has been said: