UDT Handler Filters
Answered
Hi, we're thinking of using the UDT handler to manage access to records linked to the item master, this is fine and we're using the Foreign Key functionality to filter for the item code. However there is no way of putting additional custom filters. We have injected some filters under the Advanced button.
We've noticed that the foreign key functionality is always "equals" it would be nice to select the operator here to allow for date ranges and sub selects, is this possible?
Also If additional where clauses are placed under the "Advanced" button is there a danger that these would be be made redundant in later releases???
If you could share your road map for the UDT handler that would be great.
Matt.
-
Thank you for your request. It has been scheduled for review by the development team that will get back to you should there be questions.
(Please note that as we are user-driven we can't guarantee that your request will be met unless it gets many votes and/or fit the product vision)
Read more about the Feature Request process here
[Internal Id: 16432] -
Hi Matthew,
Currently there are no plans to expand the filtering options, as the WHERE condition is the most flexible way of allowing people to do custom filtering.
Are we perhaps misunderstanding your request? What is the business case you wish to use this functionality in?
-
The business case is that we will have millions of records in a udt where the main key is the itemcode. The UDT handler can be launched from the item master and filtered on ItemCode, we know this. However this will still bring back 100,000's of rows. So to further filter we are looking at additional filters based on where clauses other than "equals", or even the need to use a sub-select to aid with filtering.
We have had some success with adding to the where clause under the "Advanced" button, my question is, is this the best method to achieve this. We want to filter the results shown by the UDT handler rather than using the "Foreign Key" functionality.
What options do we have?
-
There is nothing wrong with using the Where condition in the Advanced button... It will never go away or anything, even if we someday would add other filters.
That being said, based on your description with 100.000+ records this (UDT Handler) might not be the right tool for the job... If a user needs to get an overview of 100.000 records (if that is even humanly possible) then IMHO you need a custom tool to achieve this.
Could UDT Handler show 100.000 rows... Yes; but the more important question is: Should it/Does it give the user a good solution?... We have always seen it as a tool to take care of the small data-set, but despite me de-selling my own product here I'm a firm believer of "The right tool for the job" and UDT handler is IMHO not the right tool for the amount of data you mention here.
-
I agree.
With the additional filtering against the "advanced" button we would expect approx 70 or less records being retrieved. Which makes the UDT Handler quite effective. Also the user would only be updating not adding or deleting rows.
Thanks.
-
OK... 70 rows seem more manageable so sounds like your solution is the Advanced button IMHO
-
Hi Matthew,
I have a similar business case where I would like to filter some data for security reasons (input screen for budgets, which need to be filtered based on the departments the user has access to).
You mention this: We have injected some filters under the Advanced button.
Would you mind sharing how you accomplish this? To my knowledge, the Advanced button only supports ORDER BY clauses. I tried adding a WHERE clause but it throws an error.
Thank you in advance.
Regards,
Andy
-
Adding a simple where clause work fine for me...
Are you sure you write the WHERE clause correctly (what is the error message?)
-
Hi Rasmus,
My WHERE clause is a bit more complicated :)
WHERE "U_BU"||'/'||"U_DeptCode" IN (select "AccessCode" from UNI_V_FIN_BUDGET_SECURITY where "UserCode"= (SELECT T0.USER_CODE FROM OUSR T0 WHERE INTERNAL_K = $[USER]))
Error: SQL could not be parsed
I assumed the issue might be the double subselect or the usage of the $[USER], but even a very basic WHERE clause fails:
My guess is that this method doesn't support the double quotes for the field names (we are on Hana DB), but that's just a guess.
Any idea?
Thx
Regards,
Andy
-
By the way, could you tell me which tool you use to see the SQL? I have been looking for something like this for a long time. Is it included in B1UP?
Thx
Andy
-
We for sure do not support dynamic keywords in there... But I can also see you use the Foreign key system for DptCode and VersionCode... Using that it creates also a WHERE condition and I think that is where things break (you can't say "WHERE" 2 times)... It might work if you instead write "AND" instead of WHERE, but to best find out use the Debug Console (your second question) to get the SQL that is actually executed: https://youtu.be/M39ff4Iofho
-
Using AND instead of WHERE indeed allows me to add a simple condition. The one with dynamic syntax still doesn't work, but you mentioned this. I do need this to allow dynamic security, based on the logged on user, but I guess we'll have to live without it, no problem. Maybe something for a future release ;)
I will have a look at the debug console also, thanks for the tip. Only been using B1UP for 9 months and still discovering features ;)
Please sign in to leave a comment.
Comments
12 comments