Filter LOV - Dates - Context Variables

Check if you can sort this one from the documentation

https://docs.ifs.com/techdocs/23r2/040_tailoring/225_configuration/200_client_configurations/310_conditional_fields/200_filter_enumeration_values/?_gl=1*9yj12d*_gcl_au*MTc3OTczNzAwOC4xNzE5NDA3NDg0*_ga*MTg0NDkxOTQ2Mi4xNzAyNDgxMTY5*_ga_H7YJR9R7Q1*MTcyMjk1NjUwMy4yMDQuMS4xNzIyOTU3Mjg5LjEuMC4w

@EntNadeeL thanks Nadeesha, nice to see you here dear ;)

@dfsjmarchant 

You’re talking about dates and enumeration of values at the same time, is this a field where dates are enumerated ? Seems a bit strange at first glance ?

If you can elaborate which field you’re trying to apply a filter on I can try and have a look for you (unless it’s a custom field?)

Hi, 

I’m trying to make use of Context Variables a bit like you do on Lobby. Where you can use variables such as #TODAY# to give you todays date, or #SITE#, or #DEFAULT_USER#.

This way we can filter/hide data in drop downs, in page designer based on logic of current context, i.e. date/time, user logged in etc.

Does that make sense?

In this example, we have data in a drop down list that has a validity period. If that period has passed, we want to filter it out of this particular drop down.

Cheers

James

@dfsjmarchant  Thanks, I’m going to have a look once I get a chance, can you let me know if this is a custom field or core field? If it’s a core field, can you let me know the page it’s on so I can test on it ?

I’m trying to understand whether you’re actually talking about an enumerated field, a lov field, or a normal field, as it might change the way a filter is applied.

First thing that comes to mind is that it’s unlikely the context variables will be usable as is in the page designer as it probably uses other type of syntax etc.

(Notably, filters in page designer typically use Odata syntax, so Im not sure if you can actually get the logged in user there, but dates stuff should be possible)

So you might be able to get away with like something like

Value: ‘ValidTo le now()’

I’ll see if I can figure out something for the other stuff like logged in user, but date should be doable if it’s an actual LOV reference.

Actually it seems it doesn’t look using now(), it might not be supported by the IFS Page Designer stuff, there’s loads of limitations with its implementation:

https://docs.ifs.com/techdocs/23r1/040_tailoring/300_extensibility/100_odata_provider/1000_supported_functionalities/?_gl=1*8s301b*_gcl_au*MTk1NTk3OTA0Ni4xNzIxODkyOTY1*_ga*NTg5NDgzNDE4LjE3MTQyNTM0NjU.*_ga_H7YJR9R7Q1*MTcyMzEwNTgwNy4xMTcuMS4xNzIzMTA2Njg1LjYwLjAuMA..#apply

Obviously if you do have access to client files you could customize them to change the lov selection itself and add the filter at the view level directly, but that might have undesirable impact too if the view is used anywhere else where you DO want all results even those that aren’t valid.

Alright, so if you do an advanced search, you can do stuff like “ValidFrom le ‘#TODAY#’”, so advanced search lets you use (at least some) context substitution variables, but yeah the same bit doesn’t work as a LOV Filter in the page designer.

I’m thinking it’s because that bit of the engine doesn’t know how to translate the CSVs, but apparently advanced search does, which seems like a limitation in IFS for sure.

You  can see in the Payload that while the page designer accepts the ‘#TODAY#’ syntax, the actual payload translates it to nothing :

(where %20%27 is the space character, and anything after (the ‘#TODAY#’) gets replaced with nothing at all, So you end up with a Malformed URI error

@Jonas Feigl Jonas, sorry to tag you in on this, but do you have a confirmation of the above?

Can use CSVs for Advanced Search context, but not as a LOV Filter? (can’t use now() either). Is that intended and a known limitation ? Is there a workaround ?

I think using CSVs is simply not supported but it sounds like a useful feature so I would suggest you create an idea for this. 

The fact that you cannot use “now()” looks like a bug to me as “ValidFrom le now()” is a valid OData filter string so should be allowed in my opinion. So I would suggest you raise a case for this.

SimonTestard wrote:

Alright, so if you do an advanced search, you can do stuff like “ValidFrom le ‘#TODAY#’”, so advanced search lets you use (at least some) context substitution variables, but yeah the same bit doesn’t work as a LOV Filter in the page designer.

 

I’m thinking it’s because that bit of the engine doesn’t know how to translate the CSVs, but apparently advanced search does, which seems like a limitation in IFS for sure.

 

You  can see in the Payload that while the page designer accepts the ‘#TODAY#’ syntax, the actual payload translates it to nothing :

 

 

(where %20%27 is the space character, and anything after (the ‘#TODAY#’) gets replaced with nothing at all, So you end up with a Malformed URI error

 

Thanks for looking at this. Very useful insight. I didn’t see the thread updates. So taken a while to come back to it.

As I was struggling with the issue, I ended up creating a custom field on the CompanyAddress entity, this basically said whether the company address was valid or not. i.e. within the date range where the address is valid:

SELECT (CASE WHEN (To_Date(:Valid_To) > sysdate OR :Valid_To IS NULL) THEN   'TRUE' ELSE  'FALSE' End) IsValid FROM dual

So the above gave me a simple true or false to filter the list by, which has worked, however, the original question would still be good to answer, as it feels like it could/will have useful applications in future.

See below where I set the data up, and the Is Valid badge shows the field above I’ve defined.

Any update on this would be good guys :)

Thanks

Your workaround definitely works, it’s just a hassle to have to create a custom field for every entity you want to be able to do a LOV filter on based on sysdate ;)

Basically Jonas thinks it’s a bug that you can’t use now() (in fact, you can’t use now() in advanced search either, but ‘#TODAY#’ works in advanced search), and that we should raise an Idea for getting CSVs to work in LOV Filter syntax.

You can raise a case about not being to use now() (either as LOV Filter or Advanced Search syntax) as it’s valid Odata Syntax but it might take a while for IFS to address and possibly issue a correction for it

You can also raise an Idea for getting to use CSVs in LOV Filter Syntax as CSVs are simply not supported there (for now at least)

Regarding the workaround: The logic of a custom field will be put inside a Get-method and then filtering for the value of that Get-method (i.e. having a Get-method in the WHERE condition of a Select) is incredibly bad for performance and system load as it will lead to that method having to be executed for every single record in the table! So I would really recommend that you raise a case for the issue and do not use that workaround if possible.

@Jonas Feigl , 100% on the performance thing, now I guess the impact is probably fairly limited if you do it on a table where there’s typically very few rows (basic data kind of table), but is absolutely horrendous if done on a large table as yeah the SQL engine has no choice but to run the select statement on every record in order to evaluate the condition being met or not.

I remember you teaching me about creating null checks when using parameters in the WHERE conditions of quick reports/lobby data sources (such as (nvl(‘&parameter’,’%’) = ‘%’ or field_name = ‘&parameter’)) for exactly the same reason too ;)

Hi Guys,

It would be nice to get the context variables working on filters but unfortunately only odata filters work there as mentioned several times in the thread already, Not the best alternate solution, but one workaround to use CSVs on LOV default filter criteria is to set a saved filters. 

1. Create a Saved filter for the LOV using CSVs

Then set the filter as the startup behavior for the LOV

This will start the LOV with the defined filter which uses CSVs

This would save few clicks for the user if the filter needs to be used more often :)

Cheers!

Damith

That’s really cool thanks @dsj . Do you know if those filters are a “per user/profile” kind of thing, or can they be deployed to all users as a general startup behavior no matter who’s logged in?

Hi @SimonTestard 

It will be added to the users’ personal profile. Still it cannot be added as a common setup for all users in IFS Cloud.

Check and upvote this idea which is created regarding bringing back the base profile functionality.

Return base profile functionality to IFS Cloud | IFS Community

@SimonTestard @dsj @dfsjmarchant 

I have followed up on this for you and this was a bug in the filter string parser used by the Aurena client not recognising the “now()” and “date(...)” functions . This will be fixed in 24R2 EA.

That’s awesome, thanks @Jonas Feigl 

Do you per chance know if that bug fix for the filter string parser thing will affect (and resolve) using now() for both the Advanced Search Context AND LOV Filter Cases, or just the latter ? :)

I’m happy to confirm that it fixes it for both as they use the same OData Filter Parser!