What is your experience level in FileMaker?
Will the iPad solutions be synced up with a mySQL/PHP back-end or a FileMaker server? Or will each client just spit out the data in some pre-chosen format?
The iPad solution would be synced up to a filemaker server although that is much further than I have gotten. I would like to keep everything concentrated in one software package for reliability and ease of use.
but I would need the debits to be transfered or related over from a another table.
Can you explain what you have in mind here?
It's entirely possible to populate the inventory log via a portal on a layout based on a related table. In an invoicing system for example, the line items table can be replaced with this inventory log and the Quantity Sold field is replaced with the "credit" field in the inventory log so that each item sold is automatically logged out of inventory.
In you case, a table of well maintenance jobs could be linked to this table and a portal to the Inventory Log could be used to update the inventory by logging the consumption of various inventory items used to repair the well equipment.
This can become a very complicated database very easily. Take your time thinking things out and you can keep it very managable and easy to use and build upon later if you see a need arise.
May also need to break down your inventory into consumables and non-consumables per their very different replacement rate, etc.
This is where the "relational" part of relational database systems comes into play. You will want to break everything down so that it makes sense, is easily managable, scalable, and in a way that ensures data integrity. However, at the same time you don't want to go overboard. So we don't want 2000 tables but we don't want everything crammed into one table either. Best rule of thumb is does it make good, common, sense in your mind when you look at your schema and think it through.
I would suggest using the ACID principle but not really sure if this would apply here considering we are using FileMaker...you think Phil? =)
You're right that this becoming complicated very quickly.
When I said, "but I would need the debits to be transfered or related over from a another table." I was meaning that the inventory would be one table where I could enter in new materials after purchase but the deductions would need to be taken another table such as the drilling log.
I like what you are saying about breaking everything down. That could work to have a separate table for each material type. All the prebuilt inventory systems I've seen don't really seem like inventory systems as much as product catalogs. It appears that the quanities in stock need to be manually deducted... Is this true?
Thanks for the help. I'm still trying to wipe my mind of spreadsheet logic and embrace the database way of looking at things.
LOL believe it or not that was a brilliant self discovery, because that's exactly what we tend to do...look at it as a spread sheet at first...then you realize you have to think spread sheet, only 3D. At least thats how I explain it. hehehe
Manually deducted...depends. Say we have two tables of inventory, consumables and non-consumables. Perhaps we want to create our work order or quote before we actually remove the items needed for this project from inventory. See where I am headed here?
I promise I am not trying to confuse you or make things more complicated, I just know what it is like to get everything built without thinking a few things through first and then you got a mess on your hands or a huge re-build job just to fix it all.