PDA

View Full Version : Editable Factors and Prices In A Grid That Are Connected



rkanemeier
18 Sep 2013, 2:37 PM
I have an editable grid with prices and factors. If the user changes the price, the factor needs to be updated to reflect the changed price. If the user changes the factor, the price needs to be changed to reflect the new factor. Here are the formulas:

factor = ( (price - cost) / price ) * 100
price = cost / (1 - (factor/100)

The grid is set up to show 4 prices and 4 factors:

price1, factor1, price2, factor2, etc etc

I have an edit listener on the grid. I check for a changed value. If the value changed, I determine which column was changed, and update the other column with the correct value. I am doing this by updating the store using store.getAt(x).set('COLUMN', value).

The problem I am having is, when I change price 1, and tab to factor 1, the "editable cell" does not display the "recalculated" factor, even though the store value and the grid value contain the recalculated value. Is there a way to get the grid to display the newly changed valued prior to the tab key being pressed?

I may end up having to re-engineer this grid by having a toggle button to switch editing modes. You either edit prices, or you edit factors, but not both. I'd like to continue using my currently solution.

Any thoughts?

existdissolve
18 Sep 2013, 7:24 PM
Without seeing your code, I'm not sure why it's not working. I tried out a simple example (see below), and it appears to work as you're expecting it to.

One suggestion, though.

If the fields needing editing are all in the same row, there's no need to look up the record via the store. The edit event of the CellEditing plugin passes the entire record for the row to your handler, so you could simple update the field directly via the Model's API (e.g., assuming "e" is the edit event object: e.record.set( 'factor', somevalue ) ).

je

rkanemeier
19 Sep 2013, 11:44 AM
existdissolve,

thanks for your reply! that fiddler code editor is awesome! however, changing my code to get and set the values in the "record" vs the store did not help. When I tab over to the factor field, it is immediately brought up in edit mode (as it should) but it has the old value, not the new value. I hit reply before having my code ready to post. Give me a few because I have to make it generic as to not give away any proprietary information. (sorry about that).

Also, I see in fiddler you are using ExtJS 4.2.1. I am on ExtJS 4.0.7. I will try to update a fiddler sample with my 4.0.7 code. As a side note, I cannot get any code to work in Fiddler using ExtJS 4.0.7.

rkanemeier
19 Sep 2013, 1:16 PM
exitdissolve,

so I was able to locate some additional ways to determine what the actual value of the editable cell is that is not having it's value updated.



{
xtype: 'numbercolumn',
format: '0.00',
align: 'right',
header: "<b>Margin 1 (%)</b>",
sortable: false,
draggable: false,
width: 100,
dataIndex: 'MARGIN1',
editor: {
xtype: 'numberfield',
hideTrigger: true,
keyNavEnabled: false,
mouseWheelEnabled: false,
decimalPrecision: 2,
minValue: 0.00,
maxValue: 99.99,
listeners: {
focus: function(t,e) {
console.log(t);
}
}
}



When I tab from the price field to the margin field, the focus listener fires. the result of of the log is 70 (the old value).

Also, I put a beforeedit listener in the cell edit plugin, and indeed, when you log the "e", you can see the "old" value. So it seems, that in ExtJS 4.0.7, that the cell editor plugin is not changed when you perform a record.set on the grid record or the store record.

So why does the grid record, and the store record show the updated value while the focus listener shows the old value? Anyone have any suggestions on how to update the editor cell value?

existdissolve
20 Sep 2013, 5:39 AM
Can you post the relevant parts of your code (e.g., the grid config, the columns involved, etc.)? It's difficult to help debug in just conceptual terms.

Thanks!

rkanemeier
20 Sep 2013, 9:50 AM
existdissolve,

I appreciate your tenacity on this. I modified the code so that it does not give any proprietary info. I excluded my grid filters in a top toolbar because the code would have become too bloated.

The Grid Store:





Ext.define('priceModel', {
extend: 'Ext.data.Model',
fields: [
{name: 'PART'},
{name: 'PRICE1'},
{name: 'MARGIN1'},
{name: 'PRICE2'},
{name: 'MARGIN2'},
{name: 'PRICE3'},
{name: 'MARGIN3'},
{name: 'PRICE4'},
{name: 'MARGIN4'},
{name: 'COST'}
]
});


var priceDataStore = Ext.create('Ext.data.Store', {
model: 'priceModel',
autoLoad : true,
remoteSort: true,
pageSize: 25,
sortInfo: {
field: 'PART',
direction: 'ASC'
},
proxy : {
type : 'ajax',
url : 'vvcall.pgm',
extraParams: {
pgm: 'MYPGM',
action: 'getPriceRecords'
},
reader : {
type : 'json',
root : 'pricegrid',
totalProperty : 'totalCount'
}
},
listeners : {
beforeload : function () {

// load any extra parameters to filter data
this.getProxy().extraParams.inPARTNO = priceGrid.down('#filterPart').getValue();

// special logic for sorting
if ( this.sorters && this.sorters.items && this.sorters.items.length > 0 ) {
this.getProxy().extraParams.dir = this.sorters.items[0].direction;
this.getProxy().extraParams.sort = this.sorters.items[0].property;
}
},


load : function (store, records, successful) {


// initialize updatedRecords array
updatedRecords = [];

// disable save button
priceGrid.down('[text=Save]').disable();

// updated "calculated" fields (margins)
for (var i = 0; i < store.data.items.length; i++) {

var price,
cost,
margin;

for (var x = 1; x <= 4; x++) {

price = store.getAt(i).get('PRICE' + x);
cost = store.getAt(i).get('COST');

if (price !== 0) {
margin = Ext.util.Format.number(((price - cost) / price) * 100, '0.00');
store.getAt(i).set('MARGIN' + x, margin);
store.getAt(i).commit();


}
}
}
}
}
});


The grid:





// this override preserves the numberfield decimal precision
Ext.override(Ext.form.NumberField, {
setValue : function(v){
v = typeof v == 'number' ? v : String(v).replace(this.decimalSeparator, ".");
v = isNaN(v) ? '' : String(v).replace(".", this.decimalSeparator);
return Ext.form.NumberField.superclass.setValue.call(this, v);
},
fixPrecision : function(value){
var nan = isNaN(value);
if(!this.allowDecimals || this.decimalPrecision == -1 || nan || !value){
return nan ? '' : value;
}
return parseFloat(value).toFixed(this.decimalPrecision);
}
});


// reset cell
var applyBox = function(value, metadata) {
//used to just be .attr in 3.x
metadata.tdAttr='style="border-style:solid; border-color:#c0c0c0; border-width:1px;"';
return Ext.util.Format.number(value, '0.00');
};

// grid definition
var priceGrid = Ext.create('Ext.grid.Panel', {
region: 'center',
title: 'Price Maintenance',
store: priceDataStore,
autoScroll: true,
loadMask: true,
stripeRows: true,
enableKeyEvents: true,
plugins: [
Ext.create('Ext.grid.plugin.CellEditing', {
clicksToEdit: 1,
listeners: {
beforeedit: function(e, eOpts) {
if (e.field === 'MARGIN1') {
console.log(e.record);
return false;
}
}
}
})
],
columns: [{
header: "<b>Part Number</b>",
sortable: true,
draggable: false,
width: 200,
dataIndex: 'PART'
},{
xtype: 'numbercolumn',
format: '0.00',
align: 'right',
header: "<b>Price 1</b>",
sortable: false,
draggable: false,
width: 90,
dataIndex: 'PRICE1',
editor: {
xtype: 'numberfield',
hideTrigger: true,
keyNavEnabled: false,
mouseWheelEnabled: false,
decimalPrecision: 2,
minValue: 0.00,
maxValue: 999999.99
}
},{
xtype: 'numbercolumn',
format: '0.00',
align: 'right',
header: "<b>Margin 1 (%)</b>",
sortable: false,
draggable: false,
width: 100,
dataIndex: 'MARGIN1',
editor: {
xtype: 'numberfield',
hideTrigger: true,
keyNavEnabled: false,
mouseWheelEnabled: false,
decimalPrecision: 2,
minValue: 0.00,
maxValue: 99.99
}
},{
xtype: 'numbercolumn',
format: '0.00',
align: 'right',
header: "<b>Price 2</b>",
sortable: false,
draggable: false,
width: 90,
dataIndex: 'PRICE2',
editor: {
xtype: 'numberfield',
hideTrigger: true,
keyNavEnabled: false,
mouseWheelEnabled: false,
decimalPrecision: 2,
minValue: 0.00,
maxValue: 999999.99
},
renderer: applyBox
},{
xtype: 'numbercolumn',
format: '0.00',
align: 'right',
header: "<b>Margin 2 (%)</b>",
sortable: false,
draggable: false,
width: 100,
dataIndex: 'MARGIN2',
editor: {
xtype: 'numberfield',
hideTrigger: true,
keyNavEnabled: false,
mouseWheelEnabled: false,
decimalPrecision: 2,
minValue: 0.00,
maxValue: 99.99
},
renderer: applyBox
},{
xtype: 'numbercolumn',
format: '0.00',
align: 'right',
header: "<b>Price 3</b>",
sortable: false,
draggable: false,
width: 100,
dataIndex: 'PRICE3',
editor: {
xtype: 'numberfield',
hideTrigger: true,
keyNavEnabled: false,
mouseWheelEnabled: false,
decimalPrecision: 2,
minValue: 0.00,
maxValue: 999999.99
},
renderer: applyBox
},{
xtype: 'numbercolumn',
format: '0.00',
align: 'right',
header: "<b>Margin 3 (%)</b>",
sortable: false,
draggable: false,
width: 100,
dataIndex: 'MARGIN3',
editor: {
xtype: 'numberfield',
hideTrigger: true,
keyNavEnabled: false,
mouseWheelEnabled: false,
decimalPrecision: 2,
minValue: 0.00,
maxValue: 99.99
},
renderer: applyBox
},{
xtype: 'numbercolumn',
format: '0.00',
align: 'right',
header: "<b>Price 4</b>",
sortable: false,
draggable: false,
width: 100,
dataIndex: 'PRICE4',
editor: {
xtype: 'numberfield',
hideTrigger: true,
keyNavEnabled: false,
mouseWheelEnabled: false,
decimalPrecision: 2,
minValue: 0.00,
maxValue: 999999.99
},
renderer: applyBox
},{
xtype: 'numbercolumn',
format: '0.00',
align: 'right',
header: "<b>Margin 4 (%)</b>",
sortable: false,
draggable: false,
width: 100,
dataIndex: 'MARGIN4',
editor: {
xtype: 'numberfield',
hideTrigger: true,
keyNavEnabled: false,
mouseWheelEnabled: false,
decimalPrecision: 2,
minValue: 0.00,
maxValue: 99.99
},
renderer: applyBox
}],

listeners: {

edit: function(editor, e) {

// if the record is dirty, enable the Save button
if (e.record.dirty) {

priceGrid.down('[text=Save]').enable();


var ovalue,
nvalue,
fieldName;

ovalue = Ext.util.Format.number(e.originalValue, '0.00');
nvalue = Ext.util.Format.number(e.value, '0.00');

if (ovalue !== nvalue) {


var gridCost,
gridPrice,
gridMargin,
gridField,
gridFieldNbr,
gridFieldName;

gridFieldName = e.field;
gridCost = e.record.get('COST');
console.log('the field being changed is',gridFieldName);

// if margin was changed, update price
if (gridFieldName.indexOf('MARGIN') > - 1) {
gridField = Ext.util.Format.substr(e.field, 0, 6);
gridFieldNbr = Ext.util.Format.substr(e.field, 6, 1);
console.log(gridField+gridFieldNbr, 'has changed');
gridMargin = nvalue;
gridPrice = Ext.util.Format.number( gridCost / (1 - (gridMargin / 100)), '0.00');
e.record.set('PRICE' + gridFieldNbr, gridPrice);
}

// if price was changed, update margin
if (gridFieldName.indexOf('PRICE') > - 1) {
gridField = Ext.util.Format.substr(e.field, 0, 8);
gridFieldNbr = Ext.util.Format.substr(e.field, 8, 1);
console.log(gridField+gridFieldNbr, 'has changed');
gridPrice = nvalue;
gridMargin = Ext.util.Format.number( ((gridPrice - gridCost)/gridPrice) * 100, '0.00');
console.log('MARGIN' + gridFieldNbr + ' is being updated with value ' + gridMargin );
e.record.set('MARGIN' + gridFieldNbr, gridMargin);

}
}
}
}
},
{
xtype: 'pagingtoolbar',
store: priceDataStore, // same store GridPanel is using
pageSize: 25,
dock: 'bottom',
displayInfo: true
}]
});

rkanemeier
21 Sep 2013, 8:44 AM
I just noticed that your example doesn't work either. When you tab from price to factor, it does not update the editable cell with the new value.

existdissolve
21 Sep 2013, 9:13 AM
Yeah, I know. I reverted it to use 4.0.7, and it appears that this is a bug in that version. I'll keep playing around to find an override.

rkanemeier
21 Sep 2013, 9:27 AM
So, it took me about 8 hours of Googling and decyphering other people's needs, but I think I found a workaround.

I created a button in my toolbar. The button changes text when it's pressed: price or factor.



{
xtype: 'button',
itemId: 'editToggle',
id: 'editToggle',
name: 'editToggle',
text: 'Edit Margin',
border: 1,
enableToggle: true,
listeners: {
click: function(button, e, eOpts) {
console.log('button pressed');
if (button.pressed) {
// button.pressed = true;
button.setText('Edit Price');
} else {
// button.pressed = false;
button.setText('Edit Margin');
}
}
}
}


Then, I used the beforeedit listener on my celledit plugin, and depending on the state of the button, i cancelled the edit, skipped to the next column (startEditByPosition) then returned false.



// cell editing plugin
var cellEditing = Ext.create('Ext.grid.plugin.CellEditing', {
clicksToEdit: 1,
listeners: {
beforeedit: function(e, eOpts) {

var cellFieldName, skipToRow, skipToCol;
cellFieldName = e.field;
skipToRow = e.rowIdx;
skipToCol = e.colIdx + 1;

if (cellFieldName.indexOf('MARGIN') > - 1) {
if (!priceGrid.down('#editToggle').pressed) {
this.cancelEdit();
this.startEditByPosition({
row: skipToRow,
column: skipToCol
});

return false;
}
}

if (cellFieldName.indexOf('MSIPRICE') > - 1) {
if (priceGrid.down('#editToggle').pressed) {
this.cancelEdit();
this.startEditByPosition({
row: skipToRow,
column: skipToCol
});
return false;
}
}
}
}
});


I would have posted this code in fiddle but I cannot seem to get it to work with ExtJS 4.0.7.

existdissolve
21 Sep 2013, 9:34 AM
Try this override. It seems to be working in 4.0.7 (I just sniped it from 4.1.0):


Ext.override(Ext.grid.plugin.CellEditing, {
startEdit: function(record, columnHeader) {
var me = this,
context = me.getEditingContext(record, columnHeader),
value, ed;


// Complete the edit now, before getting the editor's target
// cell DOM element. Completing the edit causes a row refresh.
// Also allows any post-edit events to take effect before continuing
me.completeEdit();


// Cancel editing if EditingContext could not be found (possibly because record has been deleted by an intervening listener), or if the grid view is not currently visible
if (!context || !me.grid.view.isVisible(true)) {
return false;
}


record = context.record;
columnHeader = context.column;


// See if the field is editable for the requested record
if (columnHeader && !columnHeader.getEditor(record)) {
return false;
}


value = record.get(columnHeader.dataIndex);
context.originalValue = context.value = value;
if (me.beforeEdit(context) === false || me.fireEvent('beforeedit', me, context) === false || context.cancel) {
return false;
}


ed = me.getEditor(record, columnHeader);


// Whether we are going to edit or not, ensure the edit cell is scrolled into view
//me.grid.view.cancelFocus();
me.view.focusCell({
row: context.row,
column: context.colIdx
});
if (ed) {
me.context = context;
me.setActiveEditor(ed);
me.setActiveRecord(record);
me.setActiveColumn(columnHeader);


// Defer, so we have some time between view scroll to sync up the editor
me.editTask.delay(15, ed.startEdit, ed, [me.getCell(record, columnHeader), value]);
me.editing = true;
me.scroll = me.view.el.getScroll();
return true;
}
return false;
}
});

je

rkanemeier
21 Sep 2013, 9:40 AM
Incredible solution! You should have a paypal account. I'd pay $10 for this.

That being said, I removed my code (button and skip column) and added your override. It worked.

existdissolve
21 Sep 2013, 10:16 AM
Incredible solution! You should have a paypal account. I'd pay $10 for this.

That being said, I removed my code (button and skip column) and added your override. It worked.

Awesome, glad it's working!