New Database Needed!

[HarryV11]

Hello Board,

Please excuse my general ignorance. I have used FM a long long time ago.

We are a small manufacturing company and a week ago our current software basically broke. It was being installed over a 6 month period but essentially it was too complicated for us.

So, the software has been ripped out and we have reverted back to a paper based system. Details are being entered into our Sage Accounts software so Sales Orders can be allocated with stock and despatched.

I have been tasked with building a very simple database that we can begin to expand.

So, i wanted to start by using the Starter Solutions in Pro v11 to build components of the system and then link them together.

The database needs to do several things. Firstly, i need to be able to add products to the product list, with associated details. Then I need to be able to raise a Works Order for 'x' number of products.

After that, i need to build a BOM structure so we can calculate how much raw materials have been used that day. For now, i am leaving that to one side.

Eventually, I'd like to have all our computer based work-processing stuff done in FM. We have had a lot of IT issues and I'm determined to sort them out this year with Mac based servers and clients and FM.

Anyway, I have opened and created the Inventory solution and imported my products into it. So, I have a Stock Code, which is unique, and also a description. There are, ofcourse, other fields such as EBQ, time to manufacture etc etc.

My intention was to then use the Purchase Order Starter Solution so that 'Works Orders' could be 'ordered' by the operator. We'd then print them out and give them to the factory manager who'll go and make them. I want this to become more complicated, so it would show the time taken to make them as a total etc etc. But for now, just creating and logging the WO's and being able to 'Deliver' (complete) them is OK.

I don't want step by step instructions, but rather I'd appreciate some guidance on the overall path i should take for each step. I'll be coming back here and trawling the forums, as I have already; but right now i need to get some things moving.

So, the 'Stocked Parts' db is up and working as it needs to.

I am working on the Purchase Orders/Works Orders db at this time.

My question (we'll get there in the end) is, if I want to have a drop down list in the P.O. screen that shows all the Stock Codes from the Stocked Parts db, how do i do this? I've been to Manage Database and linked the fields together, from the external tables to this one, but it doesn't 'lookup' the data and autofill.

What's the process of getting it to do that?

Thank you for your help,

H

[comment]

I would suggest you start from scratch instead of using a template. This way you'll know exactly what you have put into it and why. It would also be better to keep the solution in a single file (at least until you have a really good reason to split it up).

Re your question, perhaps this could be helpful:

http://fmforums.com/...hp?post/309136/

[HarryV11]

Hi comment,

Thank you for your tip.

I have stripped away a lot of the fields that I don't need right now, and their calculations etc. to give me a basic set of fields and a fairly nice/organised layout. So it's almost like starting from scratch only it looks better!

I don't need to enter line items just yet - each Works Order will have just one line. More will come later.

I have read that link at it seems like it's one step beyond what I want to do in this first instance.

I have a Stock Code & Description in the Stocked Parts DB. I want to enter the Stock Code in the Purchase Order screen and have the description come up when you tab out of that field. It seems the guy in your link has already done that.

How do i do that first part in your link - create a drop down list of the products in my Stocked Parts db?

Then i need to work out how to Auto-Enter the 'Description' field from the Stocked Parts db also...

[comment]

How do i do that first part in your link - create a drop down list of the products in my Stocked Parts db?

File > Manage > ValueLists…

After you have created a value list, you attach it to a field on the layout, using the Inspector.

Then i need to work out how to Auto-Enter the 'Description' field from the Stocked Parts db also...

You only need to do this if the description is liable to change and you want the order to preserve the historical description. Otherwise you can just place the Description field from the Stocked Parts table on a layout of Work Orders.

[HarryV11]

Thanks again,

Well, i want the 'ValueList' to be the 'Stocked Parts Code' from the Stocked parts db.

So, when i enter a new product in that DB, I will be able to access it from the Works Order DB.

The stock code, description, and two other details (manufacturing time) should autofill into a new table, so i can print reports on that record. I don't want those values to change if the Stock table changes, so they will be a record of what we made, regardless of if the description changes. Does that make sense? As your last suggestion.. "You only need to do this if....", which is what i want to do!

[HarryV11]

I seem to be finding that i can't specify that field as a 'LookUp' field in Field Options, because it is not a Local File, but rather a related DB.... is that right or should i be able to select and auto fill?

e2a: I think a lot of what i want to do is centred on this concept, so I'm trying to crack the idea in my head.

[comment]

You should be able to lookup from any related table - regardless of which file the related table resides in.

Nevertheless, I suggest again you put all your tables in the same file.

[HarryV11]

OK, that would make sense to me, sure. How do i copy the tables and layouts from one db file to another?

I've propagated the Stocked Parts db with my data via import AND good 'ol key punching and I like the style of the layout for the moment, so i don't want to rebuild them.

I've tried copy/paste but that aint workin'!

[comment]

Unfortunately, you cannot copy/paste or import layouts as such - but you can prepare an empty layout and paste all layout objects into it. As long as the underlying tables and fields are named the same, the pasted objects will re-attach themselves.

[HarryV11]

If I could just bring the Stocked Parts table into the WO Production db, so i could see it on the Relationships view, i could rebuild the layout i suppose.

That *would* make more sense in my brain, having all the tables we need now and later all in one db file.

But i can only find info on importing records into a table with other records, not a table into another db.

[Lee Smith]

I have been tasked with building a very simple database that we can begin to expand.

Hi Henry, welcome to the forum

You might find this thread of some interest. http://fmforums.com/...s-us-seriously/

that was started by comment almost 4 years ago, and turned out to be one of our most popular topics. "Does anyone takes us seriously?"

Lee

[comment]

But i can only find info on importing records into a table with other records, not a table into another db.

When you import, select [New Table] as the target. This will bring over the entire table - structure and data.

[HarryV11]

OK, not read it all but I get the sentiment....

I would pay for a developer - can you be here tomorrow? I will pay someone to write and build this to my spec, no problem.

We've just binned a £30K+ system that was too complicated and we need a replacment.

If i could employ, tomorrow, a developer that would sit on my desk with my FMPv11 and build a system to my spec, while taking feedback from users and incorporating features (i need to print this/i need to have these labels from this printer when i do this/can you make it blue/can you calculate the result of this and put it into a report/ etc etc) to build a system that i retain copyright on, then I would.

Right now, there are people in the office downstairs handwriting thousands of lines of WO/BOMs etc that use to be done by computer.

I have no desire to spend time learning about DB's and developing them and all the stuff that goes into it, that's not my job and I'm not that good at it adn there are people who are better. I am middle management and I have the authority to engage someone in this process to do this for me - but finding the right person, getting a quote, specing out my needs, will take a week or two or three. Meantime, production and sales admin must continue.

I'm the best in the company as i've built one small successful database a few years ago. So at this point, i'm trying to get something up and running to allow day to day business to function.

Trust me, i would rather not be doing it, i've got better things to do.

I know this sounds desperate, and to a degree it is, but all help is appreciated. I in no way demean or undervalue your skills as developers, i applaud them and i know the value (in money terms) and am happy to pay for the right thing.

Is there a place where i can post a 'Hi, I'm an inexperienced user who needs a developer' post so people can quote me for work?

[Lee Smith]

if you are serious about hiring a consultant, you can find one in your area that could do what you need (most likely) by going to FileMaker site here: http://developer.filemaker.com/search/ and filling out the questionnaire.

HTH,

Lee

[HarryV11]

I've contacted a few people off this list and everyone wants to sell me a product they've already made. I don't own it, no one else can support it/access the mechanics of it.

We've been down that route and it doesn't work long term as things change.

Thank you, I'll keep looking.

[Ron Cates]

I don't think the reference to the "Do they take us seriously" post was meant to suggest that you should hire a developer rather than attempt the project yourself. Many of us began our new lives as filemaker developers in much the same way. When I read "I have been tasked with building a very simple database that we can begin to expand." I instantly thought of the openning line of that post and even went looking for it myself so I could post it.

"Hello, I am very new to Filemaker and databases in general, and I have been tasked with the responsibility of creating our company's new database."

So welcome to the forum. You've found the right place for guidance and support as you enter into the world of FileMaker development.

Good Luck :)

[Lee Smith]

I agree with Ron about the article that I referred you to. It was not meant to discourage you in proceeding with developing your files. It was only meant to point out how underappreciated in-house developers are.

I hope the people that you called were only pointing out that it would be cheaper to buy an off-the-shelf product then to develop something from ground up. As Ron pointed out, these list are full of people who would be happy to help you walk through the process.

Lee