I have two tables. One contains color info (red, blue, orange, yellow, etc.). The other contains artwork info. What I want to do is put a table on the artwork layout that would allow me to assign certain colors to the different art files. The trick though is that I don't want it to create a new "red" record every time I assign a design red. More of like a lookup value type of table. Is this possible?

I'm not sure I'm following you correctly, but as I understand it, in the Artwork table, you have records, each of which includes a container field with a path to an art file.  If you want to assign a color  to that record, you only need to add an AssignedColor field which could be set up with a drop down list of the colors in the Color table.

 

If that isn't it, please tell us a little more about the structure of the Artwork table.

I think you pretty much have the gist of it, except I would like to be able to assign multiple colors for each image. So lets say "Artwork 1" uses "Orange" "Green" and "Blue". I would like the table in the artwork layout to display those three colors. I would ideally like to have a field where I could start typing the color and it would auto finish what I was typing based on the values in the color tables.

Why not use a checkbox field employing a value list of all the values in the colors table?  If you display another instance of the same field as an edit box, you will see a return-delimited list of the checked items.  If desired, you can create a ColorDisplay field that will auto enter a calculation Substitute(Color; "¶"; ", ") to separate the values with a comma or any other preferred delimiter.

Well there are a few hundred colors, so I'm not sure a checkbox field makes sense. I'm thinking I will just have to create new color records every time I assign one to a piece of art.

 I'm thinking I will just have to create new color records every time I assign one to a piece of art.

 

Well, not totally.  What you will need is a join table .  Each record in the join table contains one color for one art. A portal to this table in an Artwork layout can display the colors assigned to that art.  You can set the field in the portal to autocomplete using a value list.

 

Something like this:

 

artwork colors.fmp12.zip

 

If new colors need to be added to the Colors table on the fly, you will have to create a script to add a new color that you type into the join table to the Colors table. Use an OnRecordCommit script trigger to start the script.

That seems to be the solution I am looking for, but not sure I understand how the "artwork colors" table works... Why are all of the ColorID fields empty?

Errrrr...I ended up not using that field.  The relationship between artwork colors and colors is not needed. Also not needed is the colors TO.

 

I used the colors for VL TO to create the Colors VL value list.

 

The relationship between artwork and ArtworkColors is set up to allow creation of records in ArtworkColors.

 

The artwork colors::color name field in the portal is set up as a drop down list using values from the Colors VL.  In the Inspector, Auto-complete using value list is checked.  So when you click on the empty last row of the portal, a new record is created, with the ArtworkID automatically populated with the artwork id.  The artwork colors::color name field shows the dropdown with the value list values.  If you start to type, the value list is filtered to show only the values starting with the characters you typed.

 

Sorry for the confusing extraneous stuff.  What can I tell you; it was late.

Hi Coy,

 

Please don't use cryptic subjects such as Portal.This is a learning Forum, and a good subject can help others in the future locate your tread if they have a similar problem.

 

A subject of "Portal"  really doesn't describe what you are trying to accomplish. The subject should give a brief description of your question, and provide help to those searching for keywords in order to solve a similar problem.

 

TIA,

 

Lee