Allow only unique values for user in portal-associated interest table

[Greenman]

Tables:

user:

uid, name

1, joe

2, bob

3, millie

interests:

item_id, interest_id, uid, where uid is associated with user::uid

1, 1, 2

2, 1, 3

3, 1, 1

4, 2, 2

5, 2, 3

6, 3, 1

7, 3, 1

interest_list:

interest_id, interest_name, where interest_id is associated with interests::interest_id

1, ecology

2, economy

3, elephants

I have a portal that uses the interest_list table as a value list using a standard pulldown menu to add new interests to each user and a delete button to remove them as necessary. The portal works as predicted, but I can add any interest more than once. I'd like to make it so there are no duplicate interests listed for each user. So 'joe' can only be associated with 'elephants' once.

I'm guessing this should either be done using a script trigger to OnObjectValidate or there should be tweak in my value list that only allows unassociated items for that user to show up in the pulldown.

What's the easiest way to accomplish this in FMPro?

Here's my example file:

example_interests_list.zip

[comment]

See:

http://fmforums.com/forum/topic/68394-relationship-help-doctorspatients/page__p__324487#entry324487

[Greenman]

Hi comment,

Thanks once again for your advice. I'm not sure I understand how this applies. When I look at my interest list, the values only 'dwindle' per user and can be added back again later. Every user has an equal opportunity to acquire any listed interest. When I navigate to the bowels of the value list, I see these two options:

"Include all values" which gives me all the items listed in the interests list,

and

"Include only related values starting from: <interest | user>"

If I check 'starting from user' I get a value list that pulls down for joe as 'ecology' and 'elephants'.

For bob, it's 'ecology' and 'economy'. Likewise, for millie, it's 'ecology' and 'economy'.

I'm looking for every thing but those, so maybe I'm looking for what would be equivalent to

"Exclude only related values starting from: user"

In the PortalToPortal2 dwindle example, once the items are 'removed', they cannot reappear again in the parts list. Also, it looks like this solution may require a second portal in order to populate the first portal. If I can avoid that kind of screen-real-estate take-over, I'd rather use a pulldown menu populated by the interest value list. I am probably missing something, so if I am, I'd be grateful for any clarification.

[comment]

Perhaps you should start with this one:

http://fmforums.com/forum/topic/51074-tough-one-excluding-value-list/page__p__266667#entry266667

Then move on to the PortalToPortal.fp7 demo linked to above - NOT the PortalToPortal2.fp7 file posted later in the same thread.

[Greenman]

Perhaps you should start with this one:

http://fmforums.com/...667#entry266667

Then move on to the PortalToPortal.fp7 demo linked to above - NOT the PortalToPortal2.fp7 file posted later in the same thread.

comment,

Again, thank you. I have been able to understand better the above example and have glanced cursorily at Portal2Portal.fp7. I have been able to partially get the pulldown list to work for my user interest list but have a couple of weird things going on:

1. If there are no existing interests listed for a user, the pulldown list does not show at all.

2. If there are as few as one existing interest(s) in the portal listing, the drop-down list shows and populates with the correct interests, but after accepting an answer or leaving the field, FM addes the portal record, but gives an error saying the script has been deleted:

"This script cannot be found or has been deleted."

I added one script similar to the one in your example. There are no other scripts, so I don't know why it's saying that.

Lastly, I'm curious why the last line of the calculation field must be "-.1".

[comment]

I have downloaded the demo in question and I am experiencing none of the problems you mention.

Lastly, I'm curious why the last line of the calculation field must be "-.1".

This question is answered in the same post where the demo is.