FMP6.0 PopUp and Field Issues help needed

[amada8]

I have a production sheet that has worked as our "shop" paperwork for years. I would like to tweak it a bit to make it do more for me and the production manager. We manufacture custom metal products and into some of these items, we install certain hardware. Right now, we have a plain text box where the PM types in the Supplier/part number/quantity of each piece of hardware that needs to be installed. We would like to opportunity to add the cost of the hardware to be calculated on the production sheet.

 

A Simple tree of this would look like:

 

 

Supplier A---Nuts-----832R1

----------------------832R2

----------------------832R3 (there are about 20 part #s per category)

------------Studs---1032M1

---------------------1032M2

Supplier B---Standoffs----B54221

-------------------------M45Y1

-------------------------108YYM and so on.

 

My question is twofold.

#1 In this particular problem, the supplier of the material is irrelevant and doesn't need to be added to the production sheet. I would like to add a single box with a Popup Menu the would have choices "Nuts" "Studs" "Standoffs" etc. When I click on Nuts, does FMP6.0 have the ability to popup a second Popup Menu with "832R1" "832R2" "832R3" etc??? This would give me the part number in only the space of one box on the page.

What I have done already is made a field of each part number (630). I can create a value list with all 630 fields, but that gets tedious to scroll through. There are but 21 categories (nuts/studs/standoffs etc) so I was hoping to use a second popup to speed data entry.

 

#2 In the example above, part number 832R1 cost $0.11 each. When I created the field 832R1, I set it as "Type:Calculation" where the formula was (.11 * 1). I then set up a box next to this with Qty and a final box with Total. If qty is ten, the total figure "Box1 * Box2" is calculated at (10 * 832) = 8320. grrrrr. What am I doing wrong? I want the box to print out 832R1, but I want it to have a mathematical value of .11

 

I hope I have more luck with this forum than I had at another site.

 

Anyway, Thanks for reading and have a great day!

 

Dave

Albany, GA USA

[Maarten Witberg]

1. see here: in post #11 and later a method to create a conditional value list in version 6.

 

2. you're mixing data types. that's not going to work. You need to create an item price field and base your calculations on that. It's much better also because next year the price may go up to 12c and then you don't need to go into all these field definitions, just change the item price.

 

maarten

[LaRetta]

Whoa.

 

630 fields to hold each part number? And now you want to price them within a calculation?

 

You need a Parts table which includes your pricing. You can then eliminate all 630 fields PLUS eliminate the will-be-very-long-PIB Case statement with simple lookup (or auto-enter). Solve it right ONCE. You are missing the power of relational and you are turning your design into a flat-file structure. It will cause you nightmares.

 

Using another table to hold your parts and prices will be piece of cake compared to the approach you are undertaking ... guaranteed. smiley-laughing

 

LaRetta

[Maarten Witberg]

Hey LaRetta

 

good pickup, I missed that. Opps!

[amada8]

Thanks to both of you for the reply. Will look onto creating and using a separate table.

[amada8]

I am such a noob. Is there such a thing as

 

I created a second file "HW" (as in Hardware) and entered the 630 records with just three fields:

Name

Price

Keyword

 

(Keyword will hopefully be used to sort the 630 into manageable lookups. Right now when I hit the Name, the popup is uhhhhh, er, huge. I can type in the Name, but I like the popup idea here. meh)

 

In Master, I have setup a relationship between HW Name and Master Name. I have setup a relationship between the two Prices. What I don't know how to do is to set up the relationship between the Master Name and the Master Price. It all seems so simple but how do I get the price to automatically fill Master Price after I have choosen the Master Name thru a lookup to HW Name?

 

I have figured out a workaround in that when I popup Names, I have set it up to also display Price. I then have to manually add the price to the Price box. If I can't figure out a way to use Keywords to make the popups more manageable and resort to only typing in the Part, I think I lose the price display option.

 

Like I said, is there any skill level classification lower than

[LaRetta]

Ah, you're doin' great ... we all were noobs at one time or another ...

 

Let's talk about your hardware a moment. Those 630 records aren't all the same, are they? You have Nuts, Studs and Standoffs? You need to categorize them by adding a field in your new Hardware table; maybe called Group. Assign each piece to its correct Group. You may also want to add a Supplier field. Once you have these 630 records broken down into manageable bites, then you can use conditional value list as you indicated you wanted ...

 

Select Supplier popup:

... all Suppliers will list. Select the correct supplier.

Select Group popup:

... only Groups made by that Supplier will list. Select the correct Group (nuts, etc)

Select Parts popup:

... only parts from that Group and that Supplier will list. Select the part.

 

When PartNo is entered, it will establish the relationship from MasterName::PartNo to HW::PartNo and will look up the price. There are other ways of handling a long list such as yours, but I suggest you first Group them.

 

Now ... you are joining HW Name and Master Name. Major incorrect ... you need to be using unique IDs in ALL tables. This type of structure is no different than Invoice to LineItems ... you have ONE (Master Name) to MANY (HW Name). HW Name is similar to a Products table. So to add many parts to the Master Name, you need a LINEITEM for parts. So you would use your Part Numbers (if they are truly unique). It would help if you could attach your file so we can see your existing structure and keys being used. Dave, the base structure is the most critical ... if it is done right then the rest of your work will be easy; if incorrect, it will be major PIB to work with.

 

Once structured correctly, use kjoe's example on establishing the conditional value lists.

 

LaRetta

[amada8]

I have a feeling that you are my angel, LaRetta. smiley_cool I see you are in OR... I lived in Corvallis for a few years in the early '90s.

 

Quick reply as I intend to hack at this a bit later tonight. I thought I was grouping by using keywords... maybe not tho. Would I make the conditional value list in HW or in the Master?

 

Thanks.... and I'll try to use your above info and the answer to my question tonight and will post results. After getting the conditional popups to work, I will post DBs for you to laugh at...... OK, maybe not, but you get the point I bet smiley-tongue-out

 

Dave

[LaRetta]

Ah. Keywords is the Group? I thought it was a short name of the name of the part. Then yes, this is correct. smiley-smile

 

I don't laugh at anyone. Sometimes I've been known to cringe or shake my head ever so slightly. Okay, a few times I've gasped. But I never laugh. I tried to private message you but you have it set to not receive private messages. You might want to fix that if you can.

 

Yep, Dave, you're about 50 minutes northwest of me. There are several Developers in the Portland area and even an FM Pug chapter!

 

Ooops! Upon re-read ... USED to live ... forget the part about being 50 minutes from my location. ROFLMAO!

 

LaRetta

[LaRetta]

One more thing ... as I mentioned (but not clearly), it appears that you still need another table here - a table to hold the many parts selected for each Master Name; otherwise, how can you add up the prices of the many parts to include with the Master? But that is where my vision stops ... I don't know if Master Name is ALSO a LineItems to ONE invoice or ONE customer. This is why I wanted to see your structure.

 

When in Master Name, you will be added several Parts RECORDS to a third table. Make sense? It probably won't until I can see what you have and explain it using your terminology. Let us know how it goes. smiley-laughing

[amada8]

LaRetta, my angel, I am but adrift at sea. After paddling for hours, I have not made headway. It's very frustrating as I can see land, but no matter how hard I pull on the oars, landing ashore is but an illusion, a mirage if you will. I'm setting out the sails right now in hopes of a gentle breeze to lead me to the promised land. Not that you are a blowhard or anything, but can you see helping a sailor by adding a bit of wind to my sails?????? (OK, it's a little overboard... smiley-tongue-out .... but I'm LOST AT SEA! Why do they even rent boats to people like me?? smiley-laughing )

 

Attached is a very slimmed down version of my main DB (Production.FP5) and the parts DB (HW Copy.FP5)

 

The lower left of the Main DB is the "problem". It works as is, but not efficiently. I can type in the quantity of a particular piece of hardware in the first left box, popup hardware choices (with price in popup), then type in the price that was shown in the popup in box three. The calculation shows up to the right and up several lines in box "PEMTOTAL". So I get all the info I need onto the production sheet.... it's just not as quick and easy as I was hoping for.

 

Right now I have four lines for data input on four different pieces of hardware that might be added to each completed item. I will probably add a few more lines. Most of our jobs involve only one or two differing pieces of hardware.

 

Wish list :

1. A Box between PEM1QTY and PEM1 that will give a popup to choose different Groups. Groups I have started value lists (in HW Copy DB) are:

Group #1: BSOS SO SOS

#2: CLS CLSS S

#3: F FH FHA FHS

#4: PEM MISC

 

In the Parts DB, I have them classified through "Keywords" and I have a Value List "Keyword choices" that have the groups above listed. There are more, but if you can help me get started, I can add the others at a later time.

 

2. Using the answer to #1, the choices for the popup window PEM1 will then be restricted to a Value List of Product Names for the appropriate Group.

 

3. and finally, the price of the chosen Product Name automatically will be entered into PEM1Price

 

Geez Louise, this all seems so simple when I read how to do it in the "Help" section, then I'm all thumbs when it comes to implimenting. I've tried so many types of relationships and Cases and Ifs.....grrrrrrrrrrrr, my head aches Opps!

 

Any chance I can get you to help a noob out of a jam?

 

Thanks

[LaRetta]

Hi Amada8,

 

Head ache is a sign of brain growth. smiley-wink

 

I'll be happy to assist. I won't have time to review them until tomorrow. If someone hasn't helped before that, I'll certainly walk you through it.

 

LaRetta

[LaRetta]

Hi amada8,

 

Houston, we have a problem ...

 

1) I am missing necessary files - Keywords particularly.

2) Whatever the purpose of the 15 COM fields, they shouldn't be fields but rather records.

3) The 15 OPT fields and 15 qTime fields also indicate an improper structure.

4) You have a field 'cust' (text) with auto-enter creation date validated by a value list?

5) You have field 'name' (text) as lookup to UNKNOWN and validated on an UNKNOWN value list.

6) I see no uniqueID in either file ... and there is a lot more ...

 

I can't even make recommendations because I can not identify the purpose here. You have Production related to itself and HW related to itself. Hardware is similar to a Product file/table and Production is similar to an Invoice file/table. If you wish to add multiple hardware pieces to one production run, then you need a third file similar to a LineItems file/table in which you accumulate how many of each part is required.

 

The best I can do is suggest one of two things: 1) Find a basic Customers, Invoices, Products, LineItems example and learn it or hire someone. Maybe others are willing to take it on. smiley-laughing

 

LaRetta

[amada8]

Roger that, Friendship 7.

 

#1 Well wasn't sure I set up Keywords right in HW. Never occurred to me that I would need separate files for the major categories of hardware. When I am in the HW DB, I can filter a screen of each type using the keyword. Guess I'll try and set up separate files and go from there. I am attaching a DB I did create called Keywords.

 

#2,#3,#4,#5 Are part of the original DB and really shouldn't be a factor in the area of the form I am working on. (4) I can't figure out why the cust lookup has anything to do with creation date either..... but the popup still works to give them a list of customers. (5) I think that is a left over from one of my attempts..... I thought I erased them all before sending the DB. As you can see, "name" field is not on any of the forms.

 

#6 shows my ignorance. The Product ID field in the HW DB is a unique identification for each piece of hardware.

 

As for my problem with relationships (I won't get into ALL of my relationships lol), when I started HW and Keywords, I used forms already in FMP that came with generic relationships already entered. I never altered,,,, but I guess I should have deleted them all. The one relationship that is listed in Production was put in by the original programmer. I'm pretty sure it has to do with a DB that follows overrun and stocked items. Once again, not related to my specific problem area.

 

Anyway, thanks for the look-around. I am grateful that you took time out of your busy schedule to try and help me.

 

Dave

[amada8]

Just a note on #2 and #3 as I think my reply might seem a bit standoffish (geez, what a word)

 

Some of the products we manufacture take up to 15 operations start to finish through our shop. (Actually, some take quite a bit more, but we have a workaround for that.) We have a sheet with 15 ops (operations) listed. It is TIGHTLY spaced and makes data entry into minute spaces a chore. A five ops sheet like the one I sent to you has the least amount of operations. We have several more forms in between.

 

If you look at the top of Production DB in either desc or part#, the best form is usually listed in parenthesis.

 

The shop has been using these forms for years. I just wanted to tweak one area to help define material costs.