First I will explain what we have been using so far for the past nine months - then I will develop on what we need for the future.
We have been using some online spreadsheets on google apps to allow a number of our employee users (at different workstations throughout the same building ) to input and share data in our picture framing business .
Customers bring in some type of artwork (eg photo / canvas / object) that they need framed - here we assign a job number, recorded their contact details, salesperson, date of receipt, due date of completion, work scope required, price, type of picture moulding and type of mount to be used, notes etc , id of physical folder in which artwork will be stored etc and assigned that job a status - 1.
* Separately a form on A4 sheet was filled with similar info and this sheet accompanied the artwork (identified by job no ). In the case of jobs needing quick turnaround we used jobs sheets with highlighted red printing (so it would visually stand out in workshop)
* Separately also we used a costing spreadsheet template locally to work out the proposed sell price of each job - this allowed entry of the artwork size / desired border size/ number of mounts (as well as options such as spacers / fillets / designs etc)- price of mount & glass & backboard / msq , price of moulding/m , fittings and other processes along with multipliers on each - to calculate the material cost and sell price and % margin etc. The resulting "sell Price" is what would be agreed with the customer for the job.
Next that job (folder containing artwork and completed form) was delivered to the workshop area and on arrival was assigned status - 2(by changing cell on Google spreadsheet) .At this point the A4 form was removed from the folder and put in "jobs to do" bin. Then when a workshop person took this A4 form to commence work they changed status to - 3. The A4 form (completed manually) contained the dimensions allowing the moulding / mount / glass etc to be cut . The artwork was removed from folder by another person at other workstation and the job was assembled and status then changed to - 4 (on separate PC). The completed job + form attached then moved to quality check area where a quality check was done - once passed status changed to 5. At final workstation (and separate PC), the completed frame was sealed and hanging hardware and stickers etc attached, and protective wrapping was attached - status now changed to 8 . (codes 6 & 7 were previously used when we operated from 2 separate buildings) Customers were then contacted and status changed to 9. Jobs were picked up and status finally changed to [url removed, login to view] covers the job from receipt from customer to giving completed item back to the customer. By manipulation of the spreadsheet (downloaded manually from google apps) with pivot tables etc, i am able (remotely) to see progress of all work and measure value of work taken in / daily workshop output / value of completed work to be picked up by customers etc. We also use a variant of these codes – eg 1.1 / 1.2 , 2.1 / 2.2 etc where x .1 signifies that the job is on hold awaiting an input from customer , while x.2 denotes the the job is on hold by us eg - awaiting material / process etc.
Currently - once - QC checked Ok the status is changed to 5 - If QC fails the job is returned to assembler - ideally this would mean that that job status would change to 3.5 (for example) - until corrected and changed to Status 4 again when assembler completes rework and places at QC table again. If possible , the quantity of these occurences would be captured - to allow for a "get it right first time" target.
Moving forward, we want to run a database to cover the above along with other valuable info on a server but I will still need to be able to get reports remotely and have a solid back up system. This database must allow simultaneous access by multiple users continually entering and editing data.
We have 6-8 suppliers who supply us with moulding (long lengths of wood material from which the picture frame is made) We stock about 200 mouldings at present but expect this to increase towards 400/[url removed, login to view] is a sample of each moulding ( identified by code) in the shop for the salesperson to work with customers We can get some identical mouldings from more than one supplier but each supplier has their own coding. We also get some very similar mouldings from a number of suppliers allowing us to substitute depending on availability / price etc Currently we keep another google spreadsheet record of all mouldings received from each supplier so that we can see what quantity / price of each moulding we purchased each month. Typically we place orders from each twice each month.
We keep no live record of raw materials in stock – so we decide on what to order by looking at the job sheets of work in and then physically looking at the stock available.
We have a similar system for mountboard sheets. We carry about 60 color types - each of which is identified by its code.
Ordering in supplies is laborious and is hit and miss. Firstly we have to physically check the completed A4 job forms to see what mouldings / mountboard we need for jobs in - then we cross check this with the quantities we have in stock to decide what quantity we have to order. We must also check what other moulding we normally buy from this supplier (what quantity we have bought per month recently and what is on hand) to add it to the order also. We then send a PO email to the supplier. Most often they will be unable to fulfill the complete order and some items will go on back order - this is important if goods have still not been received when placing the next order with the same supplier.
Moving forward, we need to incorporate all of the above into one integrated database where we make best use of our data. When we are costing a job , the dimensions and material types are entered - If the customer is proceeding with the work - a job sheet complete with Job ID is saved as file and both a detailed job sheet for workshop and a simplified copy for customer are printed out. These dimensions can determine automatically what total quantity of each material is required for jobs of status 1 & 2.(Workshop personnel will only commence a job knowing that materials are available to them immediately) Once a stock take of material on hand is updated – we could have available a report for each moulding / mountboard type the quantity on hand and the quantity needed to fulfil customer orders and the quantity of each bought in for each of past months – so we can generate better material orders.
To facilitate efficient workflow and ensure due dates are met, we need to be able to see / print out reports or priority of jobs by due date
When each person needs to update the status of a job they need to be able to do a quick look up by job number and update status ( we may need to consider that this could be done by barcode - so that as a job (and accompanying worksheet with barcode) pass from a workstation, the barcode is simply swiped – each installed barcode reader would only change job status to a pre assigned status level for the particular barcode / job Id being scanned.
When jobs move to status 8 – they need also to be assigned a storage location of 1-100 which represents the physical storage shelf on which the completed work is placed until the customer will collect.
When a customer brings in more than 1 job at the same time, these jobs need to be additionally identified as 1/x, 2/x …..x/x/ etc so that they are not notified until all of their jobs reach status 8.
When a customer brings in many identical jobs they may be considered as 1 job (but multiple quantities of materials will of course apply)
To facilitate production, we need to see work lists for the moulding cutting machine – identifying job no’s of identical mouldings – showing all the lengths that need to be cut of each moulding type. Similarly for the cutting of identical mountboard.
To facilitate identification of each moulding, a photo of each moulding can be taken and incorporated, so that it can be seen on screen wherever it is referenced. – including 4 vital dimensions A,B,C,D.
When new mouldings are introduced, they need to be easily added – supplier/ code / description/ photo / 4 dimensions (as well as what other supplier this identical moulding is available with their code)
We will need to see live reports (locally and remotely ) of what quantity and value of work we have at each point throughout the process. We need to see the value & quantity of work taken in each day , as well as value and quantity of work completed / progressed each day
We will need to keep customer records and be able to do easy lookup for customer details / previous work done etc. - also their previous sales history.
Most customers give us their mobile tel numbers – we need to be able to extract these for use use in SMS promotion broadcasts.
Once a job is accepted in , we will be able to print out the A4 sheet to accompany the job with the all important job number clearly indicated (with some red markings / identifier if its a priority job)
When the sales person is selling a framing job they need to see levels of available stock.
We need option of attaching some photos to a job record (eg work in / completed etc) – which can easily be retrieved when searching that job.
We have a number of PC's ( Win XP) on a network with internet access – so ideally need some solution that does not require any mainstream database software on our PC. (Ms Access etc)
Our website is hosted on [url removed, login to view] which supports ASP.net 3.5 , AJAX estensions , SQL server 2005.
I hope this is of adequate detail at this stage to describe what we require.
6 freelancers are bidding on average $607 for this job
I can do this for you with my huge experience of 130 completed projects world-wide with 10 on 10 rating. Lets discuss in pmb/Inbox. -Regards : TUHIN