Showing rows on a page

Rails
1

This is my first rails program and Iâ??m having trouble figuring out how
to show rows on the page when there may not be rows in the database.

There are 3 tables, employees, cost_codes and timesheet, I will simplify
them when describing them later on.

When an employee logs in, I would like to pull up their timesheet, which
should consist of one row per cost code. So timesheet would look a
little like:

(timesheets)
Name, Cost Code, Ending Date, M,T,W,Th,F

Bob, 11701, â??7-15-2006â??, 8,8,0,0,0
Bob, 11704, â??7-15-2006â??, 0,0,8,8,4
Bob, 55702, â??7-15-2006â??, 0,0,0,0,4

(Cost Codes)
Code, Description, Default
11701, â??Programmingâ??, 1
11702, â??Researchâ??, 1
11703, â??User Studiesâ??, 1
11704, â??Documentationâ??, 1
11705, â??Vacationâ??, 1
55701, â??Onsite Trainingâ??,0
55702, â??Client Relationsâ??,0
55703, â??Conferencesâ??,0

Where part of my problem is when the data entry form is displayed to the
user, all of the default rows should be shown regardless of there being
a timesheet row, plus any optional rows. Also, instead of just listing
the default rows, I would want to substitue any values currently in the
timesheet table.

So the user should see a form something like this: [ ] are textboxes

Programming [ 8 ] [ 8 ] [ 0 ] [ 0 ] [ 0 ]
Research [ 0 ] [ 0 ] [ 0 ] [ 0 ] [ 0 ]
User Studies [ 0 ] [ 0 ] [ 0 ] [ 0 ] [ 0 ]
Documentation [ 0 ] [ 0 ] [ 8 ] [ 8 ] [ 4 ]
Vacation [ 0 ] [ 0 ] [ 0 ] [ 0 ] [ 0 ]

Client Relations [ 0 ] [ 0 ] [ 0 ] [ 0 ] [ 4 ]

And a second question are my relations correct?
Employee has_many Timesheets

Timesheet belongs_to Employee
Timesheet has_many CostCodes *
(Should this be a has_one instead of many?)

CostCode belongs_to Timesheet

Thanks for any help or suggestions.

Bob

2

Hi Bob,

The answer to your first question might be as simple as a css entry. I
suspect that your using a table so:

table { empty-cells: show; }

Or perhaps use a helper to convert nil values into 0’s for each

:

Helper Method

def table_data( value )
value ? value : ‘0’
end

and in the view:
…

<%= table_data( some_value ) -%> …

As for the second question, I’m not sure what advice to give. I see
some
redundant data however. For example, in the timesheets table there is
the
column ‘cost_code’, which could be replaced with ‘cost_code_id’ and thus
enabling Timesheet.costcodes.code to get the same information.

But perhaps those are the views and I misunderstood.

The relations look correct though.

Regards,

  • trav
3

Thanks for looking this over Travis.

Using cost_code_id -> Good idea, I should have been doing that from the
start.

As for displaying the rows, I might have described the intent
incorrectly or misunderstood your reply.

I wasn’t sure how to get rails to recognize that a row should be shown
when a record doesn’t exist in the timesheet table. In my above
example, I have timesheet records for 3 of the 5 default cost code, but
I want to make sure all 5 codes will show up on the page - with 0 or
blank textboxes for those that don’t exist yet.

I don’t know if this would be the best / easiest way to do this, but my
first thought would be to list all of the default cost codes as blank
rows when a user logs in. Then perform a second pass and update the
cells from the timesheets table if any of the values have been filled
out, and then add any rows for the optional cost codes.

It seems rather round about, but I’m still trying to figure out what
rails can do for you automatically.

I can try and explain that better if its confusing at all.

4

Bob,

Thanks for explaining that a bit more. I think I understand now:
Timesheets
forms always have all CostCodes.

I can think of some ways to implement this. But they might not be the
best
way.

In the controller that renders the form for timesheets, prep the new
Timesheet object such that it already contains all CostCodes. For
example:

class TimesheetController < ApplicationController

def new
@timesheet = Timesheet.new
@timesheet.costcodes = CostCode.find(:all)
end

end

Then in the form view, one just needs to enumerate through the @
timesheet.costcodes for each row.

A second and better way might be to add all CostCodes in the model upon
instantiation. Thus reducing the need for the controller to add the
costcodes.

Something like:

class Timesheet < ActiveRecord::Base
has_many :costcodes

def after_new
costcodes = CostCode.find(:all)
end

end

  • and -

class TimesheetController < ApplicationController

def new
@timesheet = Timesheet.new
@timesheet.after_new()
end

I cannot think of a better way to do the second, even though I am sure
there
are some ways.

Hope this helps,

  • trav
5

Just bringing back an old thread. Basically what I was trying to do is
list some rows from the database, as well as create some rows on the
fly.

Short version is that I ended up having to create a sql query to get the
following result set. The rows with null ID’s are required cost codes
that previously did not have an entry for the user. Optional rows will
not have a null as they only show up if they already exist.

±-----±----±-----±---------------------±----±—±---±—±---±—+
| id | name| Code | Description | Req | h1 | h2 | h3 | h4 | h5 |
±-----±----±-----±---------------------±----±—±---±—±---±—+
| NULL | Bob | 5104 | Business development | 1 | 0 | 0 | 0 | 0 | 0 |
| NULL | Bob | 5105 | Client Relationships | 1 | 0 | 0 | 0 | 0 | 0 |
| NULL | Bob | 5106 | Market studies | 1 | 0 | 0 | 0 | 0 | 0 |
| 2 | Bob | 5107 | Interviews | 1 | 1 | 2 | 3 | 4 | 8 |
| NULL | Bob | 5108 | Other Marketing | 1 | 0 | 0 | 0 | 0 | 0 |
| NULL | Bob | 5109 | Company Meetings | 1 | 0 | 0 | 0 | 0 | 0 |
| NULL | Bob | 5110 | Training | 1 | 0 | 0 | 0 | 0 | 0 |
| NULL | Bob | 5111 | Evaluations | 1 | 0 | 0 | 0 | 0 | 0 |
| NULL | Bob | 5001 | Vacation | 1 | 0 | 0 | 0 | 0 | 0 |
| NULL | Bob | 5001 | Sick Time | 1 | 0 | 0 | 0 | 0 | 0 |
| NULL | Bob | 5001 | Holiday | 1 | 0 | 0 | 0 | 0 | 0 |
| NULL | Bob | 5198 | Other time off | 1 | 0 | 0 | 0 | 0 | 0 |
| NULL | Bob | 5001 | Other administrative | 1 | 0 | 0 | 0 | 0 | 0 |
| NULL | Bob | 5199 | Hours needed to | 1 | 0 | 0 | 0 | 0 | 0 |
| 3 | Bob | 8001 | Optional Cost Code 1 | 1 | 3 | 1 | 3 | 3 | 0 |
| 4 | Bob | 8002 | Optional Cost Code 2 | 0 | 4 | 5 | 2 | 1 | 0 |
±-----±----±-----±---------------------±----±—±---±—±---±—+

My question is when it comes time to save this data back into the table,
what is going to happpen because of the null IDs? Will the database
just take care of it because of the auto increment feild? And will
multiple rows being saved at one time cause a problem? - could an AJAX
function that saves the row when a user leaves a cell be a solution?

Second thought - I could automatically create all of the required rows
in the DB for a user when the table initalizes. This would remove the
need for the sql query, but would end up with approx 8 extra rows per
user (8 * 400 users * 3 payperiods = 10,000 extra rows).

A second opinion about these ideas would be appreciated

6

I appreciate the help. It might be a couple days before I get a chance
to try this out, but I’ll let you know how it goes!

Bob

7

If I understand your question, you’re trying to deal with tables that
don’t have unique primary keys. Don’t do this. Rails is explicitly
designed to count on a unique key. To quote AWDWR v2, page 282:

Normally, Active Record takes care of creating new primary key values
for records that you create and add to the database?they?ll be
ascending
integers (possibly with some gaps in the sequence). However, if
you over -
ride the primary key column?s name, you also take on the
responsibility
of setting the primary key to a unique value before you save a new
row.

You’ll want to add (or have the database add) a unique ID per row (in
a new column named id) when you first import the data. You’ll need
to rename your id column to something else. Once you do this,
rewriting (saving back) multiple rows simultaneously is not a problem.

In general, 10,000 rows is not a lot for any reasonable database to
handle.

        - dan

–
Dan K. mailto:[email protected]
http://www.dankohn.com/ tel:+1-415-233-1000