Genii Weblog
Pull public data into Notes on the fly
Wed 5 May 2021, 05:47 PM
Tweetby Ben Langhinrichs
This update on an earlier post shows some of the capability of Midas LSX Version 5.75, released this past week. The goal is an instant little mini-report in a rich text field, based on almost any public REST API. Let's start at the end this time. There is a nifty REST API to retrieve foreign exchange rates. You call it at https://open.exchangerate-api.com/v6/latest. An example is today's results, which are
{"result":"success", "provider":"https://www.exchangerate-api.com", "documentation":"https://www.exchangerate-api.com/docs/free", "terms_of_use":"https://www.exchangerate-api.com/terms", "time_last_update_unix":1620172951, "time_last_update_utc":"Wed, 05 May 2021 00:02:31 +0000", "time_next_update_unix":1620261101, "time_next_update_utc":"Thu, 06 May 2021 00:31:41 +0000", "time_eol_unix":0, "base_code":"USD", "rates":{"USD":1, "AED":3.67, "AFN":78.37, "ALL":102.81, "AMD":520.88, "ANG":1.79, "AOA":650.5, "ARS":93.66, "AUD":1.3, "AWG":1.79, "AZN":1.7, "BAM":1.62, "BBD":2, "BDT":84.84, "BGN":1.62, "BHD":0.376, "BIF":1950.25, "BMD":1, "BND":1.33, "BOB":6.89, "BRL":5.45, "BSD":1, "BTN":73.91, "BWP":10.93, "BYN":2.57, "BZD":2, "CAD":1.23, "CDF":1982.38, "CHF":0.914, "CLP":704.64, "CNY":6.48, "COP":3790.25, "CRC":613.73, "CUC":1, "CUP":25.75, "CVE":91.35, "CZK":21.54, "DJF":177.72, "DKK":6.18, "DOP":57.03, "DZD":133.71, "EGP":15.65, "ERN":15, "ETB":42.02, "EUR":0.829, "FJD":2.03, "FKP":0.72, "FOK":6.18, "GBP":0.72, "GEL":3.43, "GGP":0.72, "GHS":5.77, "GIP":0.72, "GMD":51.87, "GNF":9886.02, "GTQ":7.72, "GYD":213.58, "HKD":7.77, "HNL":24.03, "HRK":6.24, "HTG":86.4, "HUF":300.5, "IDR":14451.3, "ILS":3.27, "IMP":0.72, "INR":73.91, "IQD":1455.73, "IRR":41896.5, "ISK":124.59, "JMD":153.18, "JOD":0.709, "JPY":109.33, "KES":107.31, "KGS":84.83, "KHR":4071.34, "KID":1.3, "KMF":407.58, "KRW":1124.57, "KWD":0.3, "KYD":0.833, "KZT":428.31, "LAK":9416.2, "LBP":1507.5, "LKR":196.79, "LRD":171.85, "LSL":14.47, "LYD":4.47, "MAD":8.92, "MDL":17.73, "MGA":3792.33, "MKD":50.8, "MMK":1558.46, "MNT":2844.74, "MOP":8, "MRU":35.96, "MUR":40.28, "MVR":15.42, "MWK":792.93, "MXN":20.24, "MYR":4.12, "MZN":57.47, "NAD":14.47, "NGN":395.55, "NIO":34.94, "NOK":8.32, "NPR":118.26, "NZD":1.4, "OMR":0.384, "PAB":1, "PEN":3.82, "PGK":3.51, "PHP":48.08, "PKR":152.86, "PLN":3.79, "PYG":6503.36, "QAR":3.64, "RON":4.1, "RSD":97.85, "RUB":75, "RWF":984.95, "SAR":3.75, "SBD":7.9, "SCR":14.99, "SDG":378.63, "SEK":8.47, "SGD":1.33, "SHP":0.72, "SLL":10205.78, "SOS":577.27, "SRD":14.12, "SSP":177.64, "STN":20.3, "SYP":1262.9, "SZL":14.47, "THB":31.2, "TJS":11.31, "TMT":3.5, "TND":2.74, "TOP":2.26, "TRY":8.31, "TTD":6.78, "TVD":1.3, "TWD":27.94, "TZS":2315.59, "UAH":27.75, "UGX":3561.65, "UYU":43.84, "UZS":10630.2, "VES":2840459.11, "VND":23031.05, "VUV":107.92, "WST":2.51, "XAF":543.44, "XCD":2.7, "XDR":0.699, "XOF":543.44, "XPF":98.86, "YER":250.25, "ZAR":14.47, "ZMW":22.33}}
Even if you don't deal with JSON much, you could probably pick out that the exchange rate with CAD (Canadian dollar) is 1.23. We could certainly use the new JSON classes in Notes to traverse the JSON, find the values and build a report, but our Midas LSX is all about maximizing what you can do while minimizing the effort to do it. So, my business users want a snapshot of four specific exchange rates. I decided to present them like this:
But how did I get from the JSON returned to this table? As a practical matter, I could just use our AppendFieldsWithJSON method to create fields from everything. It would look like this (and goes on and on until ZAR), though this is not current data:
You can see that when our method hits an object inside the object (this one called rates), it presents the values like this. But I wanted the rates presented more nicely, so I switched the properties of the AppendFieldsWithJSON to start down at the rates object (by setting ChildOf='rates'), and specify it as a vertical table inside a single tabbed table with a label. That looked like this:
This is close, but I only want my four currencies, and I think the background should be green. Oh, and I'd like the labels to reflect the commonly used names for these currencies. So, I change the properties string a bit to include a comma-delimited list of the items I want and the way I want them to appear.
props = "AsTable='vertical' AsObjectArray='yes' ChildOf='rates' Items='AUD=Australian Dollar, CAD=Canadian Dollar, EUR=Euro, JPY=Japanese Yen' "
After a bit more tweaking of the color and tab label, I get my final code:
Sub Initialize
Dim s As New NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim http As NotesHTTPRequest
Dim gs As New Geniisession
Dim rtitem As New Geniirtitem
Dim rtchunk As Geniirtchunk
Dim props As String
Dim json_val As String
Set db = s.CurrentDatabase
Set doc = New NotesDocument(db)
doc.Form = "JournalEntry"
doc.Subject = "Exchange rate data as of "+CStr(Now)
' *** Create the rich text item, overwriting it if it already exists
Call rtitem.CreateBackend(doc.Handle, "Body", True)
' *** Add the title, and make everything Verdana to look better
Call rtitem.DefineFont("Verdana")
Set rtchunk = rtitem.DefineChunk("Everything")
rtchunk.Font = "Plain 10pt Verdana"
Call rtchunk.AppendTable(1, 1, "RowDisplay='Tabbed' TabFont='12pt Bold Verdana' BorderEffects='Drop_Shadow' TableWidth='Fixed' ", _
"Text='' Width='3in' CellColor='RGB127, 255, 127' TabLabel='FX Rates with US Dollar'")
Call rtchunk.ZoomIn("Table 1; Row 1; Inside column 1")
' *** Retrieve the JSON to use
Set http = s.CreateHTTPRequest()
http.PreferStrings = True
props = "AsTable='vertical' AsObjectArray='yes' ChildOf='rates' Items='AUD=Australian Dollar, CAD=Canadian Dollar, EUR=Euro, JPY=Japanese Yen' "
' *** Appends the fields to the form and save
Call rtchunk.AppendFieldsWithJSON(json_val, props, "TextFont='Bold #004080'", "TextFont='-Bold #004080'")
Call rtitem.Save()
End Sub
and when I run that, I am back to the beginning. This could be the entire rich text field, or you could have an agent just drop it in where you want in an existing rich text.
A lot of power with a small amount of code, and it is easy enough to tweak and change and run again so you can design and create your look without spending a ton of time designing and creating your code. That's the Midas LSX advantage,
Request a Midas LSX evaluation license today and give it a try. We'll send you a sample db showing this code with a few variations to give you ideas.
Copyright © 2021 Genii Software Ltd.
What has been said: