Need help with Inventory Problem

[mattz]

Hello, I'm new to this forum, and somewhat new to Filemaker. Perhaps I should have listed beginner, I'm not sure.

Here is my problem. Our system here at work has a Sales Order page. With it there is another page called the Traveler. On this traveler I'm trying to create a button that will copy our Part Number from a field, go to our Inventory database and find that number. I am having a horrible time with this. I have gotten it to the point where it goes into the Inventory page and sits in the FIND mode and just does nothing. I can't get it to paste text from the other database.

I hope I explained this somewhat well...

Thanks in advance...

-Matt

[AudioFreak]

Hi Matt,

The easiest way is to set up a Relationship using the Part_Number field in both files/tables as the key field.

Once that is done you can simply use the "go to related record" script step. No find mode, no copy/paste. Might as well warn you now that using copy/paste in find mode is bad practice and can cause major problems for you.

Michael

[mattz]

Michael -

Thank you for the fast reply. I am trying to do what you said, but I'm having issues. I think I know a little less of this program than I thought! I tried something and ended up deleting part numbers some how...luckily I'm using a backup copy and not the live working copy!! I'll just keep messing with it, trial and error, right?

Thanks again,

-Matt

[_henry_]

Hello Matt,

Just want to let you know about copy and paste issue. It would be better to use "Set Variable" and "Set Field" rather than "Copy" and "Paste".

Here is one of the forum link that give you more information:

http://fmforums.com/forum/showtopic.php?tid/191891/post/274910/#274910

[mattz]

Ok guys. I'm going nuts here. I'm still having problems. I've taken a screen shot and highlighted some things. What I have is 6 fields that have part numbers in them (highlighted in yellow). I'm trying to take those numbers and go into the inventory page for that part number. These part numbers are Raw Material part numbers, not finished part numbers. On the same page, there is the FINISHED part number (highlighted in red). For some reason, it is pulling that finished part number instead of the raw material part number. I'm not sure how to fix this. Here is the screen shot of the page:

Anybody have any ideas?

Thanks again,

-Matt

[mattz]

Going back over what I have done so far, I looked at the ESCO P/N fields themselves. What I am doing with them is having them filed in with data from another file, Part Numbers.fp7. So I can't be sure that the field names I had set before I set the data to display from the other database are correct. They were Trav Esco PN a thru f. Now they show as ::Material Part Number 1 thru 6. Could that be what is messing the relationship up?

-Matt

[AudioFreak]

Can you post a screenshot of the relationship graph? Or maybe a copy of the file?

Michael

[mattz]

Here's a screenshot of the relationship for this file...I can't really post the files due to client names and info...sorry.

Thanks!

-Matt

[Søren Dyhr]

When I see a juicy Jazz-chord like that, am I pretty sure normalization is somewhat incomplete ...

Are we dealing with assemblies or sets of partnumbers where the whole makes a new partnumber??

--sd

[mattz]

The ESCO FINISHED PART NUMBER is the finished part, then in the grid in the middle is ESCO P/N which is each piece that makes the finished part. I am trying to get to the inventory page for each part, not the finished part.

Hope that made sense...

-Matt

[Fenton]

Søren has understated the problem.* If there are multiple parts then there needs to be, at the least, a join table for the combinations (with a kind of self-join back to Products). That is, if the "parts" are also sometimes "finished products" themselves.

If they are always separate, i.e., never a Product in themselves, then you need 2 tables to handle them, one for Parts, and a join table to Products; that is, if a Part can be in multiple Products. If every Part belongs to 1 and only 1 Product, then you don't need the join table, just a Parts table. These are actually simpler, less confusing anyway. But one of the 3 above structures is true, and you must know which, and set it up before you even think about inventory.

What you've got, with field1, field2; fieldA, fieldA, is almost never the way to solve this kind of problem. Your relationship says: every field1, field2, etc., and fieldA, fieldB, etc. must have a value, and each field1, field2, etc. must match at least one of fieldA, fieldB, etc.. At least that's what I think it says.

*Søren's Skill level is not "Intermediate". We are not quite sure what it is, eclectic expert?, but it's certainly more than Intermediate.

[mattz]

Wow...I'm starting to think I am in over my head here...I'm actually kinda lost. I have the "Missing Manual" book, and now I think it's time to crack it open and read it til my eyes bleed. I think I bit off more than I can chew here. I thought this was going to be simple...

I'm going to have to read up on it. The guy that originally started this database for my company is long gone, and I never used Filemaker before working here, but now I am the one who makes the changes and is trying to implement new databases (Inventory for example).

Thanks for your help. I will see what I can find out and probably end up right back here!!

-Matt

[Søren Dyhr]

We are not quite sure what it is, eclectic expert?, but it's certainly more than Intermediate

Nice one for the drastically uneven spread it is!

I have the "Missing Manual" book, and now I think it's time to crack it open and read it til my eyes bleed

I pretty sure you won't find recursive structures in it at all ... it would be way too distracting. Beyond that isn't it something that belongs to a manual at all. It belongs to database "architecture" way beyond one2many, many2many and one2one!

Read Fentons clasifications, and tell us which your's is, and there will be some guidance from us - it's needless to get you into deep by linking you to articles you would have difficulties to grasp anyway!

--sd

[mattz]

Thanks Soren!

Here's what I have.

Each part that makes up the finished product is usually used in another product. For example, .125" thk x .250" wide neoprene sponge w/psa is used in a ton of finished parts. So each piece that's used to make a finished part is used many times.

Is this what you are looking for?

-Matt

[Søren Dyhr]

Ah it is indeed a many2many relational structure, excellent choise of battle for a novice ... it's just taken me somewhat 5-6 years to grasp it. Perhaps Fenton could suggest a crash course to get you there in a jiff??

What I would dislike very much, is the notion that some-one else came to rescue with a blackbox'ish approach ... similar to my grandad cured his tv-set by knocking on a specific point when the screen started to roll.

--sd

[Fenton]

We're getting closer. But there is one important question yet. Is ".125" thk x .250" wide neoprene sponge w/psa" or ANY of these "parts" EVER sold as a separate product? Because then it needs to have a dual nature, sometimes a part, sometimes a product. It can be done, but it's less trouble if no part is ever sold as a product.

Though even then, there are pros and cons. Because a Purchase Order line may be for a Product or for a Part, so if they are separate it would have to be able to handle both; and keep clear which was which, so it knew which to look at from its side. Unless you only ever buy Parts (which are never Products), then PO's are also fairly simple.

[mattz]

Well, sometimes the individual pieces are sold as parts in some cases....

[bcooney]

Ah, inventory and recursive data structures. I know that you mentioned that linking to articles may add confusion, Soren, but Jonathan Stark's article is so well-written ( Link)

[Søren Dyhr]

Perscriptions are when not given cynically bound to include both parts: The medicine and the method of ensuring correct use. Exactly with recursive structures, is there a great danger the patient thinks it's just another trickery to apply ... unfortunately is the notion of a needed trick an euphemism ... a false feeling of having accomplished 95% only needing a deux ex machina for the remaining 5% ...

Perhaps you have a way to redirect an inadequate structured solution to allow the implementation of a recursive structure so it fully integrates? The tough truth is that you need to rebuild from scratch, with the recusions well planned.

However if the solution can bear that the actual stock levels is of less importance, say we just issue an invoice without the wish to know ahead if it really can be delivered or not, does Don Wieland provide a method to deal with it with these acknowledged limiting premisses:

http://www.dwdataconcepts.com/dl/tw/compinv2.ZIP

The template is an old one in fm5 format, which needs to get converted to fm7 ... the principles he uses are pretty intuitive to developers of all skill levels!

By rereading mattz post can't I find anywhere where he actually need levels on the inventory, only a convenient way to facilitate the plucking.

--sd

Edited January 19, 2008 by Guest

[mattz]

Soren -

Actually, the inventory levels are the important part! I need a button that goes to the specific inventory record for the part I am clicking on. Essentially I'd like to make the part number itself a button that goes to that record so I can see what we have in stock when writing up a job.

Is that a little more clear? I hope I'm wording this correctly...

-Matt

[Søren Dyhr]

The difference is if you wish to know many Bloody Mar(y/ies) you with the present stock-levels can produce, or you just wish to know the levels of Vodka and Tomatojuice separately.

--sd

[mattz]

To use your analogy, i'm looking for the vodka and tomato juice seperately...

I need to know how much of any given material per finished part we have.

[Søren Dyhr]

Then would a recursive structure be overkill, what it just takes in the itemline is a Gtrr to items, where you the inspect a Sum( backwards over the relation.

You could then have a Kanban level for each item setting a binary result you can search on, eventhough the search is unstored, will it provide you with ordering list pretty fast.

--sd

[mattz]

Wow, I have no idea what you just said! What is a Gtrr? I'm still learning this stuff! :

[Søren Dyhr]

It's an abbreviation of this:

http://www.filemaker.com/help/Script-Steps19.html

--sd

[mattz]

Ahh, duh! Thank you. I am going to take a step back here, do you think reading a book like the Missing Manual and the "Multiple Tables and Relationships" chapter would help? I seem to be ignorant to most of the terms here, and I don't think it would hurt...

-Matt

[Søren Dyhr]

I don't know the book, and would never buy it ...but it doesn't mean that it can't be handy in your case. But I would perhaps rather spend some time with this:

http://www.digfm.org/ref/FM7_key_concepts.pdf

--sd

[Lee Smith]

Soren,

That is because these books are not directed at Advance or Expert Level Developers like yourself.

Hi Matt,

There are several good books out there that can help you learn FileMaker. The Missing Manuel is one that I don't have, so I can't really comment on it. Check the Resource Topic, as I believe others have done so though. There are others that I do own, and I can Recommend. I would start by trying to find the older edtion of "Special Edition, Using FileMaker Pro 5," by Jonathan Price, Rich Coulombre, It walks you through making an invoice. I can also recommend the v8 edition, by Scott Love and Steve Lane, and its companion "FileMaker 8 Functions and Scripts Desk Reference" by Scott Love, Steve Lane, and Bob Bowers.

HTH

Lee

Edited January 22, 2008 by Guest

[mattz]

Lee -

Thanks for the suggestions, I will look into those.

-Matt

[Søren Dyhr]

That is because these books are not directed at Advance or Expert Level Developers like yourself

Don't flatter me - I'm against the entire concept of:

Sams Teach Yourself x in 24 hours.... the "Baked Pigeons" concept if you wish.

--sd

[Lee Smith]

Don't flatter me - I'm against the entire concept of:

Sams Teach Yourself x in 24 hours.... the "Baked Pigeons" concept if you wish.

--sd

Whether you believe in it or not, there is often no other way for someone to learn things. So, if self taught method doesn't work for you, what does?

I'm probably going to regret asking this, but [color:blue]what is the Baked Pigeons concept?

Lee

[Søren Dyhr]

This:

http://www.fmforums.com/forum/showpost.php?post/278867/

--sd

[comment]

The concept is a little older than that - see #1014 here:

http://latinviaproverbs.pbwiki.com/group076

[Søren Dyhr]

I didn't mention the age at all, we have the same proverb in danish, but here are they fried in stead of baked, almost the same way as we do with pheasants, it makes an excellent gravy/sauce... which would go amiss if done in the oven!

--sd

[comment]

Why do you torture me so? :