Count

["... you mean these fans?"]

Hi,

 

I think I'm in the right forum list.

 

I have a client who ships with UPS.

 

Each Tracking No. could have many items.

 

I can build out the report that list the Tracking Number separately ( Once ) with each "Description" list correctly underneath the Tracking No.

 

123456ABC

    Description

    Description

    Description

    Description

    Description

654321CBA

    Description

    Description

    Description

    Description

    Description

    Description

 

However, my trouble is that I want to provide my customer with a count of tracking number on the invoice.

 

Meaning: I have 11 total tracking number, 6 are 123456ABC and 5 are 654321CBA.

 

I though I could build a "Self" relationship but I am having trouble.  I continually count 11.

 

Can anybody help me here.  My goal is to have a calculation that produces a count of 2 Tracking Number.

 

Thank you.

 

Tom :-)

[jbante]

Assuming you don't want to have a separate table for tracking numbers, you could use the ExecuteSQL function and SELECT DISTINCT:

Let ( [
    ~trackingNumbers =
        ExecuteSQL (
            "SELECT DISTINCT trackingNumber FROM InvoiceLineItem WHERE id_Invoice = ?"
            "" ;
            "" ;
            Invoice::id
        )
] ;
    ValueCount ( ~trackingNumbers )
)

I don't know if the SQL COUNT() function can be used in combination with DISTINCT within the SQL. Also, in practice I would wrap the schema references (trackingNumber, InvoiceLineItem, and id_Invoice) in SQL-oriented quoting functions so that I don't break the query when changing field or table names.