Portal Filter

[Angus McKinnon]

I'm having a frustrating time with some portal filtering. 

What we have is essentially a calendar layout. Each day has a portal, filtering records based on the order date. This means that users can see the orders due on each day at a glance, and this has worked well for many years. 

Recently, business is getting busier and it would be helpful to filter the portals by the type of order as well. 

I've started by adding three Global fields to the Calendar table, which are essentially toggles that the user sets for what they want to see. These correspond with three fields in the order table. All are text fields populated with "Yes" or "No"

I'd assumed that all I'd need to add is a Case statement, which seems to have worked elsewhere in my solution. Along the lines of:

 

Case(

Calendar toggle A = "Yes"; Order Type A = "Yes";

Calendar Toggle B = "Yes"; Order Type B = "Yes";

and so on...

 

This fell down because if multiple toggles are selected, everything gets filtered out. 

In theory all orders should only have one field being "Yes" (they are text calculations based on the status of line items in a related table) however there are some outliers that have two. None can have all three. 

 

My next step was to build a "toggle status" field which returns a number based on the combination of toggles. So, toggle status returns "1" if all three toggles are on, and "2" if only the first is selected, etc. 

 

Case (

Toggle status = 1 ; Order Type A = "Yes" or Order Type B = "Yes" or Order Type C = "Yes";

Toggle status = 2; Order Type A = "Yes";

 

This doesn't seem to be working either. What seems to happen is that the portal is filtered in line with the first Case result, irrespective of what the toggle fields (or status field) is doing. 

 

I suspect I've overlooked something but I'm baffled as to why this isn't working.

[comment]

2 hours ago, Angus McKinnon said:

Case(

Calendar toggle A = "Yes"; Order Type A = "Yes";

Calendar Toggle B = "Yes"; Order Type B = "Yes";

and so on...

I believe it should be something like:

Calendar::Toggle A = "Yes" and Order::Type A = "Yes"
or
Calendar::Toggle B = "Yes" and Order::Type B = "Yes"
or
Calendar::Toggle C = "Yes" and Order::Type C = "Yes"

(untested).

 

2 hours ago, Angus McKinnon said:

All are text fields populated with "Yes" or "No"

You will get better performance and simpler calculations if you use Number fields for Boolean attributes (1 for True,  0 or empty for False). Note that such fields can be formatted to display as "Yes" or "No".

(Although if those are really types of order, I would expect to see a single field formatted as a drop-down list or pop-up menu on the Orders side (assuming these types are mutually exclusive) and a checkbox field on the Calendar side.)

 

 

Edited July 2 by comment

[Søren Dyhr]

While Comment is right in his suggestion, would scaling issues sooner or later occur, portal filters is only for limited measures of data. Here is it the number of orders likely to be the first area where quirky performance arrive. 

Watch this video: 

https://youtu.be/iSPYgx8woYA?si=-oEJK0At5y6NXKjk

--sd

[Angus McKinnon]

Only realised when logging back in again today that I hadn't replied to this thread. 

Comment's solution fixed the problems straight away - as I suspected I hadn't got my logic straight in the Case statements. 

 

Number fields for Boolean results like this hadn't really occurred to me, but I can see how it makes sense. Unfortunately I have "Yes/No" text fields scattered throughout my solution so it'll take a bit of time to weed them out.

I'm curious, though, if adding another field for display - (essentially a calculation saying "if <boolean field>=1 then "Yes") will just add some of that overhead back in?

 

I really went to town with Portal Filtering when I first discovered it, and am now beginning to hit some of the limitations. Thankfully we have a relatively small number of records and limited users but I suspect some of my filtering is just too convoluted to execute quickly.

[comment]

3 hours ago, Angus McKinnon said:

I'm curious, though, if adding another field for display - (essentially a calculation saying "if <boolean field>=1 then "Yes") will just add some of that overhead back in?

You don't need another field for display. You can format the Boolean field itself to display as as "Yes" or "No" or any other (short) strings:

 

[Angus McKinnon]

7 minutes ago, comment said:

You can format the Boolean field itself to display as as "Yes" or "No" or any other (short) strings:

Excellent! I was completely unaware of this option.