Complete beginner needs help with inventory database

[Migs]

Hi,

Can anyone help get me started here...I have a small business importing and selling goods.

I have got FMP pro 8 and would like to create a database to record purchases from my supplier, auto add to inventory, record sales to customers and auto subtract from inventory.

Not much else really...sounds simple?

I have looked in the documentation and cannot find out how to update inventory.

The Business Tracker is nearlly there apart from no Purchase module & does not update inventory...pretty basic omissions i thought.

[Tim W]

Hi,

The business tracker is not intended to be a complete solution, only a starting template for such. The developer took separate filemaker templates (starter solutions), stiched them together with a menu and common look-and-feel interface. Because of this, the modules don't talk to each other in every respect one would think they should. There is a section on these solutions on this forum. The connection of these modules to update inventory upon sales has been discussed at http://fmforums.com/forum/showforum.php?fid/101/

At the bottom of the results page you can expand the look back period of the posts to get more post results.

Filemaker is very friendly, sometimes frustrating, so you have entered the fray to modify. If you are working with

FileMaker Business Productivity Solution

, I can provide some assistance, but realize that it can take a while to get what you want, doing it yourself with help from forums.

If you are willing to put forth the effort to modify, verify we are speaking of the same file set. The files I have are bps_??.

Tim

[Migs]

Hi Tim,

Thanks for your reply. As I understand it, Business Tracker & Business Productivity Solution are 2 completely different compilations which both seem to lack the same things, ie; Purchasing & Stock Control.

I have no preconception which would be best, probably the one which was simplest to modify.

I don't require a very complex solution just one which includes the basic MRP facilities as outlined in the first post.

Thanks

[Tim W]

OK then, FileMaker Business Productivity Solution, according to my knowledge is the later build of the two.

Short commings in structure I see that should be discussed first are as follows:

1. Invoice only allows for 4 lines using repeating fields, which is not the recommended structure. Reporting can be limited with such a structure. Also, will you ever have an invoice with more than 4 lines?

2. As pointed out inventory is not relieved when items are sold. At what point do you want to relieve inventory? As detail line is keyed or upon completion of the invoice it'self? What about over-selling? Are any items direct shipped?

3. Invoice form print does not dynamically use firm name.

4. Inventory does not have a method for adjusting for missing items. Also does purchasing require a separate module or can it be a side-bar of inventory?

5. Custom Menus are used in the FileMaker Business Productivity Solution, to modify them you need FM Pro advanced. I have this, so if you wish we can change back to standard menu tool bar.

Just a few thoughts before starting.

Have you worked with filemaker before this?

Reply and we can get started,

Tim

[Migs]

Hi again Tim,

Thanks for sticking with it!

Just to give you a little more background, this is an eBay shop and not my day job, although it has become so successful it just might become it!

I import toys (approx 40 different types), from one (at the moment) supplier and nearly always just sell one or two at a time to any one buyer, therefore 4 line items is OK.

Inventory update at completion is fine, no oversell likely and no direct shipping.

Stock replenishment would be perfectly OK with sidebar, particularly as only one supplier.

Std menus no problem and finally to answer your last question, I have not worked with FMP before.

I am grinding my way through Lynda.com tutorial CD's and a lot of the mist is starting to clear!

Once again thanks for your involvment.

[Tim W]

After reviewing the FM Business tracker, even though built for v 7, does not possess some of the short commings of bps, built to showcase v 8.

Modification requires a very good understanding of the process of committing invoices, (status <> Pending). The update of status needs to trigger the update of inventory quantities. Working on an idea, let me know if you wish to proceed.

Tim

[Migs]

I too have had a good look at Business Tracker and get the impression is a bit padded out with unnecessary stuff and far more complex than necessary for my purposes.It may well prove more time consuming to adapt than is worth, which probably explains why it hasn't been done already.

I think I could set up a minimum number of relational tables, ie:

Supplier, Product, Purchase Order, PO item, Customer, Invoice, Invoice item.

Quantity available in Product table could be calculated simply by subtracting sales from purchases?

Do you think this approach would work for a simplistic operation like mine?

[Tim W]

I do agree!

It is always easier to remember your own logic, or even develop your own logic, than to learn someone else's.

A good couple of things to get you started are:

"White paper for FMP Novices" get version 1.09 last updated in 3/23/2006 - search for it in fm forums - there is a link.

Anchor/boey in the Relations Graph -

www.kevinfrank.com He has a very good powerpoint presentation. (sidebar - Many thanks to Kevin.)

Along this same thought Jonathan Stark's Web Site has an article on squids.

The Relationship Graph can become very confusing without a good approach.

If you see other papers on designing a database, realize your needs may not require all the steps, since this is a database for your company and not to market commercially or sell to upper management.

Do plan out your database before you start to build, but it doesn't have to be extravagant. Just appropriate for your needs. I have found an Entity Relationship Diagram (ERD) is helpfull to visualize the tables that should exist and how they relate. Make a field listing for each table. Sketch out each screen on paper along with the desired reports, this gives a good feel for the layout process. Oh yes, be sure to use a pencil. I do a lot of erasing. Sleep on your layout designs before you program them into filemaker, you will feel different when you come back to work on them. Don't skip these steps, as too many do, every time, almost, I have failed to do this work I lose more time redoing than the front work would have taken. I have many years of experience with designing working systems and still ask for this when doing a system for someone else/ do it for myself when I am the client. Think about the buttons you will use, locate them before you start the layout process. You want every layout to look and feel the same, which is very hard if you collect your button images as you go along. Just collect those button images in a file/folder/database - then a layout. I have also paid for this mistake with hours of layout tweaking to make it look right.

This will be a great adventure. Make it fun and feel free to ask any questions you need to along the way. Many are here to help you. Post in the subject closest to your problem and help usually comes faster.

Happy Filemaking,

Tim

[Migs]

Thanks for the suggestions Tim. I spent some time at work today drawing up the idea, time to try things out!