3 Replies Latest reply on Nov 12, 2012 12:10 PM by philmodjunk

    Table relationship - not equal to

    jimscott77

      Title

      Table relationship - not equal to

      Post

           Table 1 (Clients) is an external sql table that the application has read only permissions to.  It includes past, current, and sample clients.  I would like to set up a view that only looks at current clients.  There is no particular field in that table that designates which is which.  I have set up Table 2 (Non Clients) which is a list of the clients that I wish to exclude.  I created a T.O. of Clients (Active Clients) and linked it to Table 2 (Non Clients) with the 'does not equal' operator.expecting not to see clients listed in the Non Clients table.  It didn't work the way I had hoped.

           Is there a way to create a T.O. that eliminates the undesired records?

           Thanks,

           Jim S

        • 1. Re: Table relationship - not equal to
          philmodjunk

               And exactly what match fields did you set up and what is the context for your layout where you want to see this list of only active clients?

               I would guess that you set up a relationship like this:

               Clients::ClientID <> NonClients::ClientID

               That won't work for what you describe.

               But try two occurrences of Clients and you can exploit a return separated list of values in a single field to exclude records from clients.

               Define These Relationships:

               Clients::anyField X NonClients::anyfield

               Clients::cExclusionList <> ActiveClients::ClientID     (ActiveClients is a second occurrence of Clients.)

               Define cExclusionLIst as List ( NonClients::ClientID ) with Text specified as the return type.

               If you need to see this list in the context of a different table, use an occurrence of that table in place of the Clients table occurrence in both relationships.

               Note: with FileMaker 12 there should be ways to get this list in a single field or just the exclusion list of clientID's without needing as many occurrences of different tables to make this work by employing the ExecuteSQL function.

          • 2. Re: Table relationship - not equal to
            jimscott77

                 Thanks,

                 I went the SQL route.

                 Added Custom Function: ValueExists(Value;ValueList) -- [is the Value in the ValueList?]

                   Let (
                   n = ValueCount ( ValueList );
                     If ( n > 0 ;
                       If(Exact(GetValue ( valuelist ; 1) ; value) ; 1 ;
                       ValueExists (value; RightValues (valuelist; n -1)))))

                 Added a calculated field, ActiveFlag, to Clients.

                   Calculation: ActiveFlag = not ValueExists(bBusiness num;ExecuteSQL ( "select ClientNum from NonClients " ; "" ; ""))

            • 3. Re: Table relationship - not equal to
              philmodjunk

                   Not IsEmpty ( Filtervalues ( ValueList ; Value ) )