First I will explain what we have been using so far for the past few months - then I will develop on what we need for the future. I think an Access DB is required but am open to other suggestions.
We have been using some online spreadsheets on google apps to allow a number of our employee users (at different workstations throughout 2 buildings) to input and share data in our picture framing business .Up to now , we had a small retail shop and a separate workshop at another location - but we are now uniting these elements into a single building next month.
Customers bring in some type of artwork (eg photo / canvas / object) that they need framed - here we assigned 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 ).
* 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 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 6. At shipping out of workshop at end of each day each job being shipped (still with completed form attached the status changed to 7. On receipt back in shop, jobs were checked in and given status 8. 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 with pivot tabled 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.
Moving forward, we will have all operations run from the same building so have the option of running 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 a 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.
Separately we try to keep spreadsheets of the actual quantity (meters) of each moulding we have on hand This is difficult to maintain live so is mostly updated prior to placing orders.
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 sometimes 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)
I hope this is of adequate detail to describe what we require.
10 freelancers are bidding on average $524 for this job
I can provide the needed application in sharepoint in 15 days with all the functionality stated. If need can provide you POC or Sample application in 2 days.
Dear Sir, Using MS Access I can provide a network application as needed and provide a sample of the work within few days. Please send samples of sheets you use
Hello. Is it possible to offer you a Sharepoint solution to resolve your task? I am the lead of sharepoint team in our company and we would be glad to provide you with a solution that will fullfill your needs
fineframer: Greetings from Ohio in the US. I don't beleive that I have ever seen such a well-detailed project explanation. Please see my listing on the Project Message Board. Thanks, Randy