Where clause creation

[wes1278]

We want to make adding the where clause an easier process.

My thoughts are the following:

• [ ] Create a new field which allows typing the filter logic similar to that of report criteria ie 1 AND (2 OR 3).
• [ ] Write a method that injects criteria into this filter logic. ( we should be able to pass this method a list of strings that represent each filter criteria. If the injection is done correctly, we shouldn't have to worry about parenthesis at all. We should first replace the digits 1, 2, 3,etc... with some longer placeholder strings like CRITERIA_1, CRITERIA_2, etc... so that we can do a nice string replacement methodology and don't run into issues with fieldnames or values containing #'s.
• [ ] Write some repeatable vf component that has an index, child field selection list, an operator select list, and a text input box to type your comparison value. (The page should have an add button to inject new blank versions of this component to "add criteria")
• [ ] When we save the record, we just need to inject the string output of the repeatable vf component filter criteria into the filter logic and voila, we have a where clause.
• [ ] Hard part about this is going to be the parsing method if we want to extend this page to be used for editing as well as creation. This parsing method will need to take the where clause, and first parse out what the filter logic should be. We'll need to look for AND, OR, ( and ) and do some fun logical operators. Then we'll need to create as many vf components for the filter criteria and default each of their select lists and the comparison values. EEEk. this is going to be kind of hard.
• [ ] We still have some additional work to make this page "edit-worthy" with defaulting the select lists etc...

Future Enhancements might be:

• [ ] Make the selection of the child field make the right side input box have nice selectors based on data type. So, if the child field was a date, it would give us a date control. If it was a date time, we could use a datetime control.

[afawcett]

Andy: Looks good, I imagine something like Iistview screen yes? Wes: yeah. I think so. Just self created via pageblocktable. I don't want to use an actual object. Andy: Yep agree, also familiar UI design for users. Makes me wonder if we can read list view defs, get user to specify name as option?

[afawcett]

Just got a new enquiry via my blog about this availability of this feature, any new thoughts on moving it forward?

[wes1278]

My thoughts are: I'd LOVE to, but like yourself, been swamped lately. Plus we've got Dreamforce coming up and the planning/preparations for my company are pretty extensive.

Perhaps we could work on this together after dreamforce?

[afawcett]

Yep, life after Dreamforce sounds good! :+1:

[wes1278]

@afawcett I was thinking about this recently and I thought we might be able to do some slick stuff by implementing a json editor to store the criteria we're looking for. https://github.com/jdorn/json-editor is one I've been particular fond of.

The idea would be we store a .json file on the rollup record and all the information we care about is stored in that json record. It would be cool to use JSON schema to enforce validations and structure. Then, we'd have to implement a JSON parser to get use the information we need at runtime to be used in the trigger.

What do you think?

[afawcett]

Yeah that looks like a great starting point, maybe do a mockup and share on the Chatter group / Twitter to get further feedback? It seems like an area that needs some kind of UX feedback on before going into to much development effort.

[jondavis9898]

Hello @wes1278 & @afawcett - I've encountered a similar need when working on #216. In short, the need to store strongly typed data and use it in generating the necessary SOQL. In order to support backwards compat for #216, I need to allow free-form input and parse. However, what I'd like to do is create a custom object (or even custom metadata if DLRS goes that way) to track the "order by clause" in a triplet of (field, asc/desc, nulls first/last). This would allow reuse of defined "order by clauses" across all rollups to ease administration and also avoid the runtime cost of parsing strings. I would see this working with a custom VF page that would allow the user to pick from a list of fields, specify order, etc. The user could add one or more portions to the order by and then at runtime, it's just generating the string from the record list. I could see the where criteria working in a similar manner.

In my limited experience on the platform, I find that working with JSON is troublesome from a couple of perspectives: 1) Makes writing tests more difficult because you need to handcode the json; 2) CPU cost of serialization/deserialization.

I think if DLRS has an admin interface around these features, it would improve usability for admins significantly (especially for those that are not familiar with SOQL) and also likely result in increased performance, less overall and more reliabe code than what we have today.

@wes1278 - Curious why you mention above wanting to avoid a custom object to store the where clause. Outside of the downside of additional Query & QueryRows encountered, are there reasons why you preferred the JSON approach over custom object?

[wes1278]

@jondavis9898 First of all, I want to thank you as another contributor of DLRS that you're work to make this tool, imho, more enterprise worthy is very much appreciated! I've been wanting to take a deep dive into the guts of this tool for a long time but time was just limiting.

The only reason I want to stay away from traditional custom objects is to prevent excessively difficult org migrations. Right now, we're already having people have hard times migrating from sandbox to prod. I had previously proposed using Files to store the raw json and then use json-editor by jdorn on github to parse and present a UI. I have a working prototype and it's fantastic. But I'm also not at all opposed to custom metadata, especially with a hopeful UI in winter 16.

[jondavis9898]

@wes1278 - Happy to be a part of the team and help continue the efforts of yourself, @afawcett and everyone else that has contributed. DLRS is an incredible tool and will only continue to get better through the contributions of the community.

Completely agree that custom objects complicate org migrations and I see how using the JSON approach would help avoid increasing the difficulty. Still coming up to speed on the platform, so just wanted to make sure there wasn't some other reason that I wasn't overlooking. I haven't dipped my toes in to custom metadata yet but I think it's likely the best route to take DLRS in. Currently, I manage org migrations using data export/import of Lookup Rollup Summary within our CI process. While it lessen's the burden, it's still difficult to manage change and migration and I think custom metadata could simplify and significantly improve. Whichever direction DLRS takes, I think having a more robust admin interface will be critical to helping adoption, etc.

Given my limited knowledge of custom metadata, you and @afawcette (or other community members) will likely be better to identify a direction to take. Possibly we identify a path forward (JSON, Custom Objects, Custom metadata) and try to start working on those enhancements? @afawcett thoughts?

In the meantime, any chance you would be willing and able to share your JSON prototype?

[afawcett]

Great discussion guys, i'm proud to see others taking such a deep interest in the future of this tool. So yeah, i do have a vision for custom metadata following my two part blog series using this tool as a POC. The Custom Metadata UI in Winter'16 is still basic however and there is no trigger support. So i feel we will need to really bring the existing VF UI into prime time to make the transition. I've got some thoughts on how to make the changes and leverage the patterns to isolate major reworks. This plan see's a wholesale switch to custom metadata, though i do have an idea to have an import/export to custom metadata as an earlier approach that could be adopted.

[jondavis9898]

Awesome, looking forward to your thoughts @afawcett and to digging in to custom metadata. Completely agree that enhancing the VF UI is critical, I have some ideas on this (eluded to above) when we get there.

[afawcett]

Yeah i've started a new enhancement (sure i had one already but it seemed not) to start to elaborate.