First off, I apologize for the length of this post and thank any of you in advance who donate your time to reading it! I'm doing some research about different databases for the company I work for, and wanted to see if you all had any feedback for what I'm looking to do and whether or not FileMaker is a good choice...
I realize this is definitely a biased audience, but I have faith that I'll get some honest feedback.
First, a little background on our company:
We manufacture custom promotional products with a lof of moving parts and no order is ever the same. We have about 65 employees but a good 45 of those are production staff (machine operators, assembly, etc.).
Currently a lot of our process is the same as it was in the late 90's/early 2000's - based off of multiple spread sheets, word documents, and paper forms that are filled-in by hand. The owner has the "If it ain't broke, don't fix it" mentality to the whole thing and has not been very open to getting a more advanced system to track things like incoming orders, scheduling and inventory. On the same token, we've been growing steadily and he's complaining about a lack of efficiency on all levels, and our order entry/processing always sticks out like a sore thumb to me.
A little background on my skills:
First, I'll be the first to point out that I'm by no means a developer. I have minor experience with very VERY old versions of access and putting together forms and formulas in CrystalReports to display that data. I also have some experience with Excel formulas/formatting. I've been using computers since I was 5 and am pretty tech-savvy, and have worked in tech support and IT in the past and am currently a Senior Graphic Designer/Pre-Production Supervisor that helps our IT contractor and in-house staff. I'm largely self taught and totally immerse myself in new topics/skills when I get into them and that mentality has served me quite well in the past, allowing me to work in a professional environment without really any formal college to speak of.
Our Antiquated Process:
Basically, our order entry is done manually. Purchase Orders are Faxed or Emailed as PDF's. From there, our production scheduler checks in the order and adds to a daily "PO Log" which lists the individual orders received that day, dollar amount for each, ordering client and total Daily PO dollars which is added to a Month-To-Date total for incoming PO's.
From there, much of the same date has to be RE-ENTERED into another spreadsheet that is our production schedule. This is MANUALLY sorted by order ship date, and has to be repopulated even though much of the information was already entered into the PO Log Spreadsheet.
If the order ship date is changed, you have to copy the 3 or 4 lines of spread sheet that list that order's details, Cut them, and "Insert Cut Cells" under the heading for the new ship date.
As a side note, our inventory is kept totally manually on a spreadsheet, including mostly raw materials and a few finished products. Everytime something is pulled, the warehouse manager writes it down on a paper Pull Sheet by hand, which is brought to the Production Scheduler to adjust the inventory - only he's a busy guy and is consistently 2 weeks behind on those...which is stupid because a new physical inventory is done every two weeks which basically negates that completely, requiring a physical stock check frequently - especially on the few finished products we do not make ourselves which are always in demand.
The Solution I'd Like to Put Together:
Let me be the first to agree that this job would be better left for a qualified database developer. That said, I can't get permission to dedicate any funds to this because I don't have the support of the owner, so I need to build something and show it in action (I'm confident that I can get it approved once it's in a workable state).
Also, my initial desires aren't incredible ambitious I don't feel (basically linked spreadsheets with a form front-end), so I think it's something I can put together in at least a rough state.
Here's the process I see initially: Order Entry Form - Includes Item #, Date Received, Proposed Ship Date, Ordering Client and a few other fields for product/process specific info. This would all be in one table. From this, I would have two main reports that could be pulled: A production schedule report that sorts all orders by ship date and the pertinent fields shown currently on our production schedule. Also, a PO Log report that would list orders received on the given day, with only a few fields showing ordering company, proposed ship date, dollar amount for the day and dollar amount for the month.
A linked table I would add would be for inventory. Initially I would have this only track the few pre-made items we sell which are in high-demand and the stock levels fluctuate pretty rapidly. From here, we'd have a standard inventory report that lists stock levels in-house, minus allocated stock and stock that is actually pulled. I would also have an ipad or computer in the warehouse for the warehouse manager to have a "pull sheet" form to which he would choose the item, enter how many he's pulling and the PO number their being pulled for, which would adjust the inventory in real-time as those were pulled. The "allocated" stock amount on inventory report would be culled from the items listed in the orders table, counting any orders with product number X, Y, or Z that haven't been pulled yet ( I would have a field in the inventory table listing inventory status - pulled or not pulled - which would be adjusted manually by hand OR by the "Pull Sheet" form which would be linked by way of PO number).
Some of this is being done in Excel with multiple spreadsheets and pivot tables, but I'm trying to streamline the process, cut down on hand-written paper in the office and avoid the time wasted by entering orders in multiple spreadsheets even though the data entered is the same on many of them. I'm also trying to use technology in places like the warehouse which has until now been largely paper-based, and keep a more real-time view on the inventory for some of the faster moving products.
If that worked out we'd build on it to track raw materials inventory and possibly Spec-Lists of orders, but that's a ways down the road if at all.
One of my colleagues (the production scheduler) has previous experience with Access (more than I do) and I've played around with the database software in Open Office a bit, but I like that Filemaker is Cross-Platform, mobile and web capable right out of the box, and seemingly a bit simpler to setup while still being powerful. Also, there seems to be a good community for it which I'm sure will be helpful.
To give you some idea of the volume of business we do, we process about 200 to 400 PO's a month. Userbase would likely be 4 to 8 individuals for actual data entry and 3 or 4 more that would just be Read Only for web reports via interal bookmarks in their browsers.
Also, we DON'T need this to do accounting or act as a CRM...I want to keep this simple and streamlined for primary use in our Pre-Production and Production departments. Primary OS is Windows, with maybe a few ipads and a mac here and there.
So, if you've made it this far, let her rip! Is FileMaker a good choice? Or would I be overcomplicating things?
If we go this route, I'll be investing my own time into training myself up properly in how to use it, and at least at first I would be investing my own personal time into it as an experiment and learning opportunity.