v2.6

I am trying to create a new field that performs addition/subtraction of the results of the subquery defined in other fields without having to repeat the code again.

Resultset fields = new ResultsetFields(4);
fields.DefineField(new EntityField("OnOrder", new ScalarQueryExpression(OrderItemFields.ProductId.SetAggregateFunction(AggregateFunction.Count), onOrderPredicateFilter)), 0);
fields.DefineField(new EntityField("Sold", new ScalarQueryExpression(SalesItemFields.ProductId.SetAggregateFunction(AggregateFunction.Count), soldPredicateFilter)), 1);
fields.DefineField(ProductFields.Inventory, 2, "Inventory");

Effectively what I'd like to do is:

fields.DefineField(fields[1] - fields[0] - fields[2], 3, "Needed");

I have read the ScalarQueryExpression and Expression documentation and searched the forums for a combination of "ScalarQueryExpression", "Expression", "ResultsetFields", "EntityField", and "Reference".

Question(s): 1) How do I refer to the EntityField / ScalarQueryExpression that was defined already in the ResultsetFields, 2) Can I refer to this in the ResultsetFields.DefineField() or does this need to be defined in a separate EntityField expression? 3) or do I need to repeat all of the code again in the ResultsetFields.DefineField for field index 3?

Thanks!

Sorry, this is not possible. You would need to repeat the logic again in the definition of the 3rd field.

Matt

I been reading about ScalarQueryExpressions, Expressions, ExOp, etc. and have not discovered how to combine two ScalarQueryExpressions into one DefineField call.

The intent here is to get counts from two separate tables and subtract one value from another, and then subtract the value of a field from that.

Can you point me in the right direction?

fields.DefineField(
     new ScalarQueryExpression(SalesItemFields.ProductId.SetAggregateFunction(AggregateFunction.Count), soldPredicateFilter))
     -
     new ScalarQueryExpression(OrderItemFields.ProductId.SetAggregateFunction(AggregateFunction.Count), onOrderPredicateFilter))
    -
    fields.DefineField(ProductFields.Inventory, 2, "Inventory");
), 3, "Needed");

Maybe this example could give you some hints: http://www.llblgening.com/archive/2009/09/llblgen-pro-expressions-and-scalar-queries/

ForwardMark wrote:

Effectively what I'd like to do is:

fields.DefineField(fields[1] - fields[0] - fields[2], 3, "Needed");

Here is how I resolved this: 1. Define your PredicateExpression(s) for the conditions to use in the SUM() sub-queries.

1. SUM the fields you need in the result (e.g. "total sold" as a sum of all sold items) in the ResultsetFields.DefineField defintion:

rsFields.DefineField(new EntityField("TotalSold", new ScalarQueryExpression(SalesItemFields.ProductId.SetAggregateFunction(AggregateFunction.Count), filterTotalSold)), 1);

1. To perform arithmetic on the various fields, you need to repeat the subquery in new Expression definitions:

Expression exInventoryMinusTotalSold = 
    new Expression(
        ItemFields.Inventory, 
        ExOp.Sub, 
        new EntityField("TotalSold", new ScalarQueryExpression(SalesItemFields.ProductId.SetAggregateFunction(AggregateFunction.Count), filterTotalSold)));

1. To add/subtract multiple fields, you'll need to refer to this Expression to chain together the different operands, e.g.:

Expression exAddAnotherSumToTheAbove =
    new Expression(
        exInventoryMinusTotalSold,
        ExOp.Add,
        new EntityField("NewSum", new SQE(..add definition here...)));

1. And then (finally) you refer this new expression back to the rsFields thusly:

rsFields[5].ExpressionToApply = exAddAnotherSumToTheAbove;

Hope this helps out.