Cascading Value List...I think...

[kjjames]

I would like to apologize if I am not using the proper naming convention for what I am asking for help with.  Until about a year ago, I was a PC woman, who had used Access forever.  So, I'm having a little trouble with the same tasks or features having different names.  So, please correct me!

 

Here is my problem.

 

My database (I'm the only user and it resides on my MacBook) is for my art supplies.  I have my Primary Table, that pretty much holds all the data, related tables, and a few extra tables that I added (Vendor Table for example) for future use.

 

My Primary Table is as follows:

 

tblArtSupplies

ArtSupplyID

ProductCode

ArtSupplyName

ManufacturerID

MediumID

etc......

 

There are more fields, however I don't think they are relevant to my question.  As you can see, there is a field, MediumID.  I also have a table for that as follows:

 

tblMedium

MediumID

Medium

MediumTypeID

MediumType

 

I also have a table for Medium Type as follows:

 

tblMediumType

MediumTypeID

MediumType

 

I've related tblMedium:MediumID to tblArtSupplies:MediumID and tblMediumType:MediumTypeID to tblMedium:MediumTypeID

What I am trying to do is this:

When I enter a new, say, tube of oil paint to my inventory, I would like to go to my entry layout and:

Enter the information pertaining to the paint, for MediumID choose Painting Supply (from a list of Painting Supply, Drawing Supply, etc.), then have that filter my choices in MediumTypeID to Oil, Acrylic, etc.

 

I have tried following many of the samples that I have found, but I'm missing something.  Maybe I've been breathing in too much Turpentine!  I thank you in advance for looking at this.

 

If I can provide further info, please tell me.  Karen

[eos]

Hi Karen –

 

welcome to the world of FileMaker (and the Mac, I guess I should say; some wise choices … ). No reason to worry, we all are newbies somewhere, so ask away.

 

I think you need to tell us more about the relationship between Art Supplies, Media (Mediums?) and MediumTypes – not in database terms, table and field names, just how they relate to each other in the real world. It might help if you posted a sample of your database with a few exemplary records.

[kjjames]

Hello eos,

 

Thank you for the welcome and the response!  It was a great conversion to the Mac...once I laid hands on the iPod, then iPod Touch, iPad.....I was lost

 

Okay, real world relationship.  I have in my inventory of supplies surfaces, such as watercolor paper, bristol vellum,  charcoal paper, and canvas to name a few. 

 

I will use these surfaces as a base for medium such as paints,  artist grade colored pencils, graphite pencils.  I also do some work with glass.  So I would classify glass itself as a medium.

 

Now, sometimes I work with different types within the medium....I might use oil paints, or water color paints.  If I'm using water color paints, it'll be with water color paper. 

 

I think that gives you an idea.  Now I'm sure you're thinking this is an inventory database to keep track of what I have used.  Nope.  It's to prevent me from buying duplicates!  When my favorite on-line art supply house has a sale, I always take advantage.  However, I always seem to buy those colors that I have in stock.  Last time I purchases, 35% of my purchase was duplication.

 

I have the cloned database with a few records in it for you.  I've compressed it, but I'm having a problem uploading it.  I think I need to .zip it.  I'll get that up here ASAP for you.

 

Thanks so much for looking at this!  Karen

 

P.S.  WooHoo....file uploaded!

Art Supplies and Tools Database Clone.fmp12.zip

[kjjames]

And please forgive all the extra stuff going on in that database.  I've have literally been trying everything in it.  And if you think I should start from square one, I will.  I'm coming from an Access background.  It's a little messy.....

[eos]

And please forgive all the extra stuff going on in that database.  I've have literally been trying everything in it.  And if you think I should start from square one, I will.  I'm coming from an Access background.  It's a little messy.....

 

It looks … artistically … like someone took a FileMaker Sample database and made it worse …  Just kidding (a bit)

 

I think the first thing FileMaker should put in their manuals/documentation is: “You don't have to connect everything to everything else in the Relationship Graph!” (not that you literally could).

 

Anyway – if I understand correctly (which I'm still not sure about): You want to enter a new supply item, then select a Medium, and then, based on that selection, see a list of MediumTypes.

 

If this is so, here are some comments: For this scheme to work, you need a foreign key Medium in MediumType – not the other way around.

 

The field you put on the layout for MediumType was a field from the MediumType table itself, which means that a selection here would alter real data (but rest assured, the previously defined relationship into the table couldn't become valid, so no danger … )

 

If you want to say that a certain item is of certain type, then that's an attribute of the item, and thus needs to be stored with the item itself, aka as a field into the table (if an item could be of several types, then that's another story … anyway). If each MediumType is of one Medium (and one Medium only), then you could simply select the MediumType – the matching Medium is already defined by the Medium key in the MediumType record (which of course you need to set when you create a new MediumType). (I'm getting a bit dizzy here …)

 

If there are many MediumTypes and you would like to make pre-selection of MediumTypes based on a Medium, then use a global field as a filter device. This way you get a nice, short list of MediumTypes; if you make a selection, that key will be stored with the Item, and with that you know the associated Material, without having to store a key for it in the Item, too.

 

I hope I got all that right – I'm still not sure about the kind of relationship that Medium and MediumType have …

 

Anyways, here's a sample to show you what I mean, using a global as a selection tool; you see how selecting a MediumType ID create an indirect connection to Medium, and you can display the value, without having to store the Medium key itself. (But read the notes on the layout, pertaining to the pitfalls of conditional selection value lists.)

 

As for going back to square one: you haven't put in much functionality, so it may not be a bad idea. Send me a PM if you want some design and interface tips, and/or search this forum for best practices.

Art Supplies and Tools Database_eos.fmp12.zip

[kjjames]

Hahaha!  I told you it was messy!  I would go in and try something to see if it would work.  If it did, great!  If not, oops!  I am still such a newbie!  It looks like I'm coloring with crayons!

 

I cannot Thank You enough!  This is exactly what I wanted to do.  Those random fields you saw at the bottom of the Layout that you commented on were tests.  I should have removed them from the Layout before I uploaded the database.  The 2 that you have working on the primary part of the form, that is what I was hung up on.  You understood my goal perfectly.  And your explanation was very clear. 

 

You saw my Access background with all the connections in Relationships.  In Access, nothing works unless it is connected.  So, I figured that was my problem.  I really need to stop thinking Access.

 

And thank you for that offer on design and interface tips.  I may take you up on that.

[eos]

I really need to stop thinking Access.

 

When I use other environments, I think FileMaker. Some habits are hard to kick.

 

And thank you for that offer on design and interface tips.  I may take you up on that.

 

Feel free to do so. And the more I think about it, the more I would recommend starting with a clean slate (and get yourself a good book on FM 12). I'm sure you know all about staring at a blank canvas …