Checkbox field and multiple values

[sarahjtf]

hi there,

i'm new to filemaker and setting up a database to organize the 2000+ photos being used in a documentary film. i have a field called "category", in which each photo is assigned to one or more themes (e.g. france, refugees, united states, etc). so any given record might have anywhere from 1 to several themes listed. (i created the database by importing an excel spreadsheet, in which each category entry is separated by a comma).

my question is this: i've created a value list for the category field and converted it to a checkbox field. what i now see is a checkbox field that shows me every possible category in my database. that's great, because if the editor wants to search by field, he can simply check off the categories he's interested in. however, in every case, only one category is checked, even where my original spreadsheet listed more than one category. i suspect i need to perform some sort of calculation field, but am not sure what the calculation should be. can anyone help me? i tried separating the categories by a paragraph break instead of a comma, but that didn't seem to work.

thanks in advance!

[Colin Keefe]

You'll need to run a replace (with calculated value) on your category field:

Substitute(Category;", ";"ParagraphMark")

Where ParagraphMark is the backwards P symbol in the calc dialog.

Basically, for the checkbox list to recognize multiple values, they need to be return-delimited inside the field.

Make a backup of your file before running the above, since replaces are not reversible operations, and it's quite easy to get it wrong the first time. Also make sure you Show All Records first, since Replace only operates on a found set.

[sarahjtf]

Thank you so much! I had tried that but accidentally had an extra space in the calculation, so it wasn't working. Thanks to your post I gave it another look and realized my mistake. Now it works.

I have one more question, but this may not be the right place to ask. My editor would like to be able to do a "global search" - type in a word and have it search all fields. If I type a word in every field, it will only bring up records for which that word shows up in all fields. Is there a way to search globally?

[Søren Dyhr]

I have one more question, but this may not be the right place to ask. My editor would like to be able to do a "global search" - type in a word and have it search all fields. If I type a word in every field, it will only bring up records for which that word shows up in all fields. Is there a way to search globally?

Make a series of request one for each field, where you something daft like "çççç", make the seach inside a pair of errorcapture's in the script where the initial search is made (ought to be empty). Then it proceedes in the script:

http://www.filemaker.com/help/Script-Steps74.html

http://www.filemaker.com/help/Script-Steps36.html

...where each occurance of "çççç" is replaced by the value in the search field. The final "Perform Find" is then giving the found set. Since you're on fm7 are you bound to loop your scrips cursor diagonally thru the requests since the last of the commands isn't within your reach.

Please await a template....

--sd

[Søren Dyhr]

Alright here it is, phew I almost forgotten how tiresome it is only to have the script parameters for temporary storage, and global fields as such should be avoided. What a relief $variables are!!!

Alright to the template, enter find mode and put a value in one of the fields, and fire the script attached to the button. I could have paid extra attention and made the call of the external subscript recursive instead. But the bare thought of reading it and let it make sense are worrying, a recursive script are always tough to read, and since the first already recurs ....

--sd

makeGlobalsearch.zip

[Wickerman]

Hi Sarah --

I noticed your post because I'm a moving image archivist and deal with similar thematic / genre / subject keyword structures in my databases. One thing I would strongly encourage you to consider with regard to your solution is using a separate table to list the "category" terms and a join table to connect it to the main photo table. (Each photo can belong to many categories, each category can hold many photos). So each occurrence of a category appears as a record in the Join table. It took me awhile to get around to this elegant but simple approach, after dealing with all sorts of frustration with the checkbox approach (an obvious one: when you want to add terms, you have to change the layout). Using the Join Table also enables all sorts of nice manipulation of the data, such as easy indexes by category. :

Good luck!

Albert

[HunterBoss]

Sorry for hijacking the post

That is exactly what I'm having trouble doing right now.

How do you do a join table like that, please?

Thanks

Christopher

[comment]

You can find a basic demo of a join table here:

http://www.fmforums.com/forum/showpost.php?post/246136/

[KirkR]

Not believing that, if your only tool is a hammer, then everything is a nail, a relational database may not be the best approach to the problem.

Not to detract from Filemaker's superb capabilities, but for this use, Apple's iPhoto is an amazingly powerful tool for managing photos. I have over 13,000 in iPhoto, and the tags (classification), ratings (quality), album structures (client groupings), smart (rules based) albums, image/camera data storage, and event based views make photo management, as well as a pretty sophisticated degree of single photo cleanup editing (no compositing or content edits - leave that to Photoshop/GIMP/Pixelmator ) make iPhoto an excellent solution - and it is part of iLife that came free with the Mac.

The tag concept, prolific within Apple's family of products, is a different concept than the relational structure of most databases. It uses XML, with the ability to "tag" any record with n number of classifications without adding additional fields, then finding, based on AND/OR conditions of those tags, and even making smart folders that create visible photos (pointers, not copies) of the photos, where one library photo can be filed in N number of categories concurrently.

If you Goggle "multiple users iphoto" you'll find how to share the iPhoto library with other Mac users - the assumption here is that your editor has a Mac as well.