Dynamic Column output

[Guest]

I have been struggling with something that I am not sure that I can do. I have a database table with 84 Columns. The rows represent projects that are constantly being added and updated. Each column represents a weekending date with the headings of W1(being week 1), W2(being week 2), W84(being week 84), ...etc. Each product has various durations so not all columns are populated. Some may only have a duration of a couple of weeks. I have a form that I want to use to update these columns. Below I have given an example of what I am trying to do. Obviously the Value attribute will not work but you can get the idea of what i am trying to accomplish. The weekday variable changes in an index loop.

<cfset weekday = 1>

<td align="right" width="70">
<CFINPUT TYPE="Text"
NAME="W#Weekday#"
SIZE="5"
validate="float"
value="#W#Weekday##"
MAXLENGTH="10">
</td>

any ideas?

[CF_output]

I dont fully understand your question.. You want to "output" only certain columns into the form?

Do you want to only update certain columns?

[Guest]

Let me give an example:

Row 1 is a project named "Crude Oil Demolition".Column 1 is W1 (week one) which has a value of "280" (hours of work).. Column 2 is W2 (week 2) which has a value of "320" (hours of work). Without going into to much detail, there are 20 more weeks of work (20 more columns) with hours assigned to them. There are constantly jobs being added to this table with hours being allocated to each week of work. Some jobs may have 3 weeks of work and some may have 80 weeks.The user sets up the job in another form with a duration(length) in weeks. When they come to this form to populate the values of the hours per week table, the form is looped (index loop) by the number of weeks of duration that the user has indicated in the other form. They then insert the hours per week in the cfinput form element. Once they do this they will submit the form. Now they may have to come back and change these hours so there will be a need for the form to dynamically display the current value and allow for change. Each column name has a W then a number... hence my logic of

W#Weekday#

The W will never change but the value of weekday will change with the index loop. What I am trying to accomplish is for this:

W#Weekday# to equal "W1"

but in the form. This won't work:

value="queryname.W#Weekday#"

it just displays

queryname.W1

Does this make better sense?

[-__cfSearching__-]

> Does this make better sense?

Perhaps array notation would help. It can be used to access query columns dynamically.

#queryName["W"& WeekDay][RowNumber]#

[BKBK]

What I am trying to accomplish is for this:
W#Weekday# to equal "W1"
but in the form. This won't work:
value="queryname.W#Weekday#"
it just displays

queryname.W1

Does this make better sense?

Yes it does. The evaluate function might be slow and complex, but would work here. For example,

value="#evaluate('queryname' & '.' & 'W#Weekday#')#"

[Dan_Bracuk]

Normalize your database and you won't have problems like this.

[-__cfSearching__-]

Dan Bracuk wrote:
> Normalize your database and you won't have problems like this.

+ 1

Not to mention eighty four columns is a very wide table.

[Guest]

Unfortunately this is as good as it gets. I may have four to five thousand projects(rows) a year and I must track some of them for over a year to two years. Each column is a week. If you can brain up a better plan believe me I am up for it. Fire away.

PS the array notion helped. I must now simply work with my loops. Thanks for the help.

[Dan_Bracuk]

quote:

---

Originally posted by: chrispilie
Unfortunately this is as good as it gets. I may have four to five thousand projects(rows) a year and I must track some of them for over a year to two years. Each column is a week. If you can brain up a better plan believe me I am up for it. Fire away.

---

Learn data modelling. That's a better plan.

[Newsgroup_User]

>> Unfortunately this is as good as it gets.

No, it isn't as good as it gets. You should learn how to model your data
and design your DB properly. A forum such as this is not the correct
environment for getting you up to speed on how to do this: searh Google for
DB design tutorials or hit your bookstore or Amazon or something.

My initial observations are that your project/weeks should be ROWS in a
table, not columns in a table. DB engines have a maximum number of columns
one can have in a table. For SQL Server it's 1024 populated columns, with
a maximum size of 8k. Oracle's around the same, I think.

A good rule of thumb is that if you have similar data in adjacent columns:
your design is probably wrong. Another one is that if you have to alter
your schema periodically (ie: a new column every month, etc), then your
design is wrong. Your tables should expand vertically (rows) not
horizontally (columns).


> Learn data modelling. That's a better plan.

Agree strongly.

And it should be done whilst the project is still in development, rather
than "later".

--
Adam

[Guest]

The project still is under development. Points taken. Thanks.

[jgethers]

you have 80 something variables for each project?