Hello everyone,
I would like to solve this issue I recently have had when trying to set a default filter within a record type. The problem is basically that I can't set this filter by the owner (List of strings) field when it includes the loggedInUser(). I'm setting the filter exactly as I described before and it doesn't solve the mentioned problem, it keeps throwing the error over and over no matter even what operator I use (have tried with IN, = as well). I would thank to anyone who can give me and suggestion to solve this problem.
Note: I already know it has something to do with relational database relationship which creates a new table when setting a List of Strings as a child value in a parent table and within this new table it storage a reference to the father table. It seems like a!queryFilter() (which is the used function in these cases) doesn't make the usual map of hibernate to turn the Relational data to Object data and it gets impossible to make this filter. Nonetheless, if someone has any way to solve this problem, I would like to see it and will be thankful for that.
Discussion posts and replies are publicly visible
Unfortunately we cannot apply a!queryFilter() on a child CDT field. Typically what I would do here is create a view for the record that utilizes a SQL STUFF query to bring the child data into one field in the parent row, delimited (with delimiters prior and after) so that an INCLUDES filter can be utilized such as concat(",",loggedInUser(),",").
ownerField: ",john.smith,tim.jones,jane.doe,"
a!queryFilter( field: "ownerField", operator: "includes", value: concat(",",loggedInUser(),",") )
Note there is a scalability concern with this approach, if say you are concatenating an ever-growing list of record IDs, etc. However in situations where say the 'owner' list will always remain manageable, this should work fine.
An example of the STUFF query in the view would be similar to:
ownerField = STUFF ((SELECT ',' + a.owner FROM ownerTable AS a WHERE a.pId = mainTable.id FOR XML PATH('')), 1, 1,''),
Thank you! I really appreciate your help and your effort in detailing this solve with examples, thank you so much for that. I think this could work for me. I'm going to try it soon and let you know the results.