GUI Query Builder for Users

[Guest]

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.

[Dan_Bracuk]

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.

[Guest]

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!

[Guest]

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.

[Dan_Bracuk]

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.

[Guest]

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).

[Guest]

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.

[Newsgroup_User]

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

[Guest]

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.

[Newsgroup_User]

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.

[Newsgroup_User]

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.

[Newsgroup_User]

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>

[Newsgroup_User]

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>

[Guest]

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.

[Guest]

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!

[Newsgroup_User]

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