0

Background

I'm using 4D Database (v17.5) and trying to query records. I have the following simplified schema:

Customers (table)
    -CustomerID (field)
    -CompanyName (field)
    -City (field)
    -State (field)

SalesOrders (table)
    -CustomerID (field)
    -OrderTotal (field)

They are tied together with a Many to One automatic relation:

[Customers]CustomerID (O)<-------------------------(M) [SalesOrders]CustomerID
            (ParentCustomer)              (ChildSalesOrders)

I can query to get a set of [SalesOrders] records using either of the following:

ds.SalesOrders.query("OrderTotal > 500 AND ParentCustomer.State = 'New York'")
ds.Customers.query("State = 'New York' AND ChildSalesOrders.OrderTotal > 500").ChildSalesOrders

Problem

Some customers have no orders at all, and if I want to find a set of customers with zero related orders, I am having trouble.

I have gotten the following to work:

$voCustomersToCheck:=ds.Customers.query("State = 'New York')
$voCustomersWithoutOrders:=ds.Customers.newSelection()

For each($voCustomer;$voCustomersToCheck)
    If ( $voCustomer.ChildSalesOrders.length > 0 )
        $voCustomersWithoutOrders.add($voCustomer)
    End if
End for each

What I'd like to do instead is something like:

$voCustomersWithoutOrders:=ds.Customers.query("State = 'New York' AND ChildSalesOrders.length = 0')

That doesn't work, though.

Question

Is what I'm trying to accomplish possible?

Kelderic
  • 6,502
  • 8
  • 46
  • 85

2 Answers2

1

In v17.5, it is impossible.

That said, if you update to v18 or higher, you can use the NULL keyword; so the solution is to write:

$voCustomersWithoutOrders:=ds.Customers.query("State = 'New York' AND ChildSalesOrders = NULL')
Kelderic
  • 6,502
  • 8
  • 46
  • 85
Umberto Migliore
  • 317
  • 4
  • 17
  • 1
    It's been a few months since I looked at this, but if I remember correctly, the consensus is that on v17.5, it isn't possible. I'd need to move to v18 for the code you wrote to work, I think. – Kelderic May 16 '21 at 17:26
0

Classic 4D can do this. I'm not sure about ORDA. Here is an example ;

CREATE EMPTY SET([Companies];"NoSales")

ALL RECORDS([Companies])
CREATE SET([Companies];"AllRecsSet")

ALL RECORDS([Invoices])
RELATE ONE SELECTION([Invoices];[Companies])

CREATE SET([Companies];"InvoiceSet")

DIFFERENCE("AllRecsSet";"InvoiceSet";"NoSales")

USE SET("NoSales")

CLEAR SET("InvoiceSet")
CLEAR SET("InvoiceSet")
CLEAR SET("NoSales")
MacGuido
  • 46
  • 1