Many-to-Many, or not really?

[javabandit]

Hi Everyone!

 

I have a situation where I think I need a many-to-many relationship, but I'm struggling so much with how to make the user interface for the joins, that I'm beginning to doubt that many-to-many is really the answer in my case.  Here's the scenario:

 

We have tools that make more than one part number.

We have part numbers that need more than one tool to make.

 

So, I set up three tables:

partnums ---< join >--- tools

 

partnums::kp_partnum = join::kf_partnum

tools:kp_tool = join::kf_tool

 

So far, so good.  My problem is when I want to create an interface for a person to make the join relationships.  We will have a list of approximately 25,000 partnums, and maybe that many tools too.  But, we don't have a unique way to refer to the tools.  That's where my problem comes in.  We refer to them by the part numbers that they create.  Here's an example:

 

Part #123 requires a Mold, a Trim Die, and a Secondary

Part #124 uses the same Mold, and same Trim Die, but no Secondary

 

If we had to make Part #123, we would say, "Go get the Mold, Trim Die, and Secondary tool for part#123." Or, if we had to make Part #124: "Go get the Mold and Trim Die for Part# 124."  In this sense, my situation is different from a " students --< join >-- classes " type of situation because my tools are identified by the part number they create, and don't have recognizable "names" like classes do.

 

So, I can easily make a layout to display all the partnums in a portal, and all the tools in a portal, and a join table showing which belong together.  But, if I wanted to add a new partnum - say #125 - and it uses the same Mold, and Secondary from #123 & #124.... how do I identify them to pick those two tools from the tool portal?

 

I've attached a screenshot to try and help visualize this.

 

Maybe I shouldn't be looking at a many-to-many.... but I don't know what else I should do.  Any suggestions are very welcome.

 

[comment]

I can't follow your description. You say that the mold used to make Part #123 is the same as the one used to make Part #124. Why then do you call it "the mold tool for part#123"? And why do you call it "the mold tool for part#124" in another context? How many mold tools do you have in total, and how do you tell them apart in practice?

[javabandit]

Thanks comment, your question hits at the core of my problem.

 

You're right that the mold to make #123 is the same as the one used to make #124.  In this case, we call it a "two-cavity mold."  When we use that mold, we will produce two different parts: One from each cavity.  This is common with left-handed and right-handed parts.  Let's say it makes one left-hand bracket (#123), and one right-hand bracket (#124).   Customer A only buys left handed brackets, and Customer B buys both.  So, we will issue shop orders to produce whichever part# we need to fill our inventory.  So, if the shop order is for part #124, the person setting up the machine will go get "the mold for part# 124" which also happens to produce part# 123.  That's why we call it differently in different contexts.

 

How many molds do we have in total?  Roughly 20,000. 

 

How do we tell them apart in practice? There are locations assigned to them that are basically spots on a shelf, or a bin.  We have lots of shelves and bins containing the different tools, and each department has a printout of the whole list that shows the various part numbers, and where they are located.  So, in the example, the person reading the shop order would look at that printout, find the location, and pull out the tool they need to make the part number.  

That list is little more than a spreadsheet where things are roughly organized.  My project is to make this into a relational database where it's easy to see which tools you need to make a part# and which part#'s the tools can make, and where those tools are located.  Also... currently, there are three "spreadsheets" - one for Molds, one for Trim Dies, one for Secondaries.  I intend to make it into one "tools" table.

I hope this is more clear. Thank you for your consideration.

[comment]

So if I am sent to fetch "the mold for part# 123", I need to consult some sort of map that tells me it is located on shelf 123A? And the same map also tells me that "the mold for part# 124" is located at ... the same place. Is this correct?

[javabandit]

Yes, exactly.

 

That "map" looks something like the attached picture.  I'm only showing three lines for simplicity.  So, the second line means this:

The tool will make these part#'s 5116102 & 5116103.  It is located on Rack H, Row 1, Carton 7 (H 1 7).  The third line is similar - it makes part# 5113102 and 5113103.  The first line is for a tool that makes many part numbers.  In this case, these are all from the molds department, so these are only molds.  There are similar "spreadsheets" for other types of tools (Trim Dies, Secondaries).

 

Thank you again for your consideration.

 

Edited my profile:

Using FM Pro Advanced 13.0 - Win7

[comment]

Okay, then. Two more questions and a note:

 

1. What is the significance (if any) of splitting the part number into an ITEM and a NUMBER? Is the ITEM value unique to the row where it appears? And would it be correct to assume that any part whose part number begins with the same ITEM will use the same mold as other parts in the same group?

 

2. Coming back to your original question;  putting the structure aside, and concentrating on the user interface only: what would be the most convenient way to select a mold for a new part just now created? For example: would you like to see a list of parts that have the same ITEM value, and select from these? Or, perhaps it would be sufficient to indicate that this part needs a mold, and let the system pick the correct one by using the ITEM value? Or something completely different?

 

Note that no matter what your answer to #2 will be, it's not going to influence how your file needs to be structured in terms of tables and relationships. At most, it will require you to add some auxiliary TOs and relationships to the core structure, in order to facilitate some UI feature or another.

[javabandit]

I seriously appreciate this discussion.  Thank you.

 

1. The significance of splitting is due to the concept of "series" and "number within the series".  Together they make the part number.  So, for example, a customer of ours has an "Alpha" project.  We will pick the next available number to start a new series.   In real life, our next number will be 5581.  Then, each part for the Alpha project will be numbered 101, 102, 103, etc.  The part number becomes 5581101, 5581102, 5581103.  If the start a "Beta" project, we will start with the next series - 5582.  Similarly, when we get a new customer who has their own new project - we start a new series, say 5583.  The major caveat is this:  Not all part numbers are done that way.  We have lots of legacy numbers with no "series" - just numbers and letters.  That said, with my new solution, I am trying to avoid the "series" and "number within the series" model.  I am trying instead to just have partnumber be a field of its own.

 

2. The UI - Putting structure aside: I think the best way for a user to make joins is to allow them to enter partnumbers in the partnum table, then either add a brand new tool to the tool table (because the tool doesn't exist in the table yet), or select an existing tool from a portal list.  I tried to do this already by creating a calculated field in the tools table that was just a List of the partnumbers.  It's like this:

c_partnums_for_this_tool = Unstored, = List ( partnums::partnum )

I can present this to the user in my tools portal for them to select from.  So, as far as that goes, it's okay.  But, the problem is that I will have so many records in that list that they will be scrolling through 20,000 or so records to find the one they need.  I also tried filtering by tool_type, so they shorten the list a little, but it's still going to be between 2,000 and 9,000 records or so to scroll through depending on the tool_type.  Finally, I attempted to create a relationship to let the user "narrow in" on the list by typing the part number in a filter field, then making a relationship like this:

    c_filter >= c_partnums_for_this_tool
AND c_filter <= c_partnums_for_this_tool

where c_filter = List ( g_filter ; g_filter & "ΩΩΩΩ" )

It's a technique I use a lot to narrow down a long list of part numbers in a portal.  It doesn't work here, though, because c_partnums_for_this_tool is an unindexed, calculated value.  So, the relationship doesn't work.  

Okay, I think I went overboard in explaining things.  I hope I didn't muddy it up too much.


 

[comment]

I am not sure I fully understand your answer to my second question. I asked what would be the ideal way for a user to select a tool (from already existing tools) for a new part. Naturally, they would select a type of the tool (e.g. "Mold") first. But then what? Do they really have no way to narrow down the list of molds, other than entering some known part number that the mold already makes? Mind you, there are ways to make that work - I am just concerned that the user must know (or find by some means) the "other" part's number; it seems like the system should be more helpful in this aspect?

[javabandit]

I'm sorry, I guess I did muddy it up too much.  I'll try again. 

 

When it is time to add records to the database, the user will have a packet of information for the project.  The packet will show the new part numbers that we will be producing, and what tools are needed to produce those numbers.  So, the database user will already know ahead of time what part numbers and what tools go together.  So, going back to my original post, they will have this kind of info at their fingertips:

 

Part #123 requires a Mold, a Trim Die, and a Secondary

Part #124 uses the same Mold, and same Trim Die, but no Secondary

Part #125 uses the same Mold, but a different Secondary

 

Ideally -

The user will first add a record to the partnums table for Part# 123.  Then, they will add three new records to the tools table, one for the Mold, one for the Trim Die, and one for the Secondary tool.  These records are joined to Part#123.

 

Next, they enter Part# 124 in the partnums table.  They will need a way to select the existing Mold and Trim Die records from Part#123, and join those two records to Part#124.

 

Finally, they enter Part# 125 in the partnums table.  They then need a way to select the existing Mold from Part #123 and Part #124, and join it to #125.  Also, they need to add a new record for the "different" Secondary tool and join it only to #125. 

[comment]

Okay, then. I think that in terms of structure, there can be very little doubt that you should have a join table between Parts and Tools. Based on what you said about users coming in with the new part number already known (from where?), I believe you should base the relationship between Parts and the join table on a PartID (an auto-entered serial number field in the Parts table) and of course, on a similar ToolID linking the Tools table to the join table. These IDs do not need to be exposed to the users.

 

In terms of user interface, you need a mechanism that will allow a user to enter an existing part number, and select from the tools that are used to make this part. My inclination would be to script the entire process of creating a new part. This would allow you to present the user with a custom dialog asking them to enter a known part number into a global field; then the script can perform a find and pop up a new window (using a list layout of the Tools table) with the tools used to make the entered part for the user to select from - if more than one such tool exists.

 

If unscripted, you will need a set of auxiliary relationships to show these tools in a portal, based on the user entry in a global field. These could be:

 

Parts::gPartNumber = Parts 2::PartNumber

 

Parts 2::PartID = PartTools 2::PartID

 

PartTools 2::ToolsID = Tools 2::ToolsID

 

 

If you add a global gToolType field to the PartTools table, you can make the last relationship:

 

PartTools 2::ToolsID = Tools 2::ToolsID

AND

PartTools 2::gToolType = Tools 2::ToolsType

 

This way, if the user enters a part number into the gPartNumber field and a tool type into the gToolType field (defined in the PartTools join table, but present on the layout of Parts), they will see a list of matching tools in a portal to Tools 2.

[javabandit]

Your first paragraph describes what I already have.... a partnums table, a tools table, and a join table using the primary keys.  That's where I started on this.  And, that's were I was also  beginning to doubt myself.  So, I appreciate you confirming that I was on the right track with a many-to-many relationship. 

 

To answer about the source of the data:  It comes from the Engineering dept.  Whenever we start a new project for a customer, the Eng. Dept. assigns the part numbers, and determines what kind of tooling is necessary to make new parts, and also if similar parts can be made in the same tooling or whatever.  So, they put that info into a packet that includes all kinds of other info about how to process the parts, production rates, drawings, quality info, etc. 

 

As for the UI recommendations, I think I understand what you are proposing.  It makes sense to me, I just need to try it out to really know if I understand or not.

 

Thank you, comment.  I will give it a try today and post back my results later.

[javabandit]

Okay.  I've got it working.  There are two files in the attached zip file.

 

forum_help1.fmp12

This is a new database file, where I attempted to create the tables, fields, and relationships that you suggested.  I think it's successful.  The layout I created works, and is super basic. 

 

forum_help2.fmp12

This is a copy of the first one, where I added some things that will be closer to how I will want the user to interact with the database.  I realized that the gToolType field, although working as you suggested, was probably a little too narrow, and not necessary for selecting tools that need to be joined.  So, I eliminated that.  In general, a single part number will not have more than 7 or 8 different tools, so we don't need to narrow by type.

 

It's probably going to take some time for this to sink in so I can understand the relationships.  It's fairly easy for me to think of something like Students--<Join>--Classes.  But, if you replace Students with PartNumbers, and Classes with Tools... I must have a special case on my hand since the Tools are only known by the PartNumbers they produce.  They don't have an identity of their own like a Class does.  It's a little strange.

 

I'm really happy with how this is working, and I really appreciate the help you have offered.  I'm going to keep these two files as examples and work this logic into my solution.  It will be neat for me to see how it all works when I have all the other data fields available, and see how the users will be able to manage the data.

 

Thank you again.

 

 

<Edit:  Attached the file that I forgot to attach before>

forum_help.zip

[bruceR]

Oops, no files attached.

[javabandit]

Oops indeed.  I went back and attached the file on the previous post.  It's there now.

[LaRetta]

I have little time this morning to review the file thoroughly but two things I would recommend upon quick review:

1. In your Make_Join script - try using the new Refresh Object [ "portal_Show_PartsTools" ].  Refresh Window [ flush cached join ] is very heavy-hitting while the new Refresh Object[] is lean.  Sometimes it will not handle portal refresh but it seems to work when I try it here.
2. In your Un-join script - move the Enter Find Mode[] to immediately before your Go To Layout.  In this way, records from Part Tools do not load just because you are landing on that layout (since you are in Find mode) but instead only load records once the find is performed.

BTW, I have read that Freeze Window can achieve the same results as suggestion 2, stopping record load, but I have been getting mixed results when testing.  I KNOW method 2 works.  YMMV.

Edited April 1, 2015 by LaRetta

[javabandit]

Brilliant LaRetta!  I made both those changes as suggested.

 

I use that Go To Layout, then Enter Find Mode all the time.  I'm thinking I should go back through some of my other Filemake files and reverse that.  I could probably get some pretty nice performance gains, especially on those that have a lot of records.

 

Thanks for the tips!

[LaRetta]

You will certainly notice substantial speed gains by entering find mode first.  I'm pleased my comments were helpful.  As for everything else on this thread, continue listening to Comment - you can't get better assistance than that. 

[javabandit]

Hey Everyone,

 

I've been arranging and polishing this file, and I am finally ready to present it to the group here that will be using it.  So, I figured I'd post the file in case anyone is curious how it looks now that I have everything in place.

 

Thank you so much for all the help. (especially comment)

 

Of course, I would love to hear if anyone has any further suggestions for improvement.

 

Thanks again -

Tool_List.zip