/ Development  

The power of filtering out a list-panel OR a repeating group/grid

Hi there AppWorks fans,

Welcome to a new installment of AppWorks tips.

It’s again a colleague (the “Dinosaur”) triggering this post. Why? Well, it gave a fresh look at an always very unclear phenomenon. Do you recognize this screenshot?

flt_001

  • If No? Just continue reading, and you learn it as we continue the post!
  • If Yes? You have the same brain-crush as I have…where to start thinking on this filter and how to build it!?

Time to finally nail this “filter option” till the bottom…

Let get right into it…

Right, where do we even find this filter option? At two places:

  1. Adding a result-list panel on the layout of an entity
  2. Adding a to-many relationship (via group or grid) on the form of an entity

What about a result-list panel on a ‘Home Page Layout’ type of document? Yes…Not possible! For a reason too, but you’ll see; It all has to do with the “current” entity which you don’t have “in context” on a homepage! 😉

Let’s build something to play with and change our thinking mindset on this filter. We start with a ‘Project’ entity and just one ‘Text’ property prj_name. Generate all the default BBs and make it nice where needed. Our second entity is ‘Task’; again, with just one property tsk_name. Our ‘Project’ will have a ‘toMany’ relation to ‘Task’. The ‘Task’ entity will have a ‘toMany’ relation to ‘User’ (of the platform identity package) and will also have a ‘hasChild’ relation to a new entity with name ‘Todo’ (including a todo_name property!). For a complete play field, we also add a ‘toOne’ relation from ‘Task’ to again our ‘Project’…Is this possible!? Yes, it’s called a “bidirectional” relation!

In the end our Entity R*elation *Diagram will look like this:

flt_002

Draw IO is your place to be for a diagram like this!

Save it all, and you can publish it already…After this we can continue our grind in two directions; The layout, or the form…

The layout-part

Open the layout of the ‘Project’ entity, drag a ‘Results’ panel next to the current ‘Form’ panel and select the ‘All tasks’ list BB of the ‘Task’ entity (or whatever you named it!). Just like this:

flt_003

Publish it and see if you can see the result in runtime? First create a ‘Task’ entity-instance and open a new (or existing) ‘Project’ instance after it. I have a view like this now:

flt_004

Now, go back to the designer and click the filter icon (from the above screenshot…The one above the above! 🙃) for our unclear phenomenon modal filter panel. Now what to fill in here? Yes, my friend…Great you ask, but first a recap on what my colleague “the Dinosaur” had to say:

…
!!START THINKING FROM THE RESULTLIST!!
…

Right, our result list panel on the layout shows ‘All tasks’. So, what property do we have on that specific entity? Well, that will be our tsk_name. So, we start our filter with Properties.tsk_name=; On the right-side of the equal sign, we’re in the land of our current entity which is our ‘Project’ entity instance with property prj_name. So, we can complete our filter like this: Properties.tsk_name={item.Properties.prj_name}.

A “ReThink”: The property you’re filtering on (on the left-side) must be available in that list; Otherwise, you get an error like this: Parameter not found in the view.. If the property on the right-side is not found for the current entity, it will still show all the results. It’s also not required to have that property of the current entity (right-side of the filter) viewable on the form of the entity…Just that you know!

Do a publication and try it out in runtime! Your task list will be empty…why? Well, because we don’t have a task entity that equals the value of our current project instance! Yes, read that line again…You can fix it in these two ways:

  1. Make sure to create a task entity equals to the name of the project
  2. Create a project that equals one of the task entity-instances

Great stuff, but a nonsense example; We can do better!…How about adding the ‘toOne’ project prj_name property to the result list of our ‘Task’? Like this:

flt_005

If you also make sure you can select a related ‘Project’ entity instance on your ‘Task’ entity in the ‘Create’ form, you can update the filter like this: to_one_project.Properties.prj_name={item.Properties.prj_name}. I see it’s also possible to use this: to_one_project$Properties.prj_name={item.Properties.prj_name}. Watch the $ sign in there! Where did I gain this wisdom?…See for yourself before applying the filter:

flt_006

For your reference, the ‘Create’ form update for the ‘Task’ entity:

flt_007

Publish our knowledge and try it out in runtime! Don’t forget to make the relation to a project entity instance from one of your task entity-instances…This is your result:

flt_008

NICEEEEE…Tell me more, tell me more! Well, how about doing it the other way around? So, something like this…I guess?: Properties.tsk_name={item.to_many_task[0].Properties.tsk_name}. Hmmm, it’s not working!? My curiosity puts me one step back (just a try-out) with a second ‘toOne’ relation from ‘Project’ to ‘Task’. With this relation in place (also on the ‘Create’ form of the ‘Project’ entity), I can make the conclusion that this does work: Properties.tsk_name={item.to_one_task.Properties.tsk_name}. Looks like there is nothing wrong with our mindset, but the platform is working against us here!

Check this forum post about the above topic and any further comments.

Let’s continue…This time for the form-part!

Wait, just one note…It’s also not required to have the ‘toMany’ or ‘toOne’ relations from ‘Project’ to ‘Task (right-side of the filter) viewable on the form of the entity…Just that you know! Only, it’s almost required to have them on the form to make the relation in runtime a fact!

The form-part

This part is the same, but ‘smells’ different…Open the ‘Create’ form of the ‘Project’ entity and add the ‘toManyTask’ relation to the form. On the created ‘Repeating group’ component, you can add the related tsk_name property. From the right-panel (of the ‘Repeating group’), you can select the ‘All tasks’ browse list. Like this:

flt_009

Now, check the red-arrowed filter icon and see what options we can do here:

Filter as is (like on the result-list)

flt_010

Filter with expression editor

flt_011

Filter with advanced editor

flt_012

…

Let’s go for the ‘old’ way with a ‘Dynamic’ filter like this: to_one_project.Properties.prj_name={item.Properties.prj_name}. Recognize this filter? Indeed, it’s the same one from the previous section. Does it work like before? Hell yeah!! Now the great question! What will happen when we make the switch to the new editor!? Will is convert our filter?…hmmm, to bad! It’s not, but what will happen after reconstruction (with a non-dynamic value!):

flt_013

Hmmm…That’s a “Stranger thing”! The column is there, but it is not “seen”!?

flt_014

A bug? A feature? Do I miss something here? Let me know in the comments…

I get the same error item.Properties.tsk_name - Parameter not found in the view. creating that same filter on tsk_name like this: item.Properties.tsk_name=="test001"? It’s getting stranger by the minute as it smells like out-of-the-box functionality to me!?
…
WAIT…My bidirectional light starts to burn!! Let’s go back to the root of the platform and make the ‘toMany’ relation from ‘Project’ to ‘Task’ non-bidirectional! For this to happen, we need to remove it first and recreate it again. After 10 min. of playing around, I still get the error; also, after removing the solution from ‘/app/admin/‘ and cleaning the build output…strange!? I even tried recreating two new entities, with the same result!? SUPPORT?

…

HOLD, STOP, WAIT…After a review of my own post, I see what is wrong here…Did I find a bug? I see it starts with item., but it should start with Properties. or in my related example to_one_project.Properties.!? hmmmm…Still strange as it is reproducible over and over again.

…

Let’s continue our grind

Filter functions

Nice, now we’re getting somewhere. Let’s go back to our filter on the result list panel of the ‘Project’ default layout. Let’s first try out these hard-coded values:

  • to_one_project.Properties.prj_name=Project001; No quotes required here!
  • to_one_project.Properties.prj_name=Project 001; So, even a space in the value will work.

Now for our first ‘equals’ function; No quotes required to pass a value in the eq function!:

  • to_one_project.Properties.prj_name=eq(Project001)
  • to_one_project.Properties.prj_name=eq(Project 001)

Can we do more? For sure:

  • Like function with multi-char-wildcard: to_one_project.Properties.prj_name=Like(Project*1)
  • Like function with single-char-wildcard: to_one_project.Properties.prj_name=Like(Project??1)
  • Not-equals function: to_one_project.Properties.prj_name=ne(Project001)
  • In comma-separated list function: to_one_project.Properties.prj_name=InList(Project001,Project002)
  • Not in comma-separated list function: to_one_project.Properties.prj_name=NotInList(Project001,Project002)

The wildcard * is database dependent; If it’s not working try %!

WE WANT MORE; WE WANT MORE…Ok, but for this we need extra properties; One ‘Integer’ tsk_points, and one ‘Date’ tsk_start_date. I make them available on the ‘Task’ entity create form and the ‘All tasks’ list BB.

  • Less then: Properties.tsk_points=lt(4)
  • Greater then: Properties.tsk_points=gt(2)
  • Less and equals then: Properties.tsk_points=le(3)
  • Greater and equals then: Properties.tsk_points=ge(3)

How about this function: Properties.tsk_points=Between(0,10)! You also have these functions Properties.tsk_name=Null() and Properties.tsk_name=NotNull() (according to the documentation!), but I could not find out how they work on any property; I keep getting the error There is no value with name 'Null' in Enum com.opentext.cordys.entityCore.orm.querySupport.ComparisonOperator!? A quick search on my VM:

sudo find /opt/tomee/apache-tomee-plus-8.0.12/webapps/ -type f -name '*.jar' -print0 | xargs -0 -I '{}' sh -c 'jar tf {} | grep ComparisonOperator.class && echo {}'

…brings me to the file home#app#entityRestService/WEB-INF/lib/entityCore.jar. With a little decompile magic we can see why it’s not working:

flt_015

So, switch gears with Properties.tsk_name=IsNull() and Properties.tsk_name=IsNotNull()

You can also replace the above ‘Integer’ checks with the Date property tsk_start_date looking like this: Properties.tsk_start_date=le(2022-09-30Z). Read here about that date format value!

It’s getting even more interesting making our “static values” dynamic…Like this:

  • to_one_project.Properties.prj_name=eq({item.Properties.prj_name})
  • to_one_project.Properties.prj_name=Like(*{item.Properties.prj_name}*)
  • to_one_project.Properties.prj_name=InList({item.Properties.prj_name})

With this knowledge in mind, I also try Properties.tsk_name=InList({item.to_many_task.Properties.tsk_name}); It fails for some reason? See the comment in the forum-post.
Another pleasant thing I tried is this hybrid option: to_one_project.Properties.prj_name=InList({item.Properties.prj_name},Project002)

Nice, you have sufficient input to find your way on correct filtering out a result! Read more about these function in the “Defining a filter” section of the low-code guide of the platform! Let’s see another topic…

Deeper relation to ‘todo’ entity

Remember the ‘hasChild’ relation from ‘Task’ to ‘Todo’. So, when we add the todo_name to the ‘All tasks’ list, can we do something like this: todo.Properties.todo_name=eq(todo001)!? Nope…why? Well, you cannot show a repeating property like this in the column list; Only in the filter list:

flt_016

Hmmm…That’s the same reason ={item.to_many_task.Properties.tsk_name} is also not working!? Leave a comment if you’re still with me! 😉

How about the ‘toMany’ user relation from the ERD in the beginning of the post? Well, same reason! Ok, how about another ‘toOne’ relation from ‘Project’ to another entity!? Yes, my friend…I leave this one for you! How deep can you go, and can we even go deeper!? That’s the question!

Right!…I give it a “DONE” for this post where we learned about filtering out a result list in runtime. It has limitations on a repeating property, but it comes with a “Basic” list of functions for you to use in your filtering features. It would be great to have an extension on that list with functions like toUpper(), toLower(), or a more powerful function matches() for regular expressions; It’s just a thought, do what you need to do with it!? I would say; Have a great weekend, and I see you in the next one…Bye, bye!

Don’t forget to subscribe to get updates on the activities happening on this site. Have you noticed the quiz where you find out if you are also “The AppWorks guy”?