I was looking online for a solution to the following problem, didn't find an answer, created this myself, and thought others would be interested. I built it in FM7, but it could be done, with more work, in FM6 also. The FM7 feature used is recursive custom functions.

Here's the scenerio: You have two entities that have a many-to-many relationship, such as Users and Groups. A user can belong to many groups and a group can have many users. To enable your users to edit these relationships, you are providing a selection portal that shows the groups a user belongs to and the groups available. How to make the groups list show only groups that have not already been added to a user, preventing a group from being added twice?

Here's the solution I came up with. For this discussion, the Users table has a UserID field for its primary key, the Groups a GroupID and the UserGroups join table has two fields, UserID and GroupID. A relationship exists between Users and UserGroups based on the UserID field in both tables (called UserGroupsForUsers).

First, create a value list called AllGroupIDs that takes its values from the GroupID field in the Groups table.

Next, create a value list called UsersAssignedGroupIDs. The value list will show all Group IDs that have already been assigned to a user. This is defined to use values from UserGroupsForUsers::GroupID, including only related values starting from the Users table.

Next you need a custom function. I called this SubtractList, and it accepts two parameters: FullList and SubList. The function will return all of the values in FullList that are not in SubList (subtracting SubList from FullList). It's basically the opposite of FilterValues, which when given two lists, will return all of the values in one list that are in the other list.

Let(

Very nice, no criticism. Perhaps some comments would help, though; see the FieldSubstitute function by Mike Hackett for an example.

I included comments in the custom function but didn't include them in what I posted. Perhaps these will help in understanding the function. I figured the field calculation was self explanatory.

// Will subtract the items that exist in SubList from the FullList. If FullList is "1

At the top of the code you might want to add:

//SubtractList(FullList,SubList)

//By Chuck [and last name]

//10-14-2004

At the bottom:

)//Case

)//Let

This way your custom function will conform to the same standards as the FieldSubstitute custom function.

I wasn't trying to start a standard, but for me it's helpful to know where a function came from. And showing the function name and the parameters is helpful when you paste a function into a solution; it's easy to retype the parameters from the comment.

Hi Chuck,

Although your function works for lists where every line ends in a paragraph return, it fails when the last item of the FullList is missing the return. This would be a good thing to fix, especially for trying to reference related value lists. Unfortunately I don't have a suggestion for correcting this...

There is a custom function called DifferenceValues on briandunning.com that does this correctly through a different method.

I really like how you are using this to get the values not used. My attempt at this was using a <> relationship, which did not work when there were no items used. Unforunately, I am still trying to get your method to work for dynamic value lists, but I haven't given up yet!

OK, here's the corrected custom function to account for the possiblity that there's no ending paragraph mark:

// Will subtract the items that exist in SubList from the FullList. If FullList is "1

Just a small tweak. Change

FullList = FullList & Case( Right( FullList; 1 ) = "

Thanks for the info Chuck. I'll try it out.

Queue,

Just a small tweak

You're right, that's just a small idiosyncrocy of mine, I always include both the true and false result in my Case functions, even if they aren't really needed. I think I find that being explicit is easier to understand when reading the calc later.

Chuck

Could this be done only with relations?

Look at the attatchment.

For UserID = 1 there is 1 AssignedGroup and the UnassignedGroups show right.

For UserID = 2 there are 2 AssignedGroups and the UnassignedGroups show all groups!?!

Can anybody correct the relation?

UserGroups.zip

Hmm. Yes, it can. Add an unstored text calculation in User of

Let( V = ValueListItems( Get(FileName); "AssignedGroupIDs" );

Case( not IsEmpty(V); V; " " ) ). Then base your UnassignedGroups relationship on this calculation not equalling GroupID in Groups.

The only thing required is a Refresh Window [Flush cached join results] script step once a user's groups have been modified.

See attached.

UserGroups.zip

I came up with a little different solution than Queue's.

Duh, that simplifies it. And this one removes the extra field. You only need " " & ValueListItems( Get(FileName); "AssignedGroupIDs" ) as the key field (or 0, if you prefer).

You still need the refresh step though.

UserGroups.zip

Because of the refresh requirement, it seems this will work best as a separate entry field, (instead of within the Assigned portal. I'm thinking in terms of using the unassigned list as a value list for adding a new portal line.)

Attached is a version that shows this with a dynamic value list to choose an ID to assign. Seems to work.

I think this method will make it into my current project.

UserGroups.fp7.zip

If you want to make sure the field isn't active which could break even the refresh window indeed, you may add a CommitRecord/Request, but it works fine within the portal itself.

Now may be v4 will give us some new refresh/update solution.

Right now, for sure this beats any calc I'd think.

There is a custom function called DifferenceValues on briandunning.com that does this correctly through a different method.

...And yeat another one availiable here:

http://www.filemakerpros.com/FILTER.sit

http://www.filemakerpros.com/FILTER.zip

Which can be downloaded and utilized as foundation, for people not having 7Dev availiable to make recursive functions.

--sd

My attempt at this was using a <> relationship, which did not work when there were no items used. Unforunately, I am still trying to get your method to work for dynamic value lists, but I haven't given up yet!

I do also think its the right way to solve this issue, and actually is there a snag to it ...it works as soon as something is entered in the keyfield. So only real requirement is to make the field allway containing a pilcrow (CR/LF) ...and then followed by the dynamic valuelist.

While the custom calc' way is sort of crossing the river to fetch water ...well we all do these days due to pullution - but thats how a local saying goes at least.

--sd