15 Replies Latest reply on Jan 9, 2009 7:48 AM by kenji776

    GUI Query Builder for Users

    kenji776
      Hey all,
      For anyone who was following my last post, I did convince my boss to buy CF, so epic win there. I am now starting to replace some our existing software with CF based code. One of the first things we would like to do is setup a "Query builder" type of thing. Basically we have a database with over 100 fields in a particular table. I need some kind of interface that will allow a user to easily build a complex query that can include any number of those fields. Before I start reinventing the wheel I was wondering if anyone had code for something like this already or could point in the direction of a place that might. All it needs to do is put a nice front end on an SQL query basically, and really only for the where statement part of it. We are always going to be selecting the same info, from the same table, but what records we select will be very very different. Thanks in advance.
        • 1. Re: GUI Query Builder for Users
          Dan Bracuk Level 5
          Your post has many contradictions.

          On the one hand, you want the user to be able to build a complex query that can include any number of fields. On the other, you say, "We are always going to be selecting the same info, from the same table, but what records we select will be very very different", in other words, a simple canned query.

          Depending on what you really want to do, it could be as simple as a bunch of check boxes for the select clause and some other inputs for the where clause.
          • 2. GUI Query Builder for Users
            kenji776 Level 1
            Dan,
            I know my posts seem a little goofy. It basically boils down to, I have users that are dumb as a box of rocks, who need to be able to build somewhat complex queries.

            what I mean by selecting the same fields is that we are always interested in retreiving the same SET of data from the same table. However, what records we want exactly will be very variable. Basically

            SELECT * FROM CONTACTS
            WHERE [A BUNCH OF CRAZY SH**]

            In this case, complex just means a ton of WHERE statments, not so much doing cross table joins or anything. I think I am making decent progress so far actually. It's easier than I thought it might be. Still any suggestions are appreciate. Thanks!
            • 3. Re: GUI Query Builder for Users
              kenji776 Level 1
              Okay, I'm a bit stuck. Basically this tool is to query against Salesforce and build exportable lists. Right now my page finds all fields that exist on the Contact object, and loops over the structure (which i turn into an array) and creates form fields for every updatable peice of data. There are checkboxes, text areas, numeric inputs, and string inputs. I would like to group similar inputs to make it look a little more presentable. Does anyone know how I can sort an array by the value of a certain element? In this case #FormItems [4]# holds the datatype (int, float, textarea, string, etc). I would like to sort my array based on that. Attached is the code for the query builder.

              • 4. Re: GUI Query Builder for Users
                Level 7
                kenji776 wrote:
                > I would like to group similar inputs to make it look a little more presentable.
                > Does anyone know how I can sort an array by the value of a certain
                > element?


                What about using a structure around your array to do the grouping while
                you are creating your array. I.E. something along these lines:

                <cfloop Collection="#oContact.Results#" item="key">
                <cfset temp[1] = #oContact.Results[key].label#>
                <cfset temp[2] = #key#>
                <cfset temp[3] = #oContact.Results[key].updateable#>
                <cfset temp[4] = #oContact.Results[key].type#>

                <cfset arrayAppend(FormItems[oContact.Results[key].type],temp>
                </cfloop>

                With the appropriate initializing code this should create a structure
                that looks something like:

                FormItems['textarea'][1][1] = label
                FormItems['textarea'][1][2] = key
                FormItems['textarea'][1][3] = updatable
                FormItems['textarea'][1][4] = type

                FormItems['textarea'][2][1] = label
                FormItems['textarea'][2][2] = key
                FormItems['textarea'][2][3] = updatable
                FormItems['textarea'][2][4] = type

                FormItems['float'][1][1] = label
                FormItems['float'][1][2] = key
                FormItems['float'][1][3] = updatable
                FormItems['float'][1][4] = type

                FormItems['float'][2][1] = label
                FormItems['float'][2][2] = key
                FormItems['float'][2][3] = updatable
                FormItems['float'][2][4] = type

                etc
                • 5. Re: GUI Query Builder for Users
                  Dan Bracuk Level 5
                  quote:

                  Originally posted by: kenji776
                  Okay, I'm a bit stuck. Basically this tool is to query against Salesforce and build exportable lists. Right now my page finds all fields that exist on the Contact object, and loops over the structure (which i turn into an array) and creates form fields for every updatable peice of data. There are checkboxes, text areas, numeric inputs, and string inputs. I would like to group similar inputs to make it look a little more presentable. Does anyone know how I can sort an array by the value of a certain element? In this case #FormItems [4]# holds the datatype (int, float, textarea, string, etc). I would like to sort my array based on that. Attached is the code for the query builder.



                  Did you change the topic? Your 1st couple of posts make it sound like you want to make a query builder, now it appears that you want to write a record updater. Or maybe I'm the one who's dumb as rocks.

                  In any event, this approach might be simpler.

                  <cfquery>
                  select field1 afield1
                  , field 12 afield12
                  some more of fields with alias names beginning with a.
                  , field2 bfield2
                  , field87 bfield87
                  etc
                  </cfquery>

                  Then you can loop through your query columnlist and build your form fields. You use the 1st letter of the list element to determine what type of form field it will be.

                  • 6. Re: GUI Query Builder for Users
                    kenji776 Level 1
                    Dan,
                    I can see how you might get confused, I am a rather poor writer, and I barely know what I am doing! Regardless, it really is a query builder. It only looks like a record updater because I have to query Salesforce with exactly the same data types as it expects on record creation. So I basically have to recreate the data entry form, only instead of creating a record with the specified info, I am querying for one that matches. I hope I am making sense, cause I do in my own mind :). The only reason I am pulling data from salesforce to build the query form instead of making it static is so that if a new field is added in salesforce, it instantly becomes available in my query builder, no manual updating of source code required. Thanks for the input, and sorry about the confusion. If you are interested, this is my demo app...
                    http://www.digitalswordsmen.com/cfschedule/SFintegration/querybuilder.cfm
                    It doesn't do anything yet besides build the query form (poorly).
                    • 7. Re: GUI Query Builder for Users
                      kenji776 Level 1
                      This line
                      <cfset arrayAppend(FormItems[oContact.Results[key].type],temp)>

                      Throws this error
                      "The value picklist cannot be converted to a number."

                      Which makes sense, trying to use a string there is going to cause an error. I just haven't used structures much and am unsure how to resolve this.

                      Also, doing it this way, wouldn't I require a different loop for every type of item, and an IF statement inside the loop that only outputs the desired type? Doesn't seem very efficient, but then again I'm probably to dumb to understand what Ian's original intent was.
                      • 8. Re: GUI Query Builder for Users
                        Level 7
                        kenji776 wrote:
                        > This line
                        > <cfset arrayAppend(FormItems[oContact.Results[key].type],temp)>
                        >
                        > Throws this error
                        > "The value picklist cannot be converted to a number."

                        Which part of that returns the string 'picklist'?

                        >
                        > Which makes sense, trying to use a string there is going to cause an error. I
                        > just haven't used structures much and am unsure how to resolve this.

                        At their most basic a structure is simple an array that uses meaningful
                        strings rather then integers for the keys. In fact many programming
                        languages call them 'hash arrays'.

                        >
                        > Also, doing it this way, wouldn't I require a different loop for every type of
                        > item, and an IF statement inside the loop that only outputs the desired type?
                        > Doesn't seem very efficient, but then again I'm probably to dumb to understand
                        > what Ian's original intent was.
                        >

                        No if statement, just a set of nested loops.

                        Loop over collection of types
                        loop over array of element of this each type
                        output each element
                        END array loop
                        END collection loop


                        • 9. Re: GUI Query Builder for Users
                          kenji776 Level 1
                          The Type element of the structure contains a string that describes what kind of value is expected in that field. So .Type is what is causing the error.
                          • 10. Re: GUI Query Builder for Users
                            Level 7
                            A slightly more fleshed out example: Code is CF8 based, some features
                            not compatible with older versions, but easily convertible.

                            <cfscript>
                            aTest = [
                            {
                            label = 'foobar',
                            updateable = false,
                            type = 'picklist'
                            },
                            {
                            label = 'george',
                            updateable = true,
                            type = 'int'
                            },
                            {
                            label = 'nix',
                            updateable = true,
                            type = 'picklist'
                            },
                            {
                            label = 'gracie',
                            updateable = false,
                            type = 'text'
                            }
                            ];
                            </cfscript>

                            <cfdump var="#aTest#">

                            <cfset FormItems = structNew()>
                            <cfloop array="#aTest#" index="key">
                            <cfset aTemp = structNew()>
                            <cfset aTemp.label = key.label>
                            <cfset aTemp.updateable = key.updateable>
                            <cfset aTemp.type = key.type>

                            <cfif NOT structKeyExists(FormItems,key['type'])>
                            <cfset FormItems[key['type']] = arrayNew(1)>
                            </cfif>

                            <cfset arrayAppend(FormItems[key['type']],aTemp)>
                            </cfloop>

                            <cfdump var="#FormItems#">

                            <cfoutput>
                            <cfloop collection="#formItems#" item="elemAry">
                            <h1>#elemAry#</h1>
                            <cfloop array="#formItems[elemAry]#" index="element">
                            <p>#element.label# - #element.updateable# - #element.type#</p>
                            </cfloop>
                            </cfloop>
                            </cfoutput>

                            Will need some modification to match your situation and there is some
                            redundancy that could be eliminated. But should be a good proof of concept.
                            • 11. Re: GUI Query Builder for Users
                              Level 7
                              kenji776 wrote:
                              > The Type element of the structure contains a string that describes what kind of value is expected in that field. So .Type is what is causing the error.

                              I bet this is caused because you still have FormItems = arrayNew(1)
                              somewhere and for this code it would need to be FormItems = structNew().

                              See my previous post with a more complete example. Luckily it is a very
                              slow day here at the office.
                              • 12. Re: GUI Query Builder for Users
                                Level 7
                                A best guess working example based on the original posts code.

                                <cfset FormItems = structNew()>

                                <cfloop Collection="#oContact.Results#" item="key">
                                <cfif NOT structKeyExists(formItems,oContact.Results[key].type>
                                <cfset FormItems[oContact.Results[key].type] = arrayNew(1)>
                                </cfif>

                                <cfset
                                arrayAppend(FormItems[oContact.Results[key].type],oContact.Results[key].type>
                                </cfloop>

                                <cfdump var="#FormItems#">

                                <cfoutput>
                                <cfloop collection="#formItems#" item="elemAry">
                                <h1>#elemAry#</h1>
                                <cfloop array="#formItems[elemAry]#" index="element">
                                <p>#element.label# - #element.updateable# - #element.type#</p>
                                </cfloop>
                                </cfloop>
                                </cfoutput>
                                • 13. Re: GUI Query Builder for Users
                                  Level 7
                                  A corrected best guessed working example.

                                  <cfset FormItems = structNew()>

                                  <cfloop Collection="#oContact.Results#" item="key">
                                  <cfif NOT structKeyExists(formItems,oContact.Results[key].type>
                                  <cfset FormItems[oContact.Results[key].type] = arrayNew(1)>
                                  </cfif>

                                  <cfset
                                  arrayAppend(FormItems[oContact.Results[key].type],oContact.Results[key]>
                                  </cfloop>

                                  <cfdump var="#FormItems#">

                                  <cfoutput>
                                  <cfloop collection="#formItems#" item="elemAry">
                                  <h1>#elemAry#</h1>
                                  <cfloop array="#formItems[elemAry]#" index="element">
                                  <p>#element.label# - #element.updateable# - #element.type#</p>
                                  </cfloop>
                                  </cfloop>
                                  </cfoutput>
                                  • 14. Re: GUI Query Builder for Users
                                    kenji776 Level 1
                                    Eh Hem...

                                    HOLY SH** I LOVE YOU GUYS!

                                    Sorry, just had to get that out there.
                                    I am working on modifying your code to output the form fields. Overall, genius, pure genius. Thank you so much. I'll let you know how it goes.
                                    • 15. Re: GUI Query Builder for Users
                                      kenji776 Level 1
                                      Well the project has gone exceedingly well. I have managed to build a very sweet query builder. There are some things I wish I could do a little more efficient, but since it's not going to be used a hundred times a minute by people (like once or twice a day probably), I'm okay with it. If anyone needs a Salesforce Query Builder, below is the code. Just grab the SOAP Libraries by Tom de Manincor at http://www.tomdeman.com/salesForceCFC, and use the following code. If anyone sees a way to make it better, and less ghetto, please let me know. Thanks for everything guys!