Count

Followers

January 30, 201313 yr

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

654321CBA

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 :-)

January 30, 201313 yr

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.