0

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.
My problem---
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.
Thank you!

2
Contributors
2
Replies
3
Views
6 Years
Discussion Span
Last Post by PPS
0

I've read this a couple of times, but my head hurts.

Could you provide the table structures and any table relationships?

0

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...
The tables---

-- 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).';
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.