Genii Weblog

Civility in critiquing the ideas of others is no vice. Rudeness in defending your own ideas is no virtue.

Mon 25 Jan 2021, 11:28 AM
Inline JPEG image
Continuing on from last week's post, we are looking at better ways to retrieve data based on two factors:
1) How much data do you need from any given record, and which records do you need it from? If there is related data in another record, can you get it without pulling in loads of stuff you don't need? 
2) What format should the data be in? How should it be structured or grouped for maximum usability? While you can certainly write code in your application to reformat or restructure, that adds complexity and room for error on your client.
Again using Exciton Boost 4.6 with the Curb Appeal database, we'll examine a couple of additional Fancy Tricks (aka "useful features").
Fancy trick 3: - Saving preliminary values
This may not sound so fancy, but sometimes the value you want is the result of multiple operations. Rather than passing back all the pieces and doing the processing on the client, which might require multiple requests, we can save the results of a formula or rich text operation into a temporary field, and then use those temporary fields to create the result.
On Friday, I posed a challenge called Sorted lists of bullets. In the real estate listings in Curb Appeal, there are usually two, occasionally, three columns of bullet items under the Features heading. These are rich text fields, but all we really care about are the text values for each bullet. In our second Fancy Trick from the first post, I talked about using rich text items as a data source.
Inline JPEG image
Using that trick, we could get the list of bullets from each of the three rich text fields, Col1, Col2, and Col3. (Imaginative naming, eh?) For this fairly simple example, we could return all three lists as separate arrays, then merge the arrays, then sort, all using JavaScript or whatever language. The item list to retrieve these would look something like this:
Col1[BulletList *;$UnformattedText]{A}=bullets1,Col2[BulletList *;$UnformattedText]{A}=bullets2,Col3[BulletList *;$UnformattedText]{A}=bullets3 
This would produce three arrays named bullets1, bullets2, and bullets3. But this is where the Fancy Trick comes in. By changing the type string in curly braces from {A} to {AP},  with the P meaning Preliminary, none of the three arrays would appear in the result. Instead, a multi-value temporary text list item would be be added. It uses the name preceded by two dollar signs, e.g., $$bullets1 plus a period plus the chunk property, so the field $$bullets1.UnformattedText is now a Notes text list.
Col1[BulletList *;$UnformattedText]{AP}=bullets1,Col2[BulletList *;$UnformattedText]{AP}=bullets2,Col3[BulletList *;$UnformattedText]{AP}=bullets3 
As a final step, we can use formula language now that we have simple text list fields, so we add an additional item as 
The three text lists get appended together then sorted with @Sort, and the result is a sorted list bullet values returned as bulletsSorted. Since we can use all of Notes formula language, we could done @ReplaceSubstring or @Unique or whatever. The client app simply gets the data it needs in the form and format it needs them.
Imagine the following code. Can you see what it would do, and how you might be able to use it while building a web page result?
Body[Graphic *;%GraphicWidth]{AP}=widths,@Max($$widths.GraphicWidth)=maxWidth
Fancy trick 4: - Retrieving related data from other documents and databases
Anyone who has worked with Notes for a while will know that a view cannot do @DbLookups to get related information. If an item number is used but the item name, description, and price are in another database, you can do @DbLookups to retrieve them from inside a document but not from a view. In a typical REST API, you might do one request to get this document's information and then other calls to retrieve related information, but Exciton Boost allows you to use formulas, so you can retrieve related information in your first request. Rather than retrieving lots of data and dealing with it on the client, this also allows highly efficient view operations and caching to empower these lookups. As a simple example from our Curb Appeal database, we could use the following to display the average price of all houses in the current city.
City+", "+State{P}=key,@DbLookup("";"";"Listings\\by Location";$$key;2){P}=prices,@Text(@Sum($$prices)/@Elements($$prices);"C")=avg 
so, for a listing in Beachwood, OH, you would get
Note that I used the previous Fancy Trick twice to save the key and then save the results of the lookup. Also note, when the values do not come from chunk properties but rather from formulas or items, the name is simply preceded by the two dollar signs. This process is very similar to saving variables in any language, but especially in cases where many data points are involved, it saves considerable time and bandwidth.
=> Request a free Exciton Boost eval in January, and if you later purchase a license. we'll add in four hours of development assistance, a $700 value. <=
If you have found these tricks interesting or intriguing, let me know and I can show more that relate to other parts of the REST API or remote procedure calls.

Copyright 2021 Genii Software Ltd.