Fine tunning this solution

[CP-1000]

Hi

Attached is a demo containing three tables:

KEYWORDS

VALUES

KEYWORS_VALUES

where if you enter a number (in the KEWYWORDS table) into the fields "Keyword_1" or "Keyword_2", a new entry into a portal is created, when you click outside the fields. To delete an entry in the portal you delete the content of "Keyword_1" or "Keyword_2" and click outside. If you add to a fields content, a new portal is created, and you are no longer able to delete the previously entered portal entry.

Any other suggestions of how to do this? At the moment I'm just making demo files in order to learn some stuff in Filemaker.

Thanks

KEYWORDS.zip

[Ron Cates]

When you edit your script fires and says If Not IsEmpty (Keyword2) then create a new record. When editting keywords2, it is not empty. If you want to edit the existing record you'll need to test if it exists already and overwrite the existing.

I think you may not be on the right track here. You should not need a script for each individual field.

I'm not quite sure what you are trying to do here, why the join table setup. What is it that you are trying to accomplish with this?

[CP-1000]

Thanks for your comments, journeyman.

I've changed the scripts to one script that also test if the row is already created.

Regarding the use of the file, well it is a tiny bit of a film database. Imagine that you have a table with a film title and a unique ID. When a film is related with another film (might be a remake, sequel, based on same novel or whatever) I enter the ID of one of the films in one of the keyword fields for both or all the films. This creates a row in the portal with a text (at the moment just the 001, 002, 003....) that has a button attached which will open a new window with the related film or make a search for all related films or whatever I figure out.

Thanks

KEYWORDS4.zip

[comment]

I don't understand your file either. As a rule, having numbered fields such as Keyword_1, Keyword_2, etc. is a sign of poor data structure.

When a film is related with another film (might be a remake, sequel, based on same novel or whatever) I enter the ID of one of the films in one of the keyword fields for both or all the films.

IMHO, you should enter the ID of the "other" film directly into the portal - which would be a portal to a Links table, joining between Films and itself.

Alternatively, tag both films with the same keyword - again by entering it directly into a portal.

[CP-1000]

It really hurts me when you tell me that you don't understand my file... either I though that I was going in the right direction.

This is what I do at the moment:

In this example I have 3 films (US versions) that are related.

In image (01) I copy the ID (bottom left) to one of 5 fields (bottom right) for the 3 films. A button is activated (blue square) when the field contains text. I can then click on the button and a new window opens (02) which is the search result of all the related films. These 3 films are also related with 2 other films (2 Japanese versions), so I enter a new ID into the second field (for all 5 films), a new blue button appears, and when I click on it I get a new window (03). So in the future when I want to se related films of the US version I click on the first button, and on the second when I want to add the Japanese versions. (The Japanese versions are also related with themselves through their first field)

What I'm trying to accomplish is to get rid of the buttons, and when I enter an ID into one of the 5 fields have a text appear at the top right (01). At the moment I'm writting this text every time a relation appears, and why should I do that if I can get Filemaker to do it? The text would then have a button attached to it, and replicate the previously mentioned function.

Although I'm going in the wrong direction, I hope this explains it a bit.

[comment]

I think we need to separate the "what" from the "how". The "what" in this case is the data structure: what tables are needed and how they should be related. Once we have that, we can talk about "how", i.e. the user interface.

So the first question, I think, is what would make two or more films "related": is it some value they have in common (for example, a keyword), or - almost the same thing - are they all related to the same "family" (where families would be records in a separate table), or (something entirely different) they are linked one to each other, one pair at a time.

[CP-1000]

Based on the "what" question:

A film could be related to multiple other films, through a keyword (remake, remade) or/and a family (part of a series of films) (as you also wrote).

Without this discussion I think that I would still make FILM, KEYWORD and FILM_KEYWORD tables.

... or would it be better to do a self-join relationship in the FILM table? (no need for a KEYWORD and FILM_KEYWORD table)

[comment]

Without this discussion I think that I would still make FILM, KEYWORD and FILM_KEYWORD tables.

That's correct. Now, regarding "how" I suggest you have a look at the following file:

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

Then tell us what additional features you want your user interface to have.

[CP-1000]

I've been looking around your version and I can see why you did not understand my file, since the things I wanted to do could be done more easely.

I renamed the items in the file to better understand them and added an additional feature with an occurrence of the FILM table. I've attached the file for any future reference.

Thank you for your help.

JoinDemo copy.zip

[comment]

added an additional feature with an occurrence of the FILM table. I've attached the file for any future reference.

But what you did is the third option: linking one film to another, one pair at a time. The problem with this approach is that you must enter the same information twice, for example:

FK000005•The Grudge•Followed by•The Grudge 2

FK000006•The Grudge 2•Sequel to•The Grudge

That's hardly a good method.

See also:

http://fmforums.com/forum/topic/38282-join-table-inverse-relationships/

[CP-1000]

Hmm, you're right... again.

I'll take a look at the link.

Thanks