how to connect

[robinpow]

I'm too new to know the correct terms, so I will try this in plain English.

I'm doing a rather large project, I've kept it simple, but have ran into a stumbling block.

Each store has inventory, each store has it own file

However from time to time someone will run out of an item, and need to do a quick search to see who else may have a particular item.

How can I do this?

They way I have it roughed in now is:

A Main file that is more a less a menu that links to all other files

One file that has part numbers with descriptions of the item

Each location has an inventory, that pulls it's descriptions from the first file

I think it's called a "key" the field that ties everything together... in this case all files do share "part number"

Thanks!

[LiveOak]

The best way to approach this problem is to have a databases that can be accessed by all store locations. This database could hold all the actual store inventories in real time or just be updated each night with the current inventories of each of the stores.

You didn't really indicate if all the stores have separate, stand-alone copies of the files or if they are tied together electronically and all have access to one set of files.

If you don't already know what a key field is, this project might be a little beyond you. It's like not knowing what a spleen is and needing to perform surgery to remove one! You might want to seek some help from a local consultant.

-bd

[robinpow]

Because of the size (number of items) of the inventories, I made a separate file for each location. This is where I'm having the problem. If it was all in one file with a location field, it would be simple. I'm still in the early stage of setting this up, so I can still do major changes. The way I have it set up now:

master - more or less menu that ties everything together

item_ description - a file with all part numbers and their descriptions

location_one - (along with location_two, and so on to a total of 50 files) each location's actual inventory. These files relate to "item_ description" when the person types in the part number the description is shown.

I'm using the "partnumber" field as the key, since everything in the inventory has a part number.

My goal is to have a place where a person can type in a part number and get a list of every location that has it and how many they have in stock.

So far the only thing I have been able to do is set up individual little portals, however this isn't practical with having 50 different files to access.

I figure there must be a way of pulling the information together, setting up a portal to that file, but I simply don't know how, and can't find it in any book.

Oh yes there is one other little thing that affects this!! Since some items also have serial numbers, they are listed individually in the files

example:

A1234 coffee cup 8

B2345 coffee maker 9876A5432 1

B2345 coffee maker 9876A5433 1

B2345 coffee maker 9876A5434 1

So not only does it need to show who has them in stock, it also must count how many of each item, like in the example there is 8 coffee cups and 3 coffee makers.

I have a good understanding of the basics to doing a database, however a little light in vocabulary. So I shy away from using some of the technical terms. Much like when my other half is talking about storage on his computer and keeps calling it RAM... he knows the difference, but still uses the wrong term.

I've been looking for a local consultant, but no luck! So far all of them are Access folks, and I DON'T want to use Access!!! The few I've spoken to keep giving me a sales pitch on how good Access is, needless to say I wasn't impressed..... I'm a Mac person. I want to use a program that works for everyone Mac and Windows users.

[Rigsby]

I can only underscore what LiveOak has said here. If the files are stored locally, then you should be using one database for everyone. The most important thing here is if all the departments are in one physical location (building) i.e. connected over a LAN. If this is the case, then you need to have one database running on a central server. Do it now, get it right, or regret it later.

However, also as LiveOak said, this kind of solution can be tricky. If you already have this system up and running, use a workaround and take your time solving the problem. One workaround is making sure that all the databases are set to multi user. Then, lets say someone in “store A” wants to find if another store has item “B2345 coffee maker 9876A5432 1”. He’s going to have to open each database, one after the other to do a search – not a nice solution, but it works, and will be faster than searching 50 different files in a portal. Remember, that for cross-file searches, Filemaker has to create a new cross-file index each time a search is performed, which with a lot of large files can take literally hours in the worst cases. However, once you have a workaround, as mentioned above, the employees will probably complain, but it will give you time to find a better solution.

On one point I can’t give LiveOak 100% support on – although I also make my money consulting and programming, I would never recommend running directly to a consultant. If you have the time, give it a go yourself. You can always go to a consultant later – and from your existing files, he or she will get a better idea of what you are trying to achieve. Individual item listings can be very tricky, but not always. Once you have all the stored items, from all stores in one file, you can create as many “keys” as you want to get them to list or show up in any form you want. The trick is usually with self-links.

Hope this gives you a little moral support –

Rigsby

[LiveOak]

You still didn't indicate if these inventories are to be kept on a single computer or one at each locations...but I'll assume they are all in one place.

It is an EXTREME mistake to make each site a separate inventory file. Every time you add a site you will need to change the fundamental structure of your database, kind of like building a house where every time you want to go from one room to another you have to cut a door.

You probably want a structure with a product file with one entry for each product (kind of a catalog file) and a related inventory file with one entry for each product/store, if the store has stock. The catalog file is important because it contain information on items whether or not stores have stock. Also it reduces the size of the inventory file because no information except a key field, store no. and quantity need be stored in the file. This make searches much faster.

I found listing for 14 FM consultants in Ohio. Take a look at http://www.filemaker.com/solutions/find_consultants2.html and search on "OH" in the state field.

RE: "Oh yes there is one other little thing that affects this!!"

I am currently writing a database with includes inventory items which are both lot controlled and not. The need to individually list some items and not others is a major complication. This is a difficult application if you are completely literate and experienced in the use of FM, it is definitely not for the beginner. Inventory control systems are some of the most complicated database applications, not good beginners projects. I'd be quite careful which consultant I picked to do this sort of system.

-bd

[robinpow]

One big THANKS to both of you!!!

Yes, I agree it's better to start off right, then have to deal with it later. So I trashed it and started over and WOW one file is much easier to handle!!! My original thought was to do one, but because of the size, I was worried it would be slow. Well much to my surprise, not to bad (so far). To get an idea of how it would handle I'm working with about 50% of the records, which made a 16.5 meg catalog file.

As to where... well all the files will be in one location, but most of the people using it are around the world, it it will be "online". I know that will be another whole learning curve, but luckily I do have a good understanding of html and web design.

LiveOak: I followed the link, found the consultants however none are very close. However one did catch my eye, not only are they consultants, but offer classes. A class or two sure wouldn't hurt me. ;-)

Rigsby: Yes it gave me that "little moral support", That was much needed!!! One of the reasons I picked the log on of "The Blond" was because FileMaker was making me feel real dumb. As a rule I'm able to learn things very quickly, but this project was putting me in my place.

[Rigsby]

I’ve been using FM for almost 9 years now, and it still “puts me in my place” from time to time. LOL! But never give up is my motto!

One tip is always use small test files that you use just for solving one problem. You can save these and use them for years to come. When you have an answer, use it in your database. Good luck!

Rigsby

[digidiva]

if you are trying to pull inventory information from several different stores (databases), then why dont you just create a look-up/relationship page that queries all databases and pulls the requested information into that search page? the question is are you storing all of the databases locally or not.

you can also create one huge database and have each item associated with a particular store. Then of course, you need to ask, in either instance, how doest the database get maintained?

you can also create a webbased store shopping cart that has auto inventory control...so many ways, so little time...

[LiveOak]

I've never understood why someone would never think of taking out their own appendix will attempt a complex technical task for which they just don't have the experience. The key to this comparison is the criticality of the project. If this is a mission critical application, why take many times as long and produce a vastly inferior product just to say you did it yourself. I've seen so many companies put in jeopardy this way. If it is not very important to the company, take your time and build your job skills.

I had a biotech client that needed handling robots for samples to be gene mapped. Instead of searching out a company with experience in this area, they put a bunch of bright, fresh out of school electrical engineers with vast enthusiasm but no experience on the job. After a year and a half of working their tails off and spending a huge sum of money, the robots just didn't work right and they had to start over. I mentioned this to a friend of mine with about 35 years experience in control systems who works for a company specializing in robotic designs. He indicated that their problem was pretty simple and should have taken an experienced team about a couple of months. The cost to this company in the very competitive human genome mapping business was huge.

The moral to the story is that if you have a mission critical application, don't ruin you company based upon your own ego or the need to have a new hobby! The company's main mission may not be to see if you can become a database designer some day.

-bd

[Rigsby]

Ok! I’m the one who normally tells everyone to give things a go themselves. Don’t go running to a consultant at the first problem. However, LiveOak is right. If this is a critical project under time pressure, you could end up in a lot of trouble. Having said that, I would now be interested in how you’re getting one with the one file solution.

Rigsby

[Anatoli]

How true what LiveOak wrote.

But I have another success story.

Very good company here needs modern Info and Data Collection system. Their boss wrote the basic requirements in FM4 as working model and temporary solution. It was 20 databases.

He hired Professional SQL Company, paid them during a year of development 20k US dollars and after that they cannot show single working database. Just asked for another money. He fired them.

So he bought v. 5, extended his "demo" system and now he has fully working system with 45 databases running his company.

After that he turn to us, to get small help with connection of 5 additional cities to his system.

[LiveOak]

Sadly too true. The number of consultants out there who will take your money and not deliver anything useful is not small. The more knowledge of the product your consultant is using and visibility of the design process you have, the safer you are. Never deal with a consultant who won't take the time to explain what they are doing or why.

What makes FM ideal in this are is the amount of co-development we do with clients. There are many things the client can do, even as a beginner. Have the consultant layout the architecture (very important) and then split up the work based upon complexity. At the end of the process, the client ends up with a much better knowledge of FM in a shorter time than if totally self taught.

-bd