You're going to need a table of serial numbers, with a product key and serial number field, and a one-to-many relationship between products and serials.
On your invoice, you'll want to select the item (which can look up the various attributes of the item) and then select a serial number from a value list of unsold serial numbers.
You can determine if a particular serial number is "out" by whether it appears on an invoice. This is pretty simplified, though - does invoiced mean it's already shipped? Might it get shipped (and therefore "out") before it's been invoiced? Is it possible to receive an order than you're not shipping yet, in which case you may want to reserve the item for a sales order even though it's not technically "out" yet.
I have a blog that reviews some of these considerations, and others: http://extensitech.com/distribution-inventory-management/
So... there are a lot of ideas, and a lot of considerations. For real control of your inventory, I would NOT recommend using the starter solution. The "starter" solutions should really be called "demo" solutions, imho, because while there a great place to see features in action, they're fairly hopeless as true, extensible business solutions, unless you have a VERY simple business that was created with a cookie-cutter.
If you're interested, Extensitech offers a free consultation to prospective clients, usually about an hour long. (Click "schedule a consultation" on our web site.) It really is free and it's really a consult, not a sales pitch. I'd suspect that even if you still want to create this yourself, we can add some value during that free consult and at least get you pointed in the right direction, and if you don't need our services beyond that, there's no obligation or hard feelings or anything. On the other hand, if you want our help ongoing, we could work out anything from occasional dedicated support up through just building the whole thing for you.
We've built a number of comprehensive inventory solutions, including serialized inventory, inventory with "lots", generic inventory (where items are completely interchangeable) and mixes between those. We should at least be able to walk you through some of the variations to consider and save you from some pitfalls. Creating a reliable inventory isn't so much a technical challenge (in FM) as a business rules challenge, but we can point you in the right direction on both fronts.
There are a number of Point of Sale systems out there already as FileMaker templates or to commercially. I'm a wee big afraid when you're talking about FileMaker 5... its pretty hard to find an operating system to even run that stuff. FileMaker 14 is so very different than FileMaker 5. But regarding FileMaker POS solutions, you can google them or go to "Made by FileMaker" link which I would give you now, but it actually is down for maintenance (haven't seen that before -- oh well).
As Chris mentioned, there are consultants out there that can help you with a conversion. Of course they cost money and you'll need a budget. But you can find many good ones at FileMaker Consultants, Data Consultants, Database Consultants
This forum is good for individual questions. We can't through the forum make you a solution, but if you want to delve in and do the work yourself, as you have questions, many of us are here to give you suggestions and help with formulas and layout ideas. But if you're working on something from FM version 5... you'll probably find it more adventageous to start afresh. You can probably still import the data from the old version, but your solution should start a new with current security, layout templates, schema, etc.
Thank you for your reply. We basically have 3 files 1) products ID 2) stock(for some reason called Lim) 3) Invoices. The invoices is what we'd like to give to customers as their receipt, after they have paid or invoicing to be paid.
2) Stock where we have to go out and put the invoice ID otherwise it can't be sold
3) then invoices
We want to type the Serial Number in here - but if we do it thinks it's a new product and the Product ID, Name etc aren't linked. It will only be linked if we start 1) get invoice number 2) go to Stock page find the item and put this invoice number into the invoice ID and alter the button to out 3) then go back to the invoice that we just created and type in the customer details.
Does the structure I have here look like it can be "repaired"/altered/fixed to go from New Invoice, type in/scan Serial Number and make it sold - rather than go back and forth? If you think it can and can advise how, I can talk to the directors about payment if you can give me an idea of cost/hour. Many thanks.
Hi Taylor, Thank you for your reply. Windows 7, is running our FMP5. Our FMP5 files can't be opened in 14, so will have to upgrade and export our data which we are happy to do as there are features that we want to make use of but I wanted to see if I could make sure that FMP14 could do what we wanted before we upgrade and find that we can't. I will have to spend some more time on working out the structure. Thanks.
It is good to see you can get FMP5 to run in Windows 7. I assume that is compatibility mode, but good to know it can still be done. I didn't realize that.
FM 14 is significantly more powerful than 5 in so many ways, so I am sure you will appreciate the improvements. One significant improvement is that all 3 of your files can be put in one FM 14 file, and each of those files becomes a table in a FM 14 file. One real advantage is that you can have a single security control of users from one file. Additionally, I forget if FM 5 was as far back as when it only had passwords and no user IDs. But FM 14 supports the traditional current security with a User ID and password.
I rather enjoyed seeing your posts because it was fun to have some memories of how layouts used to look. And it might be possible that you can upgrade to FP7 format and then to FMP12 and it would still work the same, if that is what you want. But it is hard not to appreciate how much better the current themes look and the user interface is so much more modern now. Even if you keep the functionality the same, check out the new themes. I think you'll be surprised.
I think Chris' suggestion that you hook up with a developer even if for a small amount of time will help you out quite a bit. And come back here and ask questions as you venture into the new version.
You might also see if you have a FileMaker Pro users group in your area (www.fmpug.com). And of course you know about our forums here to ask questions.
Hi, it's not in compatibility mode. It works on XP as well as we have 2 machines still on XP - (I know.... eventually we'll get to updating those too!). Yes, I can see with the new format the 3 files being one, that's good. Thanks for the tips about FP7 then 12, though like you say I might just want to start with the new system.
My advice is to export your data and start anew in FMP14.
As Taylor mentioned, your best bet is to find an existing POS built in FileMaker. There are several and the developers of such system are usually happy to customize it if needed. Otherwise, you can look for a FileMaker developer near you (FileMaker Consultants, Data Consultants, Database Consultants) if you want to build afresh.
Finally, I agree with Taylor - it was nice to see the layouts from days past.
If you need a FileMaker pos, just drop me a message. I have just finished a fm version works with tiles and on iPads in fmGo.
Your fm5 solution wil work much simpler in fm14, take my word :-) I really wonder where you got that fm5 solution, brings back memories :-P
Serialnumbers normally are unique for a specific stock item. Like a serial number for a specific iPhone. SKU number is more of a type. For example all iPhone 6, with 64GB of memory.
Anyway your fm5 solution could be adjusted to work the way you want... From your explanation it just seems the relationships aren't set up to match the workflow you have now. So you can either change the workflow or change the relationships. If you want a solid advice a peek at the file is needed.
I used to program in fm5 but in the long run investing in an upgrade will be cheaper, because development time will be a lot less in the newer version.
This is the sort of thing we'd figure out during a free consult. It's too easy to jump to conclusions just based on looking at screen shots and written explanations.
We can work out what it'd take to do this "down and dirty" to get what you need in the short term, a path for if you want to make gradual improvements and just have us help as-needed, and also what it'd take for a soup-to-nuts rebuild. Then we can determine which of these, or what combination of these, is appropriate for your needs.
Like every other developer, we're going to lean towards the "soup-to-nuts" approach ourselves, but we'll present all the options, and in the end it's your decision and we're here to serve regardless. We won't try to steer you towards anything that is too much for your budget or for the importance of the solution.
We have clients in each of the situations described, where we do "as-needed" fixes to existing systems, mentor an in-house developer, and of course plenty where we built whole solutions. You can check out our testimonials to get an idea, but one of the testimonials I'm most proud of is one you might not expect:
“I called Extensitech for a free consultation, though I was apprehensive and assumed they would find the earliest opportunity to steer me to their services…
I was pleasantly surprised. They were very knowledgeable about CRM applications, and they truly listened to my needs and the frustrations I’d had in finding a solution…
Ultimately, we came to the conclusion that one of their customized solutions was not actually the best fit for us and they encouraged me to check out an off-the-shelf solution from another party, even though that would not result in any business for them. The off-the-shelf product worked. Their free consultation was truly free; if anything, I feel guilty that they steered me to a solution that didn’t involve them. I’m hoping we can find an excuse to work together in the future, as an intelligent, thoughtful resource like Extensitech is such a rare find in this age of ‘read our FAQ’ service.”
Ray Bloch; President
Ray Bloch Productions
Just a thought - does anyone here have a preference of FM to Access? I'm asking because originally I hoped that FM15 could just import and I could just tweak things but it looks like it's going to be more complicated than that. So I was wondering if people thought Access was a good system as well only because we already have Access on our computers so we wouldn't need to purchase that part. Many thanks on everyone's thoughts and suggestions.
...does anyone here have a preference of FM to Access?
Well, this is a FileMaker forum, so yes, most of us prefer FileMaker.
Having said that, MS Access may be fine if you have 5 users or less and only work on the desktop. You'll still need a database developer to help you get things set up in MS Access, unless you have those skills yourself.
Finally, you can take or leave this next piece of advice: Databases are organic and grow with an organization. As the organization changes, so too should the database. For that reason, you would do well to budget resources for those changes. If you have the skills to make the changes, then the resource is primarily time. If you need a developer, then the resources are time and funding.
We have a joke around here:
Access is a virus intended to hook the user into using SQLServer.
While tongue-in-cheek, it does reflect the fact that Access has some limitations that you need to consider:
- No multi-user server version
- No web publishing capability
In other words, what Daniel said is basically true: If you outgrow a very small deployment, you'll have to migrate an Access solution away from Access and into another technology, typically SQLServer. Whereas you really have to grow large to outgrow a FileMaker deployment.
Development in Access is also considerably more ... arcane? Technical? You really need to understand typical database terminology - tables, views, SQL queries, event triggers, etc., etc., to work with Access. As a result, a non-technical person will have difficulty working in Access. Not so much with FM.
Now, the flip side is that it's "free", in that it's part of the Office suite on Windows. FileMaker has a licensing cost for the client versions (Pro and Advanced), and, unless you use Go (mobile) or WebDirect, every user needs a license. (And Go and WebDirect require extra money for the Server license, unless you use a hosting provider, and most of them charge more for those services.)
Daniel is right: You'll have a cost one way or another. But the overall cost will typically be smaller with FileMaker.
not a good joke, Mike! SQL server does NOT have an integrated interface method, like FM (or access).
But you go on from there with valid advice!
To answer the topic: POS
Vanessa, there are good systems out there already in FM. I believe you are looking for Inventory Management along with your Invoices. You might even take a look at FM Starting Point - Free FileMaker Template by RCC for some good FileMaker integration.
Oh, Beverly, you need to get more joy out of life ...
And yes, that's absolutely true ... and another reason to seriously consider the scaleability discrepancies between Access and FM.