I suspect this is going to be a fairly easy one once I know how to do it! I've tried searching through the help file and the forum but not managed to answer my question yet...

I have two tables, one for recording delivery information and one for tracking refunds. They are not linked but share a common TicketReference field so could be in theory...

I have a typed list from my delivery table and I want to add a boolean field stating whether or not a row exists in the refunds table.

I have the following custom code so far:

' __LLBLGENPRO_USER_CODE_REGION_START AdditionalMembers
Private _columnRefunded As DataColumn
' __LLBLGENPRO_USER_CODE_REGION_END

' __LLBLGENPRO_USER_CODE_REGION_START InitClass
_columnRefunded = New DataColumn("Refunded", GetType(Boolean), Nothing, MappingType.Element)
_columnRefunded.ReadOnly = True
_columnRefunded.Caption = "Refunded"
Me.Columns.Add(_columnRefunded)
' __LLBLGENPRO_USER_CODE_REGION_END

' __LLBLGENPRO_USER_CODE_REGION_START InitMembers
_columnRefunded = Me.Columns("Refunded")
' __LLBLGENPRO_USER_CODE_REGION_END

Do I need anything in AdditionalFields or any other sections of custom code? What do I need to put in place of nothing given that it's not just a simple combination of other fields (which I think I could manage but you never know! )

I had been doing this in SQL with a query which looked something like:

SELECT TicketDeliveryNote.TicketRef, TicketDeliveryNote.ClientName,
            TicketDeliveryNote.BookerName, TicketDeliveryNote.LeadPassengerName,
            TicketDeliveryNote.ContactName, TicketDeliveryNote.Ticket,
            TicketDeliveryNote.Origin, TicketDeliveryNote.Destination,
            dbo.ufn_GetDateOnly(TicketDeliveryNote.TravelDate) AS TravelDate,
            dbo.ufn_GetDateOnly(TicketDeliveryNote.DateAdded) AS DateAdded,
            TicketDeliveryNote.DeliveryMethod, TicketDeliveryNote.InitialAgent,
            TicketDeliveryNote.HoldTicketIssue, TicketDeliveryNote.Printed,
            TicketDeliveryNote.Cancelled, TicketDeliveryNote.HoldReason,
            [b](SELECT COUNT(DISTINCT TicketRef) FROM TicketRefund WHERE TicketRef = TicketDeliveryNote.TicketRef) AS Refunded[/b]
FROM TicketDeliveryNote
ORDER BY TicketDeliveryNote.Ticket

(SELECT COUNT(DISTINCT TicketRef) FROM TicketRefund WHERE TicketRef = TicketDeliveryNote.TicketRef) AS Refunded

You can do this using a DynamicList, and a field with a ScalarQueryExpression Please refer to LLBLGen Pro manual: "Using the generated code -> Field expressions and aggregates"

Thanks Walaa, I managed to do it for that query. I'm having trouble with another that relies on having a kind of nested replica of the same table if you follow me.

The SQL would be:

SELECT DISTINCT ContactName, ContactEmail,
 (SELECT COUNT(*)
 FROM TicketDeliveryNote
 WHERE ContactName = CDN.ContactName AND ContactEmail = CDN.ContactEmail) AS Occurances
FROM TicketDeliveryNote CDN
WHERE (ContactName LIKE '%patel%')
ORDER BY Occurances DESC

I'm struggling with what to go in the bit after (TicketDeliveryNoteFields.ContactEmail =

Dim fields As New ResultsetFields(2)
fields.DefineField(TicketDeliveryNoteFields.ContactEmail, 0)
fields.DefineField(New EntityField("Occurances", _
    New ScalarQueryExpression(TicketRefundFields.TicketRef.SetAggregateFunction(SD.LLBLGen.Pro.ORMSupportClasses.AggregateFunction.Count), _
    (TicketDeliveryNoteFields.ContactEmail = TicketDeliveryNoteFields.ContactEmail))), 1)

I tried that and I tried fields(0). Either one returns 1212 where I'm expecting numbers like 1 and 3.

I looked into AggregateFunctionToApply and a few other areas in the help but I've not managed to get it right yet.

Also, I'd like to be able to sort on this new aggregate field - how can I do that?

Any suggestions?

Thanks,

James

Another failed attempt. Am I getting close?

fields.DefineField(TicketDeliveryNoteFields.ContactEmail, 1, "Occurances")
fields(1).AggregateFunctionToApply = SD.LLBLGen.Pro.ORMSupportClasses.AggregateFunction.Count

Hi James, I think your SQL

SELECT DISTINCT ContactName, ContactEmail,
 (SELECT COUNT(*)
 FROM TicketDeliveryNote
 WHERE ContactName = CDN.ContactName AND ContactEmail = CDN.ContactEmail) AS Occurances
FROM TicketDeliveryNote CDN
WHERE (ContactName LIKE '%patel%')
ORDER BY Occurances DESC

could be rewritten as follows

SELECT DISTINCT ContactName, ContactEmail, COUNT(*) Occurances
FROM TicketDeliveryNote
WHERE (ContactName LIKE '%patel%')
GROUP BY ContactName, ContactEmail
ORDER BY Occurances DESC

So, you dont need the subquery here.

Well, now the Aggregate Sort stuff:

Here is a example in Northwind (sorry, is in C# ):

Statement: I want the Number of ORDERS shipped in Brazil with the Customer-Employee combination, ordering by the NumberOfOrders Descending.

// define fields
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(OrdersFields.CustomerId, 0, "CustomerID");
fields.DefineField(OrdersFields.EmployeeId, 1, "EmployeeID");
fields.DefineField(OrdersFields.OrderId, 2, "NumberOfOrders",  AggregateFunction.Count);

// grouping (CustomerID and EmployeeID)
GroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);
groupBy.Add(fields[1]);

// filter (only orders shipped in Brazil)
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(OrdersFields.ShipCountry == "Brazil");

// sorting  on NumerOfOrders(here is the really good stuff)
SortExpression sortBy = new SortExpression();
sortBy.Add(new SortClause(fields[2], null, SortOperator.Descending));

// retrieve
DataTable results = new DataTable();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
    adapter.FetchTypedList(fields, results, filter, 0, sortBy, false, groupBy);
}

The above produce this SQL:

SELECT DISTINCT [Northwind].[dbo].[Orders].[CustomerID], [Northwind].[dbo].[Orders].[EmployeeID], COUNT([Northwind].[dbo].[Orders].[OrderID]) AS [NumberOfOrders] 
FROM [Northwind].[dbo].[Orders]  
WHERE ( ( [Northwind].[dbo].[Orders].[ShipCountry] = 'Brazil')) 
GROUP BY [Northwind].[dbo].[Orders].[CustomerID], [Northwind].[dbo].[Orders].[EmployeeID] 
ORDER BY NumberOfOrders DESC

Enjoy