I have played a bit with the Scripting block and I am amazed at its potential!
Though I am stuck following a recent idea to enhance our base capability.
We are managing real estate opportunities and we’d like to create a tool that matches all our client needs with our land portfolio characteristics.
Let me explain, I have a table for owners’ requests (CANDIDATES) and another for land characteristics (LAND). I have several other tables with the characteristics by type (identical to the owners’ requests) to identify possible matches between the two.
In my CANDIDATES table, I can look up all the LAND for each characteristic in different fields.
Now what I’d like is to produce a report or table identifying the unique lands which have all the characteristics of the owners’ requests and that for each Candidate.
Read the requests table (and the select fields) into an array (Candidates), and then do the same in another array (Land).
Loop across the Land array and compare each of the select attributes to every Candidate item in the Candidates array.
When the attributes match, add these common attrubtes to a new array item with the record IDs and values from each of the comparative table records.
This will give you a joined record of matches in a new array.
Indeed, it requires that you scan Candidates for each and every Land item, and while this seems a bit inefficient, you’ll see that even with 20,000 records, the process will be very fast.
I also sense that this could be simplified with an array join, but my objective was to help you see the approach which would be slightly more abstract with Array Join.
Rendering as a table in Script Block is a whole new challenge. You could output the results as a list in the script block or you could update this joined list to a table to make it more like a report. You could also transform the joined array into a JSON array and render it nicely in the Script Block as a table.