Displaying records horizontally like in Excel
Ok, this is driving me insane. What am I missing here??? Im sure it is not this complicated. I have one excel spreadsheet that has data per product ordered by each branch. So in my mind that should go into a join table between branch and product but for some reason I have hit a brick wall.
In Excel it looks like this:
.................Branch 1 | Branch 2 | Branch 3 ...
Product 1 ..........5 | .........3 ..... |.......3
Product 2 ..........2 | .........4...... |.......5
and the user can scroll vertically across the 600 odd branches and horizontally through the 120+ products. The only way I have been able to get this working is by creating a field for every product and then the record is based off the branch code. But the user needs to approve or decline orders based on the stock on hand in the products table. Also they need to see the minimum quantity of each product when approving/declining request per branch. The products are all updated daily via a URL link to their suppliers DB, which is working perfectly.
I have setup portals to display 5 of the branch records at a time and so far on my sample data this is working nicely, but if I continue down this rabbit hole I am sure I am going to be in a world of hurt, as each product has a field for the code, name, order amount and sum of, approve amount and sum of, declined amount and sum of, min and on hand qty and product code. This would mean that my table could potentially have over 1000 fields, and then I would need to do sorting, finds, charting etc, plus move the order to a history table for a 3 month report.
I originally did this on a branch by branch import, but then my client said that she gets all the orders in one excel spreadsheet and not individually by branch. I need to be able to import the one excel spreadsheet and not the individual branches.