Excel workbook plan
Key objectives to achieve
Searchable data set.
Different report views for different departments
Entry method must be optimized.
Pre-defined prices for items and when user inputs item and quantity the total amount
invoice is shown.
Add item button for multiple item orders and adds all the invoice and puts a total price
list at the bottom.
Prices of products can be changed all previous entered order prices must not be
changed.
And user friendly
Tasks needs to be done
Order ID = Auto Generated
Customer code Auto generated if they are new customers but if they have one when the
user inputs the code the customer information populates the necessary fields.
In the order input form the product side need to be a drop down option and if the user
wants to add more products can click an add item button and more products.
There is a report generation tab that generates different types of reports.
Some have compound data like delivery and payment dates they have multiple dates of
delivery and payment dates needs to have a solution for this type of data’s and make
them searchable and reportable.
Some customers can be provided with a discount and in the form there needs to be a
field that discount from the total amount per specified amount it could be percentage
or specific account.
1) Consider options that are fast, easy, and no cost solutions
2) Must have a backup solution
3) Doesn’t require so many infrastructure
4) Can also be deployed on the web and could also work on a
single computer without any internet access
Steps to creating the order system
1) Define tabs and tables inside.
2) Create the form and figure it out how the data inputs must be done
3) Finish the save part of the form section
4) Reset
5) Search
6) Update
7) Delete
8) Weekly report
9) Report by other categories like by product and by customer id
10) Financial report
Step 1 build order input form and all data entry methods and fields.
Step 2 write prompt to catch all the data and save it under the order records form.
Ok in my order form I have these fields
“Customer ID”
“Customer Name” Must be automatically populated when the “Customer ID” is
inputted. And if the “Customer Name” is already existed populate the “Customer ID”
field with its own “Customer ID” if this field is left empty.
“PI No”
“PI Date” make it a date format and make it easier for the user to input dates like a drop
down date option if that is possible to insure data integrity.
“Advance Payment”
“Advance Payment Date” make it a date format and make it easier for the user to input
dates like a drop down date option if that is possible to insure data integrity.
“1st FS No” Next to this I want to add BUTTON “Add FS No” that displays a new INPUT
FIELD that says “2nd FS No” next to this INPUT FIELD add the same BUTTON that displays
an INPUT FIELD that says “3rd FS No ” and after this no BUTTON will show.
“Type of Bank” a DROP DOWN option that have Values of “CBE”, “Dashen Bank”,
“Abyssinia Bank”, “Abay Bank”, “Lion International Bank”.
“Delivery Date” make it a date format and make it easier for the user to input dates like
a drop down date option if that is possible to insure data integrity. Next to this input
field I want a button that says “Add Delivery Dates” and that will bring three input fields
that the user can add at most three delivery dates for orders that have more delivery
dates.
“Add Payments” this is a BUTTON that will reveal an INPUT FIELD that the users add a
payment and with perspective dates with a date format INPUT FIELDS like the other
date formats, also the “Add Payment” BUTTON will appear at the bottom of the INPUT
FIELDS. Make this process limit the INPUT FIELDS to Three after three times make the
Button to Not Appear.
“Products” a DROP DOWN option that have a value of “SLNW”, “SLCF”, “TS25”, “TS30”,
“SH20”, “SH30”, “SH5L”, “SHBU”, “SG20”, “SG30”,”SG5L”, “SGBU”, “HC20”, “HC30”,
”HC5L”, “HCBU”, “BL20”, “BL30”,”BL5L”, “BLBU”, “AADK”, “AASK”,”AAPC”, “AALB”,
“CPHW5L”, “CPHWBU”, “CPDW5L”, CPDWBU”, “CPDG5L”, “CPDGBU”, “COMB”, VAKT”.
When one option is clicked automatically show an INPUT FIELD that says “Quantity”
where the user inputs the amount of the product the customer’s need of that product
which will fetch the measurement quantity specified at the “Products” tab in the
“Products” table. And I want a BUTTON below THAT SAYS “Add Item” which will repeat
the process above and also appear again and again when the “Add Item” is clicked. So
the user can add product as many as he wants.
There is # BUTTONS and a “Total Amount” and “Remaining Amount” shown at the
bottom of the form
“Total Amount” This is a section at the bottom that calculates this (each Product “Unit
Price” from “Products” table x the “Quantity” specified) and adds all products price and
shows the Total amount. I want this field to be saved also in the “Orders” tab in the
“Orders” table.
“Remaining Payment” I want a calculation to populate this field (the “Total Amount”
value – the “Advance Payment” value). And when the “Add Payments” field is inputted
with a value also minus the added amount from the remaining value.
The three buttons are “Save”, “Update”, and “Delete”
First let’s build the form first and we will handle the saving and all process step by step.
Second I want to save every info I have in the form to the “Order” table.
For inputs that have compound value like “Products”, “Add Payments”, “Delivery Dates”
in this case I want to save on the same column but different row but not outside of the
order row as in general if you u understand me if you don’t I will give an example.
So suggest me which columns I must have to save all the information needed.
And have a time stamp column that saves the time when the order is created w will use
this in the reporting section.
Be Professional
CAREFULLY ANALYZE DATA STRUCTURE
And step by step process how to create the html doc and process
I also want when I opened the order form or even the document itself this form to appear
automatically
Ok now I want to refine some things and move to the next step one is I want the bottom
BUTTONS to have a different color.
“Save” to remain “Green”
“Update” to be Yellow
“Delete” to Be Red
I want to add the following options to the “Products” DROP DOWN INPUT FIELDS. I have
noticed some of the DROP DOWN options doesn’t work when I CLICK “Add Item” BUTTON
and the DROP DOWN FIELDS doesn’t work properly.
“SLNW” which corresponds to the “Products” tab “Products” table ROW 2
“SLCF” which corresponds to the “Products” tab “Products” table ROW 3
“TS25” which corresponds to the “Products” tab “Products” table ROW 4
“TS30” which corresponds to the “Products” tab “Products” table ROW 5
“SH20” which corresponds to the “Products” tab “Products” table ROW 6
“SH30” which corresponds to the “Products” tab “Products” table ROW 7
“SH5L” which corresponds to the “Products” tab “Products” table ROW 8
“SHBU” which corresponds to the “Products” tab “Products” table ROW 9
“SG20” which corresponds to the “Products” tab “Products” table ROW 10
“SG30” which corresponds to the “Products” tab “Products” table ROW 11
”SG5L” which corresponds to the “Products” tab “Products” table ROW 12
“SGBU” which corresponds to the “Products” tab “Products” table ROW 13
“HC20” which corresponds to the “Products” tab “Products” table ROW 14
“HC30” which corresponds to the “Products” tab “Products” table ROW 15
”HC5L” which corresponds to the “Products” tab “Products” table ROW 16
“HCBU” which corresponds to the “Products” tab “Products” table ROW 17
“BL20” which corresponds to the “Products” tab “Products” table ROW 18
“BL30” which corresponds to the “Products” tab “Products” table ROW 19
”BL5L” which corresponds to the “Products” tab “Products” table ROW 20
“BLBU” which corresponds to the “Products” tab “Products” table ROW 21
“AADK” which corresponds to the “Products” tab “Products” table ROW 22
“AASK” which corresponds to the “Products” tab “Products” table ROW 23
”AAPC” which corresponds to the “Products” tab “Products” table ROW 24
“AALB” which corresponds to the “Products” tab “Products” table ROW 25
“SP5L” which corresponds to the “Products” tab “Products” table ROW 26
“CPHWBU” which corresponds to the “Products” tab “Products” table ROW 27
“CPHW5L” which corresponds to the “Products” tab “Products” table ROW 28
“CPDWBU” which corresponds to the “Products” tab “Products” table ROW 29
CPDW5L” which corresponds to the “Products” tab “Products” table ROW 30
“CPDGBU” which corresponds to the “Products” tab “Products” table ROW 31
“CPDG5L” which corresponds to the “Products” tab “Products” table ROW 32
“COMB” which corresponds to the “Products” tab “Products” table ROW 33
VAKT” which corresponds to the “Products” tab “Products” table ROW 34
Next if it is possible I want to make the “Order Entry Form” display a little bit bigger so it
doesn’t look cramped but if it complicated stuff we could live it as is.
After this are all done write me a save function that captures all the data inputted to the “Order
Entry Form” to the “Orders” table on the “Orders” tab.
Problems that occurs running the codes
1 is still the products DROP DOWN OPTION has a problem it only shows for one cycle
after that it dosen’t show any of the options.
2 when it saves the data from the “Order Entry Form” to “Orders” table the locations
are not in the right way if there is a way to enforce a method for the data to be safed at
a specific location that will be perfect.
3 I want to add an INPUT FIELD named “Remark” that the user can put some un
conditional or notes from the customer to the order records.
4 I want to add a SEARCH BUTTON and INPUT FIELD for the auto generated “Order ID”
to be searched and the data in that “Order ID” ROW can populate the respective fields.
5 I want a function that generate “Customer ID” and saves in the “Customer ID”
COLUMN of the “Customers” table on the “Customers” tab. consider the first statement
also where I specified ““Customer ID”
“Customer Name” Must be automatically populated when the “Customer ID” is
inputted. And if the “Customer Name” is already existed populate the “Customer ID”
field with its own “Customer ID” if this field is left empty.” This one so connect the
“Order Entry Form ” to the “Customer” table also.
6 I want a function that generates “Order ID”
1) Structure
“Customers” table in “Customers” tab COLUMNS
- “Customer ID” Auto Generated
- “Customer Name”
- “Contact Person”
- “Email”
- “Phone”
“Products” table in “Products” tab COLUMNS
- “Product ID”
- “Product Description”
- “Unit Price”
- “Measurement”
“Ordered-Products” table in “Ordered-Products” tab COLUMNS
- “Product ID” fetched From “Products” table in “Products” tab
- “Customer ID” Fetched From “Customers” table on “Customers” tab
- “Quantity”
- “Advance Payment”
- “Advance payment Date”
- “”