This is a migrated thread and some comments may be shown as answers.

Calculated Colulmn w/ Lookup

12 Answers 147 Views
GridView
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Mark
Top achievements
Rank 1
Mark asked on 31 Mar 2011, 09:30 PM
I have a GridView with a combobox (cbo).  The cbo has a query lookup source for DisplayMemberPath and SelectedValueMemberPath, both working great.

<telerik:GridViewComboBoxColumn UniqueName="cbo_RawPack" Width="Auto" 
                                                DisplayMemberPath="packDesc"
                                                SelectedValueMemberPath="RawPackID"
                                                DataMemberBinding="{Binding RawPackID, Mode=TwoWay}"
                                                SortMemberPath="RawPackID" ex:ComboBox.Mode="Async" />

I'm trying to also create a calculated column based a field from this query ("packLBs") which is not shown in the GV.  Is there a way I can do this cleanly and without hardcoding it?

So far I have:
//Poundage: unitQty * poundBasis
            System.Linq.Expressions.Expression<Func<tblPOLine, decimal>> exp2 = poline => poline.unitQty;
            (dg_tblPOLines.Columns["ex_Poundage"] as GridViewExpressionColumn).Expression = exp2;

where "poline.unitQty" is a field in the GV.  I then want to extend the formula to multiply that by the combobox lookup field "packLBs".  This column is a calculated field at the database level so I'm trying to remain loosely coupled.

The 'logic' of the statement would look something like:
System.Linq.Expressions.Expression<Func<tblPOLine, qryRawPack, decimal>> exp2 = (poline, rawLine) => poline.unitQty * rawLine.packLBs WHERE (rawLine.RawPackID == poline.RawPackID);

12 Answers, 1 is accepted

Sort by
0
Maya
Telerik team
answered on 04 Apr 2011, 09:08 AM
Hello Mark,

You may take a look at the sample project attached in this forum thread for a reference.
 

Best wishes,
Maya
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
Mark
Top achievements
Rank 1
answered on 04 Apr 2011, 02:03 PM
Hi Maya,

Unfortunately I wasn't able to resolve what I really need with your reference.  I made some database changes so that I now have values (and no longer need the lookup), however I need the Calculated column to base its calculated value on a condition.  For example, what I'm trying to do is bases a value in my property 'tblPOLine.packKilosID' select the equation I'd like to use for the cell on the particular row.

I've tried

System.Linq.Expressions.Expression<Func<tblPOLine, decimal>> exp2 = (tblPOLine.packKilosID == 1) ? poline => Math.Round((poline.unitQty * poline.packKilosID * 2.2046226m), 0) : poline => Math.Round((poline.unitQty * poline.packKilosID), 0);

however it rejects the 'tblPOLine.packKilosID ==1' with "An object reference is required for the non-static field, method ..."


This is the RIA created class -

    public partial class tblPOLine
    {
  
        internal sealed class tblPOLineMetadata
        //public class tblPOLineMetadata
        {
  
            // Metadata classes are not meant to be instantiated.
            private tblPOLineMetadata()
            {
            }
  
.... 
  
            [Display(Name = "Packaging", Description = "Indicate the size of the packaging.")]
            //[Required(ErrorMessage = "A packaging type selection is required.")]
            public decimal packKilosID { get; set; }
  
....
  
  
        }
    }

any additional help is appreciated.  I'm not sure if you can do such a conditional selection.
0
Accepted
Yavor Georgiev
Telerik team
answered on 04 Apr 2011, 02:39 PM
Hi Mark,

 I'm afraid that the problem arises because you don't supply the expression in the correct form to the compiler. Could you please try declaring your expression like:
Expression<Func<tblPOLine, decimal>> exp2 = poline => poline.packKilosID == 1 ? Math.Round((poline.unitQty * poline.packKilosID * 2.2046226m), 0) :  Math.Round((poline.unitQty * poline.packKilosID), 0);

All the best,
Yavor Georgiev
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
Mark
Top achievements
Rank 1
answered on 04 Apr 2011, 02:55 PM
Hello Yavor,

Thanks so very much!  ...  This is a terrific "feature" and I would encourage adding it to the some of the documentation of the calculated column as an example.

I appreciate your help as well as Maya's very much.

Mark
0
Mark
Top achievements
Rank 1
answered on 04 Apr 2011, 03:56 PM
Hi Yavor,

Is there a way to use a lookup value in another table based on the poline.packKilosID?  For example in logic

Get the lookup value of tblRawPack.rawPackID associated with this grid line item (poline below).
Based on poline.packKilosID != 1 select the proper function (that part is working well).

Something close to

System.Linq.Expressions.Expression<Func<tblPOLine, tblRawPack, decimal>> exp2 = (poline, rawPack) => poline.rawPackID != 1 ? Math.Round((poline.unitQty * (rawPack.packKilos) * 2.2046226m), 0) : Math.Round((poline.unitQty * rawPack.packKilos), 0);

The goal here is to convert all choices from pounds to kilograms unless the user selects the first input choice which should not be converted because it is entered in pounds. tblRawPack is a list of bag sizes (e.g. 15kg, 30kg, 60kg, etc.)
 and the condition 'poline.rawPackID = 1' is the PK of the one entry where input is in pounds.
0
Yavor Georgiev
Telerik team
answered on 04 Apr 2011, 04:50 PM
Hi Mark,

 The expression must be of type Func<TParameter, TReturn>, because it only ever takes one parameter (the data item). In fact, I believe you should receive an exception if you supply a LambdaExpression with more than one parameter.

 That said, you can also reference variables and functions local to the scope you are declaring the expression in. Example:
// using a custom class
IRawPackProvider rPP = GetSomeClassThatFetchesRawPacks();
Expression<Func<tblPOLine, decimal>> exp2 = poline => poline.rawPackID != 1 ? Math.Round((poline.unitQty * (rPP.Get(poline.rawPackID).packKilos) * 2.2046226m), 0) : Math.Round((poline.unitQty * rPP.Get(poline.rawPackID).packKilos), 0);
 
// using an anonymous method that you define for brevity
// supposing that 'dbContext' is an EntityFramework context, for example
Func<int, tblRawPack> getPack = id => dbContext.RawPacks.Where(r => r.Id == id).FirstOrDefault();
Expression<Func<tblPOLine, decimal>> exp2 = poline => poline.rawPackID != 1 ? Math.Round((poline.unitQty * (getPack(poline.rawPackID).packKilos) * 2.2046226m), 0) : Math.Round((poline.unitQty * getPack(poline.rawPackID).packKilos), 0);

 The ExpressionColumn only know to call the expression you provide with a single parameter - the data item for the row. There is no way to modify the behavior of the column in this case. You can't tell it how to handle the expression, and you shouldn't. You can do the necessary lookup inside the expression. Think of it as code that gets executed whenever the ExpressionColumn needs to show a cell. It can do everything you like, it' just code.

 However, do note that intensive operations such as web service calls and database lookups can take some time to process. Since the expression in the column is executed whenever a cell is brought into view, if it takes a long time to process it can reduce the scrolling performance of your RadGridView. This is why it's best to perform the actual lookups in the Model where you can cache the data.

Regards,
Yavor Georgiev
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
Mark
Top achievements
Rank 1
answered on 04 Apr 2011, 05:24 PM
Hi Yagor,

I'll give your sample some try, thank you very much.
In respect to Lamba, it has no difficulty with more than one input paramters, it just takes the form of:

Func<T1, T2, Tn, TResult> exp = (var1, var2, varn) =>



0
Mark
Top achievements
Rank 1
answered on 04 Apr 2011, 11:49 PM
Hi Yagor,

This actually worked very well.  I made a schema database change and now use a convPounds property which will allow me to use other unit of measure conversion if need be and be a bit more loosely coupled.

What I've settled on is

//Lookup tblRawPack for calculated column
RoasterWerksDomainContext ctxt = new RoasterWerksDomainContext();
ctxt.Load(ctxt.GetTblRawPacksQuery());
Func<int?, tblRawPack> getPack = id => ctxt.tblRawPacks.Where(r => r.rawPackID == id).FirstOrDefault();
System.Linq.Expressions.Expression<Func<tblPOLine, decimal>> exp = poLine => Math.Round((poLine.unitQty * (decimal)(getPack(poLine.rawPackID).convPounds)), 0);
            (dg_tblPOLines.Columns["ex_Poundage"] as GridViewExpressionColumn).Expression = exp;

I now want to aggregate a column sum, which I'm assuming will need a custom aggregate.  Can you please give me a little direction?  As there is a lookup involved I'm not quite certain how to proceed.

Thanks again for your assitance.
0
Yavor Georgiev
Telerik team
answered on 05 Apr 2011, 05:00 AM
Hello Mark,

 You can use our AggregateFunction<TElement, TResult>, which just like our ExpressionColumn, takes an expression telling it how to aggregate over a collection of data.

All the best,
Yavor Georgiev
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
Mark
Top achievements
Rank 1
answered on 05 Apr 2011, 01:56 PM
Hi Yagor,

Thank you once again.  Is there a sample I can reference to see how this is constructed?  I'm having some difficulty in implementing it.

Mark
0
Yavor Georgiev
Telerik team
answered on 05 Apr 2011, 02:10 PM
Hi Mark,

 I believe this should work for you:
RoasterWerksDomainContext ctxt = new RoasterWerksDomainContext();
ctxt.Load(ctxt.GetTblRawPacksQuery());
 
Func<int?, tblRawPack> getPack = id => ctxt.tblRawPacks.Where(r => r.rawPackID == id).FirstOrDefault();
 
AggregateFunction<tblPOLine, decimal> aggregateFunction = new AggregateFunction<tblPOLine, decimal>();
aggregateFunction.AggregationExpression = poLines => poLines.Sum(poLine => Math.Round((poLine.unitQty * (decimal)(getPack(poLine.rawPackID).convPounds)), 0));
 
column.AggregateFunctions.Add(aggregateFunction);

 These generic aggregate functions work in the same way as the ExpressionColumn, although the parameter they take is a collection, rather than a single data item.

Greetings,
Yavor Georgiev
the Telerik team
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items
0
Mark
Top achievements
Rank 1
answered on 05 Apr 2011, 02:24 PM
Hi Yavor,

Sure did, thanks for your troubles.

Best,
Mark
Tags
GridView
Asked by
Mark
Top achievements
Rank 1
Answers by
Maya
Telerik team
Mark
Top achievements
Rank 1
Yavor Georgiev
Telerik team
Share this question
or