Uniqness on two fields??

[Jokez]

I have a many to many rellationship and I have devided it so I have a rellationship table it looks like this:

Image (with id)

Image_Searchword(ImageID and SearchwordID)

Searchword (with id)

Now I can add the same searchword on an Image more than once, and I dont want that to happen, how do I make it unique on the combination of ImageID and SearchwordID in the table Image_Searchword

Thanx for the help!

// Jocke

[Kent Searight]

One way to handle this is to make your searchword value list appear in a checkbox field that displays in a new, small window whenever the user wants to edit the searchwords associated with the image(script triggered by button). This, with a "Close Window" button on the layout, and a calc field on your original layout the contatenates the keywords, is very much "a la MS Outlook", making it a familiar interface to many...always a plus!

FYI The formula to concatenate the keywords is as follows:

[color:blue]Case ( ValueCount ( keywords_cb ) = 1 ;

/*If only 1 checkbox is checked*/

Substitute ( LeftValues ( keywords_cb ; ValueCount ( keywords_cb ) ) ; "¶" ; "" );

/*If any number other than 1 checkbox is checked, put commas in between keywords, but omit trailing comma*/

Substitute ( LeftValues ( keywords_cb ; ValueCount ( keywords_cb )-1 ) ; "¶" ; ", " ) & Substitute ( RightValues ( keywords_cb; 1 ) ; "¶" ; "" ))

[comment]

See this thread.

[Kent Searight]

Hi comment,

As it turns out, I'm currently developing a digital asset (mostly photos) database and I started off by using the same approach as Jokez, i.e. separate table for keywords (searchwords) with many-to-many relationship to assets. After pondering for a while I decided to omit that relationship to the keywords table, and just use that table's records for a value list of keywords, which in turn are selected in a checkbox field in the Assets table. This seems to work very well and solves the problem of having the same keyword assigned multiple times to an asset record. Do you foresee any issues that might arise later that would cause problems with keyword searches, etc.?

[comment]

I can't think of any specific issue at the moment, but then I don't know how far you intend to take your solution. For those uses that I can imagine I think it should be fine. Probably even preferable - keywords are not really an entity in this situation.

[Kent Searight]

keywords are not really an entity in this situation.

Thanks, comment, that was my logic too. Unfortunately, my logic has painted me into corners before, so it's nice to get an opinion from someone with more FMP knowledge than me. :)

[Jokez]

I used a field that took the two other fields and put them togheter ImageID and SearchwordID in a ImageSearchwordID field. Then I checked that field for uniqness... that worked like a charm

// Jocke