How to implement a keywords/tags system

[P.G.]

Hi all,

I'm trying to implement a keywords/tags system, but I'm having a bit of trouble and found very little resources on the subject.

I have an Items table, and I want to be able to assign keywords to the Items, with the prospect of searching on keywords afterwards.

My first thought was to create a separate Keywords table, with a join-table to the Items table (as to avoid redundancy).

So my fields would be as follows:

Items_table:

- kp_Item_ID

- Other fields

Keywords_table:

- kp_Keyword_ID

- Keyword

ItemsKeywords_table:

- kp_ItemsKeyword_ID

- kf_ItemID

- kf_Keywords_ID

Does this seems like a practical way to set up the relationships? Or would I be better off with, say a simple text field with comma-separated keywords in the Items table?

I've seen people here talk about return-delimited text fields for keywords but I haven't really understood the use of it.

Also, how can this be implemented on the layout side of things? One of the problems I am having is when I display a portal on the Items layout to the Keywords table, I cannot seem to create new entries via the portal. (plus I would still need a way to handle adding keywords that already exist).

Thanks in advance for your help/suggestions,

Peter

[efen]

Should these questions be answered in conjunction with the other post?

http://fmforums.com/forum/showtopic.php?tid/213076/fromactivity/noreply/

[P.G.]

Not necessarily. I just thought I would be easier to break it down because the question about the keywords was kinda off-topic in my other post. But yes it is for the same solution that I want to use it.

Peter

[efen]

One of the problems I am having is when I display a portal on the Items layout to the Keywords table, I cannot seem to create new entries via the portal. (plus I would still need a way to handle adding keywords that already exist).

Is the attached of any help?

assets.zip

[P.G.]

It most certainly is, thank you very much, it's just what I needed to get me started.

I used the same kind of relationship, but I didn't think about using the cross-product to be able to access the whole list of keywords.

I am wondering however, do you need this cross-product relationship in order to perform the add script?

I don't need to show the whole list of keywords on the layout, only the related keywords, so couldn't the add script be performed directly without another TO?

Thanks,

Peter

[efen]

If you don't have all [or a specific subset of] the unrelated keywords in a portal to choose from for the particular asset then how do you select an unrelated keyword from a portal of related keywords?

I understood from your post that you wanted to select keywords from the one layout. You could eliminate the portal from the layout by using a new window showing keywords from which to select, but again these would be unrelated.

[P.G.]

I have been tampering around with your example before I read your post and I managed to pull it off by scripting a find on the add button (see attached file).

Now that I read your post however I'm guessing this is probably not the most effective way to do this... is a find script too "heavy"?

This is exactly why I came to this forum, I manage to somehow pull stuff off in Filemaker, but I want to be sure i do it properly :

Peter

[bruceR]

1. There is no point in using a replace operation on a global field. You probably need to use: set field [ Items::g_addkeyword; lower( [ Items::g_addkeyword)]

2. Perform Find Restore will not work. You need to find on the individual keyword.

Enter find mode

Set Field [ someField; $keyword ]

Perform Find [ no restore]

3. If get( FoundCount) > 1

4. Toward the end of script you are ALWAYS making a new record, it is not clear if that is what you are trying to achieve.

5. Seems like a lot of trouble when FileMaker can perform finds so easily, why do you need to do this?

[Søren Dyhr]

1. There is no point in using a replace operation on a global field. You probably need to use: set field [ Items::g_addkeyword; lower( [ Items::g_addkeyword)]

I can think of one... though! Agnès Barouh that if you load a $variable via the Replace function, a global dummy field as reciever is required, could give some algoritmic speed.

--sd

[P.G.]

1. There is no point in using a replace operation on a global field. You probably need to use: set field [ Items::g_addkeyword; lower( [ Items::g_addkeyword)]

Thanks for pointing that out.

2. Perform Find Restore will not work. You need to find on the individual keyword.

Enter find mode

Set Field [ someField; $keyword ]

Perform Find [ no restore]

I see, probably because of the variable, right?

But if I set the field like you say, will the search be an exact match?

3. If get( FoundCount) > 1

Noted.

4. Toward the end of script you are ALWAYS making a new record, it is not clear if that is what you are trying to achieve.

Yes it is, that script step creates a new record in the join table.

5. Seems like a lot of trouble when FileMaker can perform finds so easily, why do you need to do this?

I don't necessarily need to do it this way, but that's the first idea that came to mind : What would you suggest? Using a cross-product relationship like Efen did in his example?

His example however does not account for duplicates in the Keywords table. How can you check if the keyword already exists without performing a find?

Thanks for your replies,

Peter

[efen]

Efen ['s] example however does not account for duplicates in the Keywords table. How can you check if the keyword already exists without performing a find?

In my sample file create another table occurrence [keywords2] of the keyword table with the relationship between the keyword in one occurrence and the keyword in the other occurrence.

Add another field - showduplicate - to the keywords table: calculation field,result number:

Case( _pk = keywords2::_pk ; "1"; "0")

Create a one-line portal from this relationship on the keywords layout with only the showduplicate field in it. Sort the portal by the showduplicate field.

Then create a subscript "check whether duplicate" and place it before the last go to original layout step in the "new keyword" script

pseudo code

Commit Records/Requests

Go to first portal row

Go to field keywords2::showduplicate

If keywords2::showduplicate = "0"

Show Custom Dialog [ "This keyword is already in the file"; "Do you want to delete it?" Yes No

If [ Get ( LastMessageChoice ) = 2 ]

Exit script

Else

Delete Record/Request [No dialog]

End If

Exit Script [ ]

End If

Edited January 29, 2010 by Guest
corrected field name

[P.G.]

Thanks a lot for your help.

This thread alone has helped me understand much clearer how everything works together in Filemaker.

I see relationships and TO's in a totally different light now.

Foreign keys form a relationship on their own, the relationship you draw on the graph is just to facilitate data access or entry. They are really more a way data is displayed than a way data is actually related. It all makes sense now! :

I'm gonna go back to the lab to try and implement it!

I still have a long way to go until I finish my whole database (which is an ambitious project), so I'll probably be back in no time with more questions :

But this has been incredibly helpful already; I feel I understand it much better and I'll probably be able to figure a lot out on my own. Thanks.

[P.G.]

Just to let you know, I've just managed to achieve the same functionality, with a very simple script, without adding a calculation field, without adding a portal, and without performing any find steps.

The only thing I did is create a global field g_duplicatecheck in the Keywords table. I then created a Keywords2 TO and a relationship between the global field and the Keyword_name field.

When the user is on the Items layout, he inputs his keyword in a global field. The "add" script copies this keyword, goes to the Keywords layout, pastes it into the g_duplicatecheck field.

If the keyword is already present in the Keywords table, this will "trigger" its relationship with the second TO and there will be a related record in Keywords2.

You can check for the existence of this related record just by checking if its ID exists -> isEmpty( Keywords2::ID ). No need to even place it on the layout!

I'm pretty sure this is the most effective way to achieve this functionality. I've tested it and it's blazing fast as well, way faster than the find method.

So I'm very proud of myself :, but I couldn't have done it without your help!

[bruceR]

I can think of one... though! Agnès Barouh that if you load a $variable via the Replace function, a global dummy field as reciever is required, could give some algoritmic speed.

--sd

I have done that also and I have mentioned that technique, for instance as a way to build ID lists. Don't know which of us thought of it first.

[bruceR]

Just to let you know...

When the user is on the Items layout, he inputs his keyword in a global field. The "add" script copies this keyword, goes to the Keywords layout, pastes it into the g_duplicatecheck field.

Argh! Strongly suggested that you NEVER use copy/paste in scripts. It messes with the clipboard and just isn't needed. We have variables, use them.

[P.G.]

Argh! Strongly suggested that you NEVER use copy/paste in scripts. It messes with the clipboard and just isn't needed. We have variables, use them.

Sorry if I was being unclear, I didn't actually use the copy/paste script step, I just need to get used to the terminology :