1. Home
  2. Forum
  3. UWP
  4. How to update a Cell Value Calculated From 2 Other Cells. Somewhat Complex Expression..

How to update a Cell Value Calculated From 2 Other Cells. Somewhat Complex Expression..

Hi,

I have a sfDataGrid in my UWP project.  I want to be able to edit a numeric value in GridNumericColumn and have the GridNumericColumn to the right of it calculate from my change and from another GridNumericColumn Cell in the row that hadn't been edited.  Basically like a spreadsheet works when you change one value another value is automatically updated.

I tried updating the ObservableCollection in a sfDGIndexes_CurrentCellEndEdit  but remembered that ObservableCollections only work when row added or deleted. 

I looked at GridUnboundColumn for the calculated Cell which appears to work. But I cannot follow the instructions when there is both an Expression and I also need a Format string. I am looking for a Percentage display with 1 or 2 decimals.  ie/ 45.7%.  Also, it looks like the Expression cannot take a value like ((Value1/Value2)-1)*100.

CORRECTION: It does look like that Expression "((Value1/Value2)-1)*100" does work.  I must have had a typo.  The real question now is how to format an Expression Result to Percentage?  I tried Format="P" and "{0:P}" and neither worked

How can I calculate a Cell from 2 other Cells with a slightly complex calculation and percentage formatting?

Thanks in advance.

9 Replies 1 reply marked as answer
RO Robert October 1, 2020 06:16 PM UTC

OK.  I've worked on this more and not getting the results I am looking for.

The Format="'{0:P}'"  sort of worked but I am getting the single quotes in the calculated Cell, '10.45%'

Also, the calculated GridUnboundColumn doesn't update when I edit one of the input Cells unless I go in to edit the caclulated GridUnboundColumn Cell.

Is there any way to get the SfDataGrid to calculate a Cell live as the other Cells are changed the way a spreadsheet does?

MA Mohanram Anbukkarasu Syncfusion Team October 2, 2020 08:14 AM UTC

Hi Robert, 

Thanks for contacting Syncfusion support.  

We have prepared a simple sample using GridUnboundColumn to replicate your scenario using QueryUnBoundColumnValue and it is available in the following link for your reference.  


UG references :  

Please have a look at the provided sample and the UG references and let us know if the given solutions doesn’t meet your requirement.  

Regards, 
Mohanram A. 


Marked as answer
RO Robert October 4, 2020 06:09 PM UTC

Hi,  That example works well.

However there is one more thing I need. If there is a divide by 0 in the Expression it crashes the app.

I am adding a row to the grid via a <Button> Code behind. Since I don't have values for Value1 and Value2 the Model populates them as 0.  However my GridUnboundColumn Expression now has a divide by 0 which crashes the app.  

How can I have the Expression="((Value1/Value2)-1)*100"   be ignored for a new row?
Can there be an "if" in the Expression?

I have included my modification of your example and some sample code is:
 
private void AddRow_Click(object sender, RoutedEventArgs e)
      {
         //this following doesn't work because Value1 and Value2 get initiallized to 0.
         //Then have divide by 0 in Expression
         //How to fix if new row has no values??
         //Can there be an "If" in the expression?
         
         Model newRow = new Model() {};

         //this following works because Value1 and Value2 are not 0
         
         //Model newRow = new Model() { Value1 = 10, Value2 = 20 };
         
         pageCollection.Add(newRow);

      }

Attachment: SfDataGrid_GridUnboundColumn490183072_Mod1_bfd7754d.zip

MA Mohanram Anbukkarasu Syncfusion Team October 5, 2020 04:50 PM UTC

Hi Robert,  

Thanks for the update.  

We are currently working on this query. We will update with further details on 7th October 2020. We appreciate your patience until then.  

Regards, 
Mohanram A. 


RO Robert October 6, 2020 12:46 AM UTC

Honestly,  The way the Expression works in a GridUnboundColumn and the QueryUnboundColumnValue in the Data grid are VERY confusing! 
  • When is the Expression evaluated??? 
    On Grid Load? On Row Created?  When a value in the row changes?
    When editing a Cell the Expression is based on and typing numbers???
  • When does the QueryUnboundColumnValue fire??
    On row create? On Add new row? When a Cell in the grid is changed and edit stopped??
  • What does the UpdateUnboundColumn do?  I have in my CellEndEdit but it doesn't seem to do anything.  It doesn't fire the QueryUnboundColumnValue event.
  • What does Format do and how does it work with an Expression and QueryUnboundColumnValue?
  • Does the grid have to be tied to an ObservableCollection?  And how to implement the PropertyChanged?
I have a DataGrid win a sample that is working about how I need it but I can't get it to work correctly in my app.  In the example the QueryUnboundColumnValue fires when I make any change to an entry.  In my app it only fires when the grid is loaded.

Really the documentation on UnboundColumn is very sparse.  A thorough write up on DataGrid UnboundColumn would be most helpful.

MA Mohanram Anbukkarasu Syncfusion Team October 6, 2020 04:16 PM UTC

Hi Robert, 

Sorry for the inconvenience.  

We are able to understand your requirement. We need two more business days to validate all this. We will update with further details on 8th October 2020. We appreciate your patience and understanding until then.  

Regards, 
Mohanram A. 


MA Mohanram Anbukkarasu Syncfusion Team October 8, 2020 02:28 PM UTC

Hi Robert, 

Thanks for your patience.  


We have checked the reported scenario. If you are using QueryUnboundColumnvalue event, there is no need to use Expression for the column. You can include validation to avoid divide by zero exception while adding new row by including the condition within the QueryUnboundColumnValue event as shown below. 

Code example :  

private void DataGrid_QueryUnboundColumnValue(object sender, Syncfusion.UI.Xaml.Grid.GridUnboundColumnEventsArgs  
{ 
    if (e.UnBoundAction == UnBoundActions.QueryData) 
    { 
        var value1 = Convert.ToInt32(e.Record.GetType().GetProperty("Value1").GetValue(e.Record)); 
        var value2 = Convert.ToInt32(e.Record.GetType().GetProperty("Value2").GetValue(e.Record)); 
        if (value1 != 0 && value2 != 0) 
        { 
            double value = ((value1 / value2) - 1) * 100; 
            e.Value = value.ToString() + "%"; 
        } 
        else 
            e.Value = 0; 
    } 
} 


You can make the QueryUnboundColumnValue  method to trigger when changing any cell value by using SfDataGrid.CurrentCellValidated event as shown below. 

Code example :  

private void DataGrid_CurrentCellValidated(object sender, CurrentCellValidatedEventArgs e) 
 { 
     this.dataGrid.GetUnBoundCellValue(this.dataGrid.Columns["Percentage"], e.RowData); 
 } 


However the value in the unbound column of the corresponding row is not updated without clicking on the unbound column cell. We are still validation this. We will update with further details on 12th October 2020. We appreciate your patience and understanding.  

Regards, 
Mohanram A. 


RO Robert October 9, 2020 04:23 PM UTC

Thank you for you suggested code.  It will help me.

But I did finally find the cause of my problem. The class for the row in the table was a Derived Class that inherited from another class.  I had the PropertyChanged and OnPropertyChanged in BOTH the derived and base class.  This caused one of the OnPropertyChanged events to be ignored (I think the one in the base class) So the CurrentCellEndEdit wasn't firing when I made a change to one of my properties.  

The correct way to have PropertyChanged with a Derived Class is to have the PropertyChanged and OnPropertyChanged in the base class and then reference it in the derived class.

Ex/ Base Class

public class IndexLink:INotifyPropertyChanged
   {
      public int Id { get; set; }
      public int ForeignId { get; set; }
      public string Symbol { get; set; }

      double price;
      public double Price { 
         get { return price; }
         set
         {
            if (price != value)
            {
               price = value; 
               OnPropertyChanged("Price");
            }
         }
      }
      
      public event PropertyChangedEventHandler PropertyChanged;
      public void OnPropertyChanged(string propName)
      {
         if (this.PropertyChanged != null)
            PropertyChanged(this, new PropertyChangedEventArgs(propName));
      }
   }
.
.

Derived Class
public class IndexLinkWPrice : IndexLink
   {
      private double lastPrice;
      public double LastPrice {
         get { return lastPrice; }
         set
         {
            lastPrice = value;
            base.OnPropertyChanged("LastPrice");
         }
      }

Perhaps this will help some others.


MA Mohanram Anbukkarasu Syncfusion Team October 12, 2020 12:28 PM UTC

Hi Robert, 

Thanks for the update.  

We are glad to know that the reported problem has been resolved at your end. Please let us know if you have any other queries. 

Regards, 
Mohanram A. 


Loader.
Up arrow icon