4 Replies Latest reply on Jan 21, 2014 11:00 PM by philmodjunk

    Excluding Group Item Records from an Exclude Groups table

    leszekk

      Title

      Excluding Group Item Records from an Exclude Groups table

      Post

           Hi Everyone

           I've trying to display Item records for a particular Client where those Items do not belong to certain Groups using a join table. These are 4 tables:

           Items  (PK_ItemID, FK_GroupID, ItemName)

           Group (PK_GroupID, GroupName)

           Client (PK_ClientID, ClientName)

           Client_Group_Exclude_Join (FK_ClientID, FK_GroupID)

           Item/Group table is self explanatory.

           Clients may have zero or more groups associated to them (however this association is to tell us which Groups or Items in that group this Client is NOT to see!) So in other words using a portal we'd say out of all the groups this particular client cannot see items belonging to those groups associated to that Client Table.

           I've added all the relationships in particular (Client_Group_Exclude_Join/FK_GroupID != Group/PK_Group)

           I've added sample data however if I try to display a Portal in the Client layout which lists Items I don't seem to get the correct Items list.

           If I have no Groups associated for a particular Client then no Items appear?!

           If I have a group added to Clients then then items NOT belonging to that group appear - which is what I want!! :-)

           If I have more then one Groups added to that client then all items for all Groups reappear?!

           If I have all the group added to the client then No Item should appear - however All items are appearing?!

           Can anyone please tell me what am I doing wrong? 

           Thanks in advance.

            

      2014-01-21_23-49-25.png

        • 1. Re: Excluding Group Item Records from an Exclude Groups table
          philmodjunk

               If I have no Groups associated for a particular Client then no Items appear?!

               That is correct. If you have no related records in Group there is nothing to link to Items and the portal will be empty.

               I suggest the following relationship:

               Clients::anyField X Items|All::anyField

               Where Items|All is the name of a new Tutorial: What are Table Occurrences? of Items. Using the X operator instead of = means that any client record will link to all Items records. Set up a portal to Items|All but give it this portal filter: IsEmpty ( Filtervalues ( List ( Client_Group_Exclude_Join::_fkGroupID ) ; Items|All::_fkGroupID ) )

               This evaluates as true only if the item's GroupID is not a member of the list of IDs from your exclude table.

          • 2. Re: Excluding Group Item Records from an Exclude Groups table
            leszekk

                 Thanks for your advice. I'll try that out.

            • 3. Re: Excluding Group Item Records from an Exclude Groups table
              leszekk

                   Thanks. It does exactly what I wanted. :-)

                    

                   However, I'm opting to perhaps revert back to the traditional method where Groups are added to Clients (and those Group Items are visible for the Client - which is opposite to my original plan above).

                   I'm finding it quite tedious and difficult to enforce this 'Exclude Items from these Groups' method throughout Layouts. In particular where a user is allowed to select items (using the exclude conditions mentioned before) within a drop-down in a layout field for a particular Client. Drop-downs seem to be quite specific and seem to be unable to perform Filtered style rules.

                   I appreciate your help though.

              • 4. Re: Excluding Group Item Records from an Exclude Groups table
                philmodjunk
                     

                          Drop-downs seem to be quite specific and seem to be unable to perform Filtered style rules.

                     This is not necessarily the case. Relationships can be used to filter the value list results. This can even be a diminishing value list where each time an item is selected it drops off the list. ExecuteSQL can also be part of the filtering process.

                     And if you mean using a drop down to filter what is shown in a portal or to control what records make up a found set, that is also possible.