Relationship for "not in"

[hal9000]

I have a customers table and an orders table. I want to create an occurrence of the customers table that only includes customers with no orders in the orders table.

I have a customerID field in both tables. I added a relationship between the customers2 table occurrence and the orders2 table occurrence. The relationship is a "not equal to" (equal sign with a "no" line though it) relationship.

So I expect a layout based on the customers2 table occurrence to include only customers with no orders, but no, it shows all customers. What am I doing wrong? Or what should I do to make this work? The purpose of the customers2 occurrence is to use in a value list.

Thanks in advance,

Hal

[mr_vodka]

So I expect a layout based on the customers2 table occurrence to include only customers with no orders, but no, it shows all customers. What am I doing wrong? Or what should I do to make this work? The purpose of the customers2 occurrence is to use in a value list.

A layout will not filter out by the criteria in your relationship. It will show you all the records in your table occurrence. You will still need have a found set of records.

What you need is a list of customerIDs that have orders first. So make a new relationship from customers to orders using a Cartesian join (X) operator. Now create a value list that returns a list of customerIDs from the new orders2 TO.

Create a calculation using ValueListItems () to return the list of customerIDs.

Use this calc as the parent side match to a new table occurrence of Customers2 using not equal. Make your value list that you want based off this new relationship.

As a note, you could have used List () instead of create the value list and ValueListItems (), but then you would have lots of repeat customerIDs in your list whereas by using the value list it get rid of dups.

[Trevisan]

John,

I got stuck in this step of your great explanation:

Use this calc as the parent side match to a new table occurrence of Customers2 using not equal. Make your value list that you want based off this new relationship.

I have now a table with field CustomersID and CustomerID_With_Orders.

CustomersID is a unique serial number, and CustomerID_With_Orders have the list of CustomersID found in the orders table.

When I make a relationship Customer_With_Orders not equal CustomerID it shows me all records in the first table because all the values that are being matched are different:

Table Customers

CustomerID | Customer_with_orders

0001 | {0001;0003}

0002 | {0001;0003}

0003 | {0001;0003}

Table Orders

OrderID | CustomerID

0001 | 0001

0002 | 0003

in the relationship Customers - Customers(2) (CustomersID_With_Orders not equal CustomerID)

CustomersID_With_Orders not equal CustomerID | Value

{0001;0003} ≠ 0001 | TRUE

{0001;0003} ≠ 0002 | TRUE

{0001;0003} ≠ 0003 | TRUE

Since all of them are true, this relationship returns me all the IDs again.

Did I understand your example correctly?

Thank you,

RT

Edited August 11, 2009 by Guest

[mr_vodka]

Table Customers

CustomerID | Customer_with_orders

0001 | {0001;0003}

0002 | {0001;0003}

0003 | {0001;0003}

Customer_with_orders should be returning as list as such:

0001

0003

in the relationship Customers - Customers(2) (CustomersID_With_Orders not equal CustomerID)

CustomersID_With_Orders not equal CustomerID | Value

{0001;0003} ≠ 0001 | TRUE

{0001;0003} ≠ 0002 | TRUE

{0001;0003} ≠ 0003 | TRUE

Referring to the earlier calc list of:

0001

0003

For your example purposes, I am going to denote it as 0001¶0003 ( pilcrow denotes carriage return )

0001¶0003 ≠ 0001 | FALSE because 0001 matches in multi-line

0001¶0003 ≠ 0002 | TRUE

0001¶0003 ≠ 0003 | FALSE because 0003 matches in multi-line

[Trevisan]

Thanks again John.

You are right. I made a simple database that worked as you said.

Only issue now it to make the list work.

Here is an example I have made.

Example

Layout #3 shows Table1::Field1 and Table2:Field2 together and the Table2:Field2 shows that the ID 0002 is Not In Table 1.

But when I make a list out of Table2::Field2 it shows me all the items there...

Any clue on how to solve it?

Thank you,

[mr_vodka]

You should be using the relationship as part of yuor value list. Most likely you are using all values instead of related values only.