Help needed for dynamic update form

Report · Aug 27, 2009

I could really use some advice - I've been asked to build a time tracking application (basically a timesheet) and I have a fair bit done but the part I am really struggling with is the best way to accommodate some of the specifications. I am NOT a programmer (I have some coldfusion experience but nothing really advanced) so I have not managed to sucessfully integrate the various methods I've found on the web so far. The database is created and so are all the queries, and I have also written a cfc to handle the drop-down menu logic needed but I don't really know how to integrate it with the form.

Our production server has ColdFusion MX7 so all the great functionality in the CF8 examples I can't use.

The issue is the user should ideally be able to add/edit/delete multiple rows at once- I like CFGRID, and the HTML version seems best. The main issue with the Flash version is the scrolling to get to the insert/delete buttons- I couldn't see how to get rid of that. A separate add and edit form could be ok depending on how easy it is to use.

One problem I have is that I can't work out how to have default values with the grid (the userID which is a session variable, and the date which is constantly changing- there is a cfcalendar for the user to change date).

The biggest hurdle is the related select drop-downs I need- it's not quite as simple as the city,state,postcode examples. For the first drop down the pick an option- and for only 2 of those options there is a second drop-down. Anything else and it stops there. For the second drop-down, there are 2 options, and depending on which one of these they pick the 3rd drop-down pulls a query from one or another table in the database (2 entirely different things). The three options have different database tables. The main timesheet table just stores the id number from those tables (so I also need to display the names on the drop-down from the options tables not the number).

I played with simple and complicated javascript and coldfusion solutions as well, but because it's a form to update records and also because of the above specs I just couldn't get anything to work right. I tried binding with the cfc and nothing would bind, plus I don't know how to make all happen without a page reload.

Does anyone have any advice for the best approach to this? As I mentioned I've got tables, queries and even a cfc but I'm not too clear on how to put it all together properly within the constraints of MX7.

PS I also can't post a lot of code because of where I work- I know that's not helpful but am looking for the best approach to this, then I can work on the details. Right now I am jumping from solution to solution and not getting anywhere.

Report · Aug 28, 2009

It's so much to take in, it'd be best to show the code.

Report · Aug 31, 2009

Well, a lot of code has come and gone because I couldn't make it work, where I'm currently at is:

** for some reason getProjects.name doesn't work and causes an error. I haven't worked out how to get the default inputs for the date and user ID to work either. I also tried binding and a flash form somewhere along the way.

** the CFC is below, #ds# didn't work and I had to put in the actual DSN name, not sure why, but anyway this is the logic of the thing. Ideally I would like to use this logic with the cfgrid, but I'm not sure if that is possible? It seems like it would be the most user friendly approach.

The CFC so far is:

<cfcomponent>
   <cffunction name="getActivities" access="remote" returnType="query">
        <cfquery name="getActivities" datasource="#ds#">
SELECT * FROM timesheet_activities
</cfquery>
        <cfreturn getActivities>
    </cffunction>

    <cffunction name="getTypes" access="remote" returnType="query">
    <cfargument name="Activity" type="any" required="true">
    <cfif ARGUMENTS.Activity EQ "">
        <cfset getType = "">
    <cfelse>
        <cfquery name="getTypes" datasource="#ds#">
        SELECT * FROM timesheet_type
        </cfquery>
    </cfif>
    <cfreturn getTypes>
    </cffunction>

    <cffunction name="GetProjects" access="remote" returnType="query">
    <cfargument name="Activity" type="any" required="true">
    <cfargument name="Type" type="any" required="true">
    <cfif ARGUMENTS.Activity EQ "" OR ARGUMENTS.Type EQ "">
        <cfset LstProjects = "">
    <cfelseif ARGUMENTS.Activity EQ "1" OR "3">
    <cfquery name="getProjects" datasource="#ds#">
SELECT id,name FROM projectsa
WHERE completed = 'false'
</cfquery>
<cfelse>
<cfquery name="getEProjects" datasource="#dse#">
SELECT id,name FROM projectsb
WHERE statusID = '6'
</cfquery>
    </cfif>
    <cfreturn getProjects>
    </cffunction>

</cfcomponent>

Any attempts to actually use the cfc didn't work. I tried to use it with a normal html update form and got the message- failed to bind, Activity didn't exist. I also tried to bind it to a flash grid. The argument for Activity needs to come from the drop-down Activity type selected. Maybe I'm missing something.

ETA:

just moved everything to the live MX7 server (because my dev server is Coldfusion8) and I get the following:

Attribute validation error for tag CFGRID. The tag does not allow the attribute(s) BINDONLOAD,BIND.

Does this mean I definitely can't use the CFC with the cfgrid on MX7? Or is there a way to do it?

Any advice would be greatly appreciated.

Report · Sep 01, 2009

The scope of a component is detached from the main thread of execution. So, you should not expect a function in a component to know a variable, ds, that is defined in a Coldfusion page.

In cases like yours, there are usually 2 ways to define the datasource name . Either

1) define <cfset application.ds = "the_dns"> in the Application file, if the datasouce applies to the whole application. Then use the variable application.ds throughout the application.

or else

2) You could just pass the ds variable as an argument to a function.

Now, on to the CFC. First, test the functions without putting preconditions. Insert the preconditions afterwards.

Previously, the statement <cfif ARGUMENTS.Activity EQ "" OR ARGUMENTS.Type EQ ""> could lead to no query being returned. No good.

Be clear that the function always returns a query. Also, invoke the component just before the form. Get the query from the component instance, then use the query to populate the grid. You should therefore not be passing an empty query to grid.

<cfset myObject = createobject("component", "path.to.component")>
<cfset getMainData = myObject.getProjects(1,2)

<cfcomponent>
<cffunction name="getActivities" access="remote" returnType="query">
    <cfquery name="getActivities" datasource="#application.ds#">
        SELECT * FROM timesheet_activities
    </cfquery>
    <cfreturn getActivities>
</cffunction>

<cffunction name="GetProjects" access="remote" returnType="query">
<cfargument name="Activity" type="any" required="true">
<cfargument name="Type" type="any" required="true">
    <cfif ARGUMENTS.Activity EQ "1" OR "3">
        <cfquery name="getProjects" datasource="#application.ds#">
            SELECT id,name FROM projectsa
            WHERE completed = 'false'
        </cfquery>
    <cfelse>
        <cfquery name="getEProjects" datasource="#application.dse#">
            SELECT id,name FROM projectsb
            WHERE statusID = '6'
        </cfquery>
    </cfif>
    <cfreturn getProjects>
</cffunction>
</cfcomponent>

Report · Sep 09, 2009

Thanks for your helpful answer, I haven't worked with CFCs much. Sorry to take so long to reply I got pulled away on other projects and only just got back to this one. I did have the DSN defined in application.cfm but only as ds not application.ds, I didn't think of that. I am wondering about one thing, you mentioned Be clear that the function always returns a query. This sounds practical but what if you want the select drop-downs to be disabled for some choices?The idea was to populate the grid with drop-downs depending on previously selected ones (like related selects).

In the meantime I have actually gotten everything pretty much working over the last couple of days with regular forms and javascript for the drop-downs, as I managed to get a very helfpul book (Advanced Macromedia Coldfusion MX7 Application Development) which had just what I needed so I could custom code the related selects as the requirements were so specific, and it was pulling data from different databases with different DSNs. It's single line editing only but it looks sort of like a grid. I gave up on the cfgrid because the html type didn't work on the MX7 server, and because I can't use the Flash version because of the scrolling for the add/delete buttons, and because I wasn't sure I'd be able to get the grid working the way it needed to.

I am still wondering 2 things, in case V2 uses the grid or I use it for something else down the road:

can you get rid of the scrolling so the add/delete buttons aren't hidden?

and how would you define default values for columns in the grid?

Neve

Report · Sep 10, 2009

I am wondering about one thing, you mentioned Be clear that the function always returns a query. This sounds practical but what if you want the select drop-downs to be disabled for some choices?The idea was to populate the grid with drop-downs depending on previously selected ones (like related selects).

This is not only practical, but a necessity. You want the function to return the query variable getProjects. However, that return variable is undefined when arguments.activity is neither 1 nor 3. You will then get an error.

One possible solution would be to return the empty query in the case when ARGUMENTS.Activity EQ "" OR ARGUMENTS.Type EQ "". You could do this by writing <cfset getProjects = queryNew("","")> just after the line <cfset LstProjects = "">. You should also change the last queryname from getEProjects to getProjects so that the return variable is defined here too.

can you get rid of the scrolling so the add/delete buttons aren't hidden?

The simplest way I can think of is to add the attributes format="flash" and height="400"(or your own number) to the grid.

how would you define default values for columns in the grid?

By using cfparam, just before the form.