Dynamic value lists

[HarrisonM]

Articles to tags in a database has a many to many relationship. I am using a portal to add tags to an article. How do I create a dropdown list that dynamically removes tag entries already added by the user. Say if the taglist contains "Applescript, bash, ruby, Filemaker" and if the user selects "Filemaker" for Article1 and when trying to add a new tag to Article1 the value list shows only "Applescript, bash, ruby" and so on..

Logic was that if I could connect a return separated list of userSelectedTags from the parent table to a return separated list of all TagNames in the child table using a not equal to relationship, I could use the relationship to show all the TagNames user did not select.

When Articles are connected to Tags in a one to many relationship, the dynamic value list works.

1. Table ARTICLES has a textfield called "userSelectedTags" which calculates a return separated list of user selected tags (Calculation from the context of Articles = List(TAGS::TagText))

2. Table TAGS has a textfield called "AllTags" which calculates a return separated list of all tags from the table TAGS (Calculation = List(TAGS::TagText))

3. ARTICLES and TAGS are connected using the above fields using a relationship where ARTICLES.userSelectedTags IS.NOT.EQUAL.TO TAGS.allTags

4. Created a value list using the relationship in Step 3

However when ARTICLES and TAGS are connected by a many to many relationship using a join table, The value list is somehow not working. Any ideas where I am wrong?

This is what I did:

1. In the many to many relationship, to come up with a return separated list of all tagsNames from the table TAGS, I used a self join to the from TAGS to TAGS2 table between an empty match field and TagNames using a cartesian product relationship. Then I was able to calculate the "AllTags" field using the calculation List(TAGS2::TagName) from the context of TAGS.

2. Then ARTICLES and TAGS were connected between "userSelectedTags" and "allTags" using a relationship where ARTICLES.userSelectedTags IS.NOT.EQUAL.TO TAGS.allTags

3. A value list called "Tags" was made using the setup shown in the picture.

[comment]

See if this helps:

http://fmforums.com/...hp?post/274656/

See also:

http://fmforums.com/forum/showpost.php?post/233897/

[eos]

I suspect it has to do with the arrangement of the TOs. Your cartesian query for allTags is piped initially through ArticleTags, but it will not get there, let alone to the cartesian Tags 2 TO, if you don't have already at least one matching ArticleTag for the current Article record. So the exclusion field will not have a list of all Tag names, but be empty, which doesn't yield all non-empty (all), but no records at all. (I seem to remember that this worked differently before FileMaker 11.) Try connecting Tags 2 directly to Articles (and give the poor thing a decent name …).

btw, why don't you collect TagsIDs instead of the Tag names? What happens to your system when you rename a tag that's already in use?

[HarrisonM]

cool ! for the links.

Thanks to eos for explaining that cartesian query.

But AllTags actually gets a return separated list of all tag names using the cartesian query. Don't know how that works for me.

Anyway the link "comment" posted clarifies how to excluding value lists. Thanks again.