Filtered portal with summary field - blank when zero

[Angus McKinnon]

I’m trying to set up a simple “Dashboard” style layout, the objective being to help users see the state of play with orders at a glance. 

As things stand, the Dashboard layout is based on its own table, and uses filtered portals to show records from an Orders table. 

 

So my prototype dashboard consists of a bunch of small portals, each filtered differently to give a total number of orders with a particular status. For example, “All orders due to leave today” is just filtered by  Date Out = Get(CurrentDate)  Then there’s another for “All orders still to leave” which is filtered the same way but adds Order Status ≠”Dispatched”. The plan is to have a whole series of these for different statuses. 

 

What I’ve done is brought each portal down in size so it’s just displaying one row, and a single number. This number is a summary field, it’s a Count function of the ID number field in each order. 

 

This seems to work fine, the only exception being that if the result is zero, nothing is displayed. I realise that this is because there are no rows in the linked portal, therefore nowhere for the Summary to be displayed. 

 

It’s not a deal-breaker by any means, but I’m annoyed by the look of it. I can think of a few potential workarounds but it’s making me think that there may be a better technique to get the same end result?

[Angus McKinnon]

So, the least inelegant workaround I've managed so far is to hide the portal when there are no rows present. The way I'm doing this is using

Length ( GetLayoutObjectAttribute ( "DashboardPortalSummary1" ; "content" )) = 0

So that when there isn't a digit present in that summary field on the layout, the portal isn't shown. 

 

Then I have a zero as a normal text label underneath in the same font etc. The drawback with that is I have to extend the text box beyond the bounds of the portal with some additional spaces, so that the label doesn't disappear along with the portal. 

 

This works, but it's reinforced my belief that I may be approaching this whole thing from the wrong angle. Does anyone have any better solutions?

[comment]

Perhaps an easier workaround would be to allow the relationship to create records in the Orders table. Then you can put the zero text inside the portal row and hide it when not IsEmpty ( Orders::OrderID ).

Ultimately though, dashboards are a pain both in terms of required work and performance. You might want to consider gathering all the required data via script (possibly with the help of ExecuteSQL) and passing it to a web viewer (or even to a text field) for display.

 

Edited January 13, 2023 by comment

[Angus McKinnon]

19 hours ago, comment said:

Perhaps an easier workaround would be to allow the relationship to create records in the Orders table. Then you can put the zero text inside the portal row and hide it when not IsEmpty ( Orders::OrderID ).

I'd never thought of using not with IsEmpty, that's a technique I'll definitely remember. 

I wondered about having a "dummy" order record that would always appear - essentially add a condition to each filter to ensure it is always included. Of course, this would then throw the summary field off by 1, so I suppose I'd have to use a calculation to adjust that. This is still potentially neater than having ramshackle layout kludges all over the place. 

 

19 hours ago, comment said:

Ultimately though, dashboards are a pain both in terms of required work and performance. 

I'm slowly discovering that. 😃

19 hours ago, comment said:

You might want to consider gathering all the required data via script (possibly with the help of ExecuteSQL) and passing it to a web viewer (or even to a text field) for display.

That's a possibility - way outside my current knowledge but could be a worthwhile project.

One of the reasons that I started by using the portals is that I'm going to add a button under each one. There's a larger portal (with multiple rows showing the actual orders) and I'm thinking that hitting the button under each dashboard summary portal will change the filtering on the main portal so that users can see the orders that the summaries are flagging up. 

I'll need a long  case statement in the filtering for the main portal, but I can just copy the individual filtering conditions over from the dashboard summary portals. 

[bcooney]

When I build these type of views I created a dedicated dashboard table that is all globals. My refresh script simple runs finds and sets the globals to the counts found and a global “last refresh” timestamp. Typically, the refresh is tied to an onlayoutenter trigger. We don’t use  esql as much as we use find.

The button can nav to the records in a list using the same find that did the count.

but definitely don’t use filtered portals. Performance will just get worse and worse. And complex finds are much easier to create than complex filters. 

[comment]

3 hours ago, Angus McKinnon said:

I wondered about having a "dummy" order record that would always appear

No, no, no!!! I did not suggest having a "dummy" record! That would be extremely bad practice - not to mention that it would not help with the current issue unless the dummy record could somehow pass all your filters.

What I did suggest is using a "ghost" record - that is the future record waiting to be created by entering data into last (or more precisely, the first empty) portal row. To enable this, you must allow the relationship to create records on the Orders side. And on second thought, this cannot work if your relationship is using the X relational operator - so perhaps this is not so much of an improvement over what you already have. 

 

 

[Angus McKinnon]

2 hours ago, comment said:

What I did suggest is using a "ghost" record - that is the future record waiting to be created by entering data into last (or more precisely, the first empty) portal row. 

Ah, that makes sense to me now - thanks. 

Yep, I didn't fancy having a "dummy" record, would have needed to hard-code an exception into every filter.

2 hours ago, comment said:

And on second thought, this cannot work if your relationship is using the X relational operator - so perhaps this is not so much of an improvement over what you already have. 

I've had a look at the relationship graph, and whilst I'm not using the X operator, it seems I've done something similar to achieve the same result. There's a field in each table with an auto-entered identical piece of text, and an = relationship between the two. 

I suspect that was a bodge from my earlier days developing this solution, probably because I wasn't aware of the X operator. 

[comment]

1 hour ago, Angus McKinnon said:

There's a field in each table with an auto-entered identical piece of text, and an = relationship between the two. 

In such case my suggestion can work for you.

Another option is to define a value list using values from the Orders::OrderID field, and an unstored calculation field in the dashboard table =

ValueListItems ( "" ; "Orders" )

 where Orders is the name of the value list. Then you can link this field to the OrderID field in Orders and get rid of the auto-entered "constant" fields.

However, if all your filtered portals have some common criteria - for example, if all show only orders that are due to leave today - then you would be much better off to base your relationship on this common criteria and use portal filtering only to distinguish between the different statuses of today's orders. That would greatly improve the performance of your dashboard, and - even more importantly - the load on portal filters would remain more or less constant, instead of increasing every day until becoming unbearable.

 

Edited January 14, 2023 by comment

[Angus McKinnon]

Reporting back: 

The technique of using the "ghost" record to display a zero value worked a treat. I'd never have thought of doing that myself. 

I ended up using a new relationship for these small portals, so that there weren't any ghost records appearing in other portals where they could have caused confusion. I was also able to use the relationship criteria to restrict the range of orders - bringing the amounts needing sifted by the portal filters from ~10k to <100. It's certainly made a difference to the overall responsiveness.