PDA

View Full Version : Grid formulas

chrisgotter
19 Sep 2016, 5:59 PM
ok, I am writing a grid which is essentially a timeline ex:

activity

started
ended
duration

get up/eat/get ready for work
07:00
07:30
00:30

go to work
07:30
08:00
00:30

work
08:00
16:00
08:00

go home
16:00
16:30
00:30

eat
16:30
17:15
00:45

watch tv
17:15
23:00
05:45

sleep
23:00
07:00
08:00

EXCEL format:

activity

started
ended
duration

get up/eat/get ready for work
07:00
=B3

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

go to work
07:30
=B4

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

work
08:00
=B5

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

go home
16:00
=B6

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

eat
16:30
=B7

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

watch tv
17:15
=B8

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

sleep
23:00
=B2

=C:C+IF(B:B >C:C, DAYS(1,0), 0)-B:B

looking past how sad my personal life is, does anyone have any ideas for how to make this happen?
As you can see the only values which are actually important are the "started times".
Ideally, I would like my store to just be a list of those (the activities are arbitrary in reality I just included them for context).
I implemented a system of 2way data-binding myself which syncs up the values on a single row, but I still need to ensure that C2 = B3 and that SUM(d:d) = 24:00

// Handler receives array of itmes and selector in parentheses
Ext.ComponentQuery.pseudos.nth = function(components, selector) {
if (selector.indexOf(',') == -1) {
return components[selector - 1];

} else if (selector === '') {
return components;
} else {
var retain = selector.split(',');
var result = [];
for (var i of retain) {
result.append(components[i - 1])
}
return result;
}
}
var startChange = function(field) {
//if (field.hasFocus) {
var row = field.ownerCt;
var start = row.down('field:nth(2)');
var end = row.down('field:nth(3)');
var diff = row.down('field:nth(4)');
end.setValue(start.getValue() + diff.getValue());
//}
}

var endChange = function(field) {
//if (field.hasFocus) {
var row = field.ownerCt;
var start = row.down('field:nth(2)');
var end = row.down('field:nth(3)');
var diff = row.down('field:nth(4)');
diff.setValue(end.getValue() - start.getValue());
//}
}

var diffChange = startChange;

Ext.application({
name: 'Fiddle',

launch: function() {
Ext.create('Ext.data.Store', {
storeId: 'testStore',
fields: ['name', 'start', 'end', 'diff'],
data: [{
"name": "Lisa",
"start": 2,
"end": 23,
"diff": 21
}, {
"name": "Bart",
"start": 1,
"end": 5,
"diff": 4
}, {
"name": "Homer",
"start": 11,
"end": 16,
"diff": 5
}, {
"name": "Marge",
"start": 44,
"end": 49,
"diff": 5
}]
});

var rowEditing = Ext.create('Ext.grid.plugin.RowEditing', {
clicksToEdit: 1
});

Ext.create('Ext.grid.Panel', {

title: 'Test',
store: Ext.data.StoreManager.lookup('testStore'),

columns: [{
text: 'Name',
dataIndex: 'name',
flex: 1,
}, {
text: 'Start',
dataIndex: 'start',

editor: {
xtype: 'numberfield',
listeners: {
change: startChange
}
},
flex: 1
}, {
text: 'End',
dataIndex: 'end',
editor: {
xtype: 'numberfield',
listeners: {
change: endChange
}
},
flex: 1
}, {
text: 'Difference',
dataIndex: 'diff',
editor: {
xtype: 'numberfield',
listeners: {
change: diffChange
}
},
flex: 1
}],
selType: 'rowmodel',
plugins: [rowEditing],
width: 400,
renderTo: Ext.getBody()
})
}
});

Gary Schlosberg
20 Sep 2016, 7:01 AM
I added your code to this Fiddle.
https://fiddle.sencha.com/#fiddle/1h2i

Can you elaborate on what you are looking to achieve?

chrisgotter
20 Sep 2016, 11:51 AM
I am not actually sure how to elaborate further, it is basically a timeline, I fixed the tables so they should not look like a jumbled mess anymore

chrisgotter
20 Sep 2016, 11:56 AM
Basically it is the excel code i showed, only you should be able to edit any of the fields (except for the activities and the relationships should be maintained.
I have the start-end-diff relationships for a single row working fine, it is the relationships between rows that have me stuck