Demo file: virtual-portal-v1b.zip
This is a follow-up to last month’s article on virtual list, and this time we’re going to explore a way to use virtual list in a portal.
Imagine you have built a system where a Company can be a parent of a Mill, Refinery, Estate, or another Company.
Each of these entities exists as a separate table in your database…
…and from the perspective of a Company record, you’d like to be able to see all immediate children.
Of course you could go the traditional route with four portals like so…
…but what if you instead want to view all child entities in a single portal, regardless of entity type, like this?
It’s true that FileMaker portals don’t normally work this way, but fortunately virtual list can help. Let’s see how. Here’s the portal in layout mode…
…and you’ll notice that rather than referencing a regular data table, it’s pointing at a stripped down virtual list table, which I have chosen to call “virtual_portal”, and which consists of just two fields: ID and cell_text_r.
(For more info on these two fields or the array structure shown below, see last month’s article.)
The job of the repeating field cell_text_r is to render data from this array…
…which is auto-generated via a Hide Object calculation attached to this field:
Note 1: the Hide Object calculation is not actually hiding anything; its purpose is to populate and update the $$json.childEntities variable as necessary.
Note 2: see last month’s article for two other techniques to build arrays.
As we navigate from company record to company record, we want the portal to update in real time, and to facilitate this, in addition to the preceding, we utilize a little helper script which will be invoked like so:
Next, a few words about the relationship from COMPANY to VIRTUALPORTAL since it’s a bit unusual.
As we navigate company records, the number of rows in $$json.childEntities changes, so what we want to see via this relationship is the number of corresponding rows in virtual_portal. The predicate on the left is defined like so:
And since, as you may recall, the ID field in the virtual list (or in this case, virtual_portal) table is a sequential serial number beginning with 1, we can use the ≥ operator to limit the number of portal rows that will display data.
Incidentally, if you’re wondering about the four TOs at the right…
…they facilitate navigation when you click one of the blue triangles inside the portal.
Here are some limitations of this technique to be aware of:
1. Since the JSON array is generated without using JSONSetElement, potentially problematical characters will not be auto-encoded. For example, since a hard return serves as the row delimiter of the array, it will wreak havoc if it appears as data within the array. In today’s demo this risk has been substantially mitigated via an auto-enter calc attached to the name field in all four data tables:
cfZap ( Self )
For suggested code improvements to resolve other potential problems, see Alex Zueiv’s comment at the end of this article.
2. You cannot search within the virtual portal. However, you can search these fields beneath the portal because they are based on relationships to standard data tables.
Also note that the labels on the first four fields above are hyperlinks. If you click them you will go to the corresponding records in the target table.
The technique we’ve looked at today is an example of how virtual list provides a framework to accommodate “impossible” requests. I will confess that I’ve only had this particular request come up once in the 25 years that portals have been a component of FileMaker.
On the other hand, this narrow use case is merely a proof of concept of the liberating idea that a portal can display whatever you want it to display, as opposed to literally rendering rows from one related data table.
At any rate, it will be interesting to hear whether anyone else finds this useful, or has solved this challenge via other means.
Note: The other obvious approach for combining entities I can think of would be to use the party data model, but that would involve a reworking of the system, whereas virtual list can be bolted on to whatever system you currently have.
A few months ago, when I mentioned to Paul Jansen that I was thinking of doing an article on Virtual Portals and asked if had any examples, he emailed me these screen shots and comments, which I am a. sharing with his permission, and b. hoping we can consider to be a teaser for a future guest article.
This portal is based on comments table where each record has been split into a number of rows in a virtual list. A button bar makes use of hide and conditional formatting to produce the result you see below. Matching icon colour to background colour makes them invisible.
Italics and background colour applied based upon the row type value; the second column in the array which can be H (header), C (comment), or S (spacer ).
Very cool. Just the other day a client asked me if we could show items from two different sources in the same portal, and of course my initial response was no. Whenever I start to say no I should insert the words “let’s ask Kevin”!
I appreciate your comment Mark. When you have a virtual list hammer in your tool kit, many impossible requests look like nails :-)
The idea of constructing a data model in the “right” way with supertyping (which I guess the Party Model is an example of) you don’t need any virtual portals (or less) – is an idea that seem right to me, at the moment.
If one agree with this idea, the need of a virtual portals becomes a symptom of that the data model is not the right one?
Have you been thinking in these terms about virtual portals?
To what extent is the need for virtual portals/virtual lists a symptom of bad data modeling?
I have never used virtual lists/portals (yet) – and this is something I wonder about…
Hi Tobias,
Thanks for your comment. I would agree that if you don’t get your data model correct, you will need to employ work arounds to compensate. On the other hand we can’t fundamentally re-architect our systems every time we get a weird reporting or interface request.
To my way of thinking virtual list is a *presentation* layer tool that meets us where we are, so to speak.
Best wishes,
Kevin
Right.
Thinking about the relation between data models and virtual lists/portal – would you say a model where e.g. the hierarchical entities PROJECT, PHASE, TASK, SUBTASK are consolidated and merged into one table called WORKEFFORT will likely reduce the need for virtual portals and lists?
Hi Tobias,
Bear in mind that virtual list is an “all purpose” presentation tool, with application far beyond the specific use case I demonstrated in this article.
There’s no requirement that any developer use virtual list ever as far as I’m concerned, and I encourage you to architect your solution in the way that makes the most sense to you.
Best wishes,
Kevin
As usual, Kevin, amazingly clever technique. Thanks for sharing.
Thanks Michael I appreciate you saying so.
Kevin, great article!
But creating JSON from eSQL requires a bit more efforts. Using a custom field separator is not enough. If, for example, some company name contains quotation marks, it will break the calculations.
You need to quote the whole result in JSON style to escape all possible special characters. This will require to temporary replace the field and row separators with some characters that can newer appear in the result.
I believe, the simplest solution is the following:
1. Remove the function Substitute() from the variable x definition:
x = Case ( …
2. Change each eSQL call the following way:
ExecuteSQL ( ”
SELECT CHR(1) || id, ‘Company’, name
FROM company WHERE id_parent = ? ORDER by name
” ; Char(2) ; Char(3) ; COMPANY::ID )
3. Remove the function List() from the default Case() result and replace semicolons (;) with ampersands (&):
ExecuteSQL(…) & ExecuteSQL(…
4. Change JSON definition the following way:
$$json.childEntities = If ( not IsEmpty ( x ) ; Substitute (
JSONSetElement (
“[]” ; 0 ;
Replace ( x ; 1 ; 1 ; “” ) ;
JSONString
) ;
[ “\\\\” ; “\1\2” ] ;
[ “\u0001” ; “\”]¶[\”” ] ;
[ “\u0002” ; “\”,\”” ] ;
[ “\u0003” ; “” ] ;
[ “\1\2” ; “\\\\” ]
) )
This calculation will fail only if some field contains character 0x01 or 0x02.
Hi Alex,
I was hoping some smart person would suggest something like this.
Thank you!
Kevin
Another option would be to use a calculation field (it can be unstored to reduce the weight) to create the JSON natively and avoid any issues with “dodgy” characters.
jsonRow = JSONSetElement ( “[]”
; [ “0” ; COMPANY::ID ; JSONString ]
; [ “1” ; Get ( LayoutTableName ) ; JSONString ]
; [ “2” ; COMPANY::name ; JSONString ]
)
which will produce [“C00257″,”Company”,”Fastsigns”]
This single field can then be safely retrieved with ExecuteSQL
Had not considered that. Nice.
P.S. No need to quote the numbers when generating an array :-)
Kevin,
I notice that when I switch from one record to another I briefly see the first part of a JSON error for each field:
? * Line 1, Column 1
Syntax error: value, object or array expected.
* Line 1, Column 1
A valid JSON document must be either an array or an object value.
To prevent this from happening, I changed the virtual list field calculation to first test the JSON for validity and display an empty string if the JSON is invalid:
Let ( [
i = ID[1] ;
r = Get ( CalculationRepetitionNumber ) – 1 ;
t = GetValue ( $$json.childEntities ; i )
] ;
If ( JSON.IsValid ( t ) ; JSONGetElement ( t ; r ) ; “” )
)
Where the JSON.IsValid custom function is defined as
Let(
[
~isArray = Left( json ; 1 ) =”[” ;
~test = If(
~isArray ; Left( JSONGetElement ( json ; 0 ) ; 1 ) ;
Left( JSONGetElement ( json ; “xxxxxxxx” ) ; 1 )
)
];
~test ≠ “?”
)
Good catch and excellent fix. Thanks.
It just occurred to me that we know in advance that the JSON we want to test for validity will always be an array. So we can simplify the test and eliminate the custom function.
Let ( [
i = ID[1] ;
r = Get ( CalculationRepetitionNumber ) – 1 ;
t = GetValue ( $$json.childEntities ; i )
] ;
If ( Left( JSONGetElement ( t ; 0 ) ; 1 ) ≠ “?” ; JSONGetElement ( t ; r ) ; “” )
)
(Replying belatedly….) Thank you Paul. I have updated the article and demo to incorporate this improvement.