For my job I want to convert an enormous excel sheet to an asp.net application to save us some time and eliminate human errors.
In one excel file we have 15 different sheets. Each sheet (Sheet per Customer) contains products and next to that the weeks of the year.
So you can fill in the number of products you want to order per week.
We have a last sheet which adds all the products per customer per week.
I'm having trouble designing the database relations and how to present this in a datagrid.
My thought was create a dropdown where you can select the customer. Then you get presented with the products on the left and the weeks on the right (in the same grid so per product you can fill in the amount for the week)
Then also create an overview grid that adds all the numbers together.
The reason I want to do this is so we only need to create a product once and can save the data in a database.
Does anyone have a clue on how to begin with this?
As per my understanding your problem is like managing customer product list through out the year right. Ok this is simple.
your database should have 3 tables, (Customers, Products, CustomerOrders). CustomerOrder should have the columns like customerid, productid, quantity, week, year. By having these fields you can design your screen in way that. it should be complex as well
as it should be user friendly. There are so many ways to design the screen.
1. give a dropdown to select customer and when customer selected populate your page with a matrux consists of rows as product and columns as weeks and the meeting points as text boxes to select the number. This is easy to implement but may not give good
usability
2. Provide your UI such way that user can select customer in a dropdown. Instead showing entire matrix just show single row for
product1 as described in first case. User will finish the quantity requirements for product1. and then provide a way to add product2 as you can see in many applications adding multiple entries with single user input fields set at once
3. Same as in case 2 but reverse provide all the products on columns and weeks on rows and one with multiple product requirements once
4. you can have two dropdowns one for customer second for products/weeks and provide a list of weeks/products and let the user fill the values
5. wtih 3 dropdowns 1. customer, 2.product 3. week and a textbox for user input. this is very hard and takes huge time for end users to add all the records so difficult
and finally another page with a Customers dropdown. on selecting a customer it should show all the products requirements for all the weeks throughout year in a matrix
Thank you very much! This is exactly what I need, i made my database different but it wasn't as good as your suggestion! I'm thinking of creating 2 dropdowns, 1 for the year and another to select the customer.
Then show the products on the left and the weeks on the rights like you suggested. I need to display all the weeks and products at once.
So the difficulty will be to create one save button (as supposed to one update per line) to allow save when the editor has finished editing multiple quantities. Another difficulty is that the products have an orderstart (for example product1 can only be
orderd from week 12) so the cells of week 1-11 need to be grayed out so editors can't fill in a quantity until week 12
if your application is such a simple one why dont you consider using visual studio light switch which will provide you the best interface and less time to develop the feature
Thanks again for your suggestion, i haven't used lightswitch before but I took a look and it looks really good, also you can convert it to a Webapp which I need. I'll get to work!
So I've created the app in lightswitch, i've created the tables and relationships and created the screens for adding products and customers.
(that was really easy)
But to create the custom datagrid for adding the quantities per product/week I'm totally flabbergasted. If you have any tips they would be most welcome :)
BorrieRulez
Member
18 Points
60 Posts
Datagrid design question
Oct 13, 2012 03:25 PM|LINK
Hey everyone,
For my job I want to convert an enormous excel sheet to an asp.net application to save us some time and eliminate human errors.
In one excel file we have 15 different sheets. Each sheet (Sheet per Customer) contains products and next to that the weeks of the year.
So you can fill in the number of products you want to order per week.
We have a last sheet which adds all the products per customer per week.
I'm having trouble designing the database relations and how to present this in a datagrid.
My thought was create a dropdown where you can select the customer. Then you get presented with the products on the left and the weeks on the right (in the same grid so per product you can fill in the amount for the week)
Then also create an overview grid that adds all the numbers together.
The reason I want to do this is so we only need to create a product once and can save the data in a database.
Does anyone have a clue on how to begin with this?
Kind regards,
Borrie
sarathi125
Star
13599 Points
2691 Posts
Re: Datagrid design question
Oct 13, 2012 03:35 PM|LINK
Hi,
Its better to have a dropdownlist to show all the customer and show the related data on the gridview.
Load all sheets as separate datatable and put it all in a single DataSet, then you can use it.
For more help, give some mockup design, then it will be better here for your requirement.
Remember to click Mark as Answer on the post that helps to others.
My Blog :MyAspSnippets
srinivasupem...
Member
361 Points
64 Posts
Re: Datagrid design question
Oct 13, 2012 04:36 PM|LINK
Hi Borrie,
As per my understanding your problem is like managing customer product list through out the year right. Ok this is simple.
your database should have 3 tables, (Customers, Products, CustomerOrders). CustomerOrder should have the columns like customerid, productid, quantity, week, year. By having these fields you can design your screen in way that. it should be complex as well as it should be user friendly. There are so many ways to design the screen.
1. give a dropdown to select customer and when customer selected populate your page with a matrux consists of rows as product and columns as weeks and the meeting points as text boxes to select the number. This is easy to implement but may not give good usability
2. Provide your UI such way that user can select customer in a dropdown. Instead showing entire matrix just show single row for product1 as described in first case. User will finish the quantity requirements for product1. and then provide a way to add product2 as you can see in many applications adding multiple entries with single user input fields set at once
3. Same as in case 2 but reverse provide all the products on columns and weeks on rows and one with multiple product requirements once
4. you can have two dropdowns one for customer second for products/weeks and provide a list of weeks/products and let the user fill the values
5. wtih 3 dropdowns 1. customer, 2.product 3. week and a textbox for user input. this is very hard and takes huge time for end users to add all the records so difficult
and finally another page with a Customers dropdown. on selecting a customer it should show all the products requirements for all the weeks throughout year in a matrix
choose the UI carefully
Srinivasu Pemma
http://www.srinetinfo.com
BorrieRulez
Member
18 Points
60 Posts
Re: Datagrid design question
Oct 15, 2012 04:27 PM|LINK
Srinivasu,
Thank you very much! This is exactly what I need, i made my database different but it wasn't as good as your suggestion! I'm thinking of creating 2 dropdowns, 1 for the year and another to select the customer.
Then show the products on the left and the weeks on the rights like you suggested. I need to display all the weeks and products at once.
So the difficulty will be to create one save button (as supposed to one update per line) to allow save when the editor has finished editing multiple quantities. Another difficulty is that the products have an orderstart (for example product1 can only be orderd from week 12) so the cells of week 1-11 need to be grayed out so editors can't fill in a quantity until week 12
I'm using MVC4, I found this article: http://www.codeproject.com/Articles/305308/MVC-Techniques-with-JQuery-JSON-Knockout-and-Cshar
I'm gonna try and set it up but any other suggestions are welcome as I'm a beginner in programming :)
Kind regards,
Borrie
srinivasupem...
Member
361 Points
64 Posts
Re: Datagrid design question
Oct 16, 2012 02:40 AM|LINK
if your application is such a simple one why dont you consider using visual studio light switch which will provide you the best interface and less time to develop the feature
Srinivasu Pemma
http://www.srinetinfo.com
BorrieRulez
Member
18 Points
60 Posts
Re: Datagrid design question
Oct 16, 2012 12:41 PM|LINK
Srinivasu,
Thanks again for your suggestion, i haven't used lightswitch before but I took a look and it looks really good, also you can convert it to a Webapp which I need. I'll get to work!
Borrie
BorrieRulez
Member
18 Points
60 Posts
Re: Datagrid design question
Oct 16, 2012 09:03 PM|LINK
Srinivasu,
So I've created the app in lightswitch, i've created the tables and relationships and created the screens for adding products and customers.
(that was really easy)
But to create the custom datagrid for adding the quantities per product/week I'm totally flabbergasted. If you have any tips they would be most welcome :)
Borrie