I need a little help getting started with this, suggestions would be great!
The applications' purpose is this: a school is allocated a certain amount of $ for the year to spend on updating technology, it's called 'refresh'. The database has a table for the location and amount of $ it starts with. Then a table for orders and for order_details (the individual items) and funding. The funding table exists because an item can have a percentage paid by these refresh dollars and the rest paid by another fund, so there can be more than one funding source for an item in an order.
The current access form has a drop down at the top for choosing the location. That fills 2 grids, one with the orders and a second with the details of each order as it is selected. A purchasing specialist uses these grids to indicate that the purchase is allowed (meets the standards for the fund), may add or change fund distribution, or may just mark the purchase as 'reconciled'.
I would love some suggestions on how to go about this form. I'm fairly good at pulling data and looping through it to generate HTML, I've done it with checkbox and radio arrays anyway. But this has me stumped - I need a push in the right direction.
I've read this a couple of times, but my head hurts.
Could you provide the table structures and any table relationships?
Yes, I know what you mean..
I'm trying to replicate an access form without falling into access traps.
This is a new project, so if you think the table structure is flawed I can make adjustments there.
Thanks for looking at this, my head hurt too...
-- how much of the refresh budget each school received CREATE TABLE itr_budgets ( budget_id SERIAL PRIMARY KEY, ode_num INTEGER, dept_num INTEGER, loc_num INTEGER, cluster VARCHAR(200), school_name VARCHAR(200), enrollment numeric, budget_percent numeric, budget_dollars numeric, created_by VARCHAR(50), created_date TIMESTAMP, updated_by VARCHAR(50), updated_date TIMESTAMP, deleted_date TIMESTAMP, deleted_reason TEXT, deleted_by VARCHAR(50) ); -- Index for foreign key. CREATE INDEX itr_budgets_index ON itr_budgets (budget_id); CREATE INDEX itr_budgets_location_index ON itr_budgets (loc_num); COMMENT ON TABLE itr_budgets IS 'How much of the refresh budget has been allocated to each school.'; CREATE TABLE itr_orders ( order_id SERIAL PRIMARY KEY, budget_id INTEGER REFERENCES itr_budgets ON UPDATE CASCADE, order_date TIMESTAMP DEFAULT NOW(), by_userid VARCHAR(50), by_fname VARCHAR(200), by_lname VARCHAR(200), by_title VARCHAR(200), by_phone VARCHAR(200), by_email VARCHAR(200), chartfield VARCHAR(200), verified BOOLEAN DEFAULT false, comments TEXT, order_reconciled BOOLEAN DEFAULT false, created_user VARCHAR(50), created_date TIMESTAMP, update_user VARCHAR(50), update_date TIMESTAMP, deleted_date TIMESTAMP, deleted_reason TEXT ); -- Index for foreign key. CREATE INDEX itr_orders_index ON itr_orders (order_id); CREATE INDEX itr_orders_budget_id_index ON itr_budgets (budget_id); COMMENT ON TABLE itr_orders IS 'Each order placed by a school, for tracking info on who placed the order.'; CREATE TABLE itr_order_detail ( detail_id SERIAL PRIMARY KEY, order_id INTEGER REFERENCES itr_orders ON UPDATE CASCADE, description TEXT, unit_price numeric, quantity numeric, -- sometimes they divide up a purchase room VARCHAR(250), comments TEXT, detail_reconciled BOOLEAN DEFAULT false, created_user VARCHAR(50), created_date TIMESTAMP, update_user VARCHAR(50), update_date TIMESTAMP, deleted_date TIMESTAMP, deleted_reason TEXT ); -- Index for foreign key. CREATE INDEX itr_order_detail_index ON itr_order_detail (detail_id); CREATE INDEX itr_order_detail_order_id_index ON itr_orders (order_id); COMMENT ON TABLE itr_order_detail IS 'Purchase detail included in each order.'; COMMENT ON COLUMN itr_order_detail.description IS 'Item description from the on-line order form.'; COMMENT ON COLUMN itr_order_detail.quantity IS 'Could potentialy be a percentage.'; CREATE TABLE itr_detail_funding ( funding_id SERIAL PRIMARY KEY, detail_id INTEGER REFERENCES itr_order_detail ON UPDATE CASCADE, fund_id INTEGER REFERENCES itr_funds ON UPDATE CASCADE, budget_id INTEGER REFERENCES itr_budgets ON UPDATE CASCADE, distribution_notes VARCHAR(200), percent_of_total numeric, amount_this_fund numeric, payment_type INTEGER REFERENCES itr_payment_type ON UPDATE CASCADE, payment_reconciled BOOLEAN DEFAULT false, created_user VARCHAR(50), created_date TIMESTAMP, update_user VARCHAR(50), update_date TIMESTAMP ); -- Index for foreign key. CREATE INDEX itr_detail_funding_index ON itr_detail_funding (funding_id); CREATE INDEX itr_detail_funding_detail_id_index ON itr_order_detail (detail_id); CREATE INDEX itr_detail_funding_fund_id_index ON itr_fund (fund_id); CREATE INDEX itr_detail_funding_budget_id_index ON itr_budgets (budget_id); COMMENT ON TABLE itr_funding IS 'How each portion of the purchased item is being paid for.'; COMMENT ON COLUMN itr_funding.budget_id IS 'Budget that is paying for this portion (could be IT or even another school).';