Custom XML binding – Grid

[kakaoParticipant]

Greetings, jqWidgets gurus and masters!
There is some project & I would like to use jqWidgets Grid.
I’m new in jqWidgets, and I have stopped at some problems about binding from custom XML’s.
There is some complex example XML (I need edit it before posting, so there may be some mistakes, but I hope You get idea):

<?xml version="1.0" encoding="UTF-8"?>
<data>
<filters>
<amount>
<am/>
<am>Actuall</am>
<am>Not actuall</am>
<am>Total</am>
<am>Old</am>
</amount>
<payment>
<py/>
<py>(01220)</py>
<py>(02220)</py>
<py>(04710)</py>
<py>(09910)</py>
</payment>
</filters>
<py c="(01220)"  currency="USD" ><d c="31.12.2013"><o>
<ca>
<dc>
<am c="Total"><s>-1596630,84</s>
<ff>1038154,70</ff>
<la>558476,14</la>
</am>
<am c="Actuall"><s>-1596630,84</s>
<ff>103854,70</ff>
<la>558476,14</la>
</am>
<am c="Not actuall"><s>0,00</s>
<ff>0,00</ff>
<la>0,00</la>
</am>
<am c="Old"><s>0,00</s>
<ff>0,00</ff>
<la>0,00</la>
</am>
</dc>
</ca>
</o>
</d>
<d c="31.01.2014"><o>
<ca>
<dc>
<am c="Total"><s>-161222,24</s>
<ff>103854,70</ff>
<la>574567,54</la>
<laa>16091,40</laa>
<ds>31</ds>
</am>
<am c="Actuall"><s>-1612722,24</s>
<ff>1038154,70</ff>
<la>574567,54</la>
<laa>16091,40</laa>
<ds>31</ds>
</am>
<am c="Not actuall"><s>0,00</s>
<ff>0,00</ff>
<la>0,00</la>
<ds>31</ds>
</am>
<am c="Old"><s>0,00</s>
<ffff>0,00</ff>
<la>0,00</la>
<laa>0,00</laa>
<ds>31</ds>
</am>
</dc>
</ca>
</o>
</d>
<togeth>
<ca>0,00</ca>
<dc>0,00</dc>
<laa>16091,40</laa>
</togeth>
</py>
<py c="(02220)"  currency="USD" ><d c="31.12.2013"><o>
<ca>
<dc>
<am c="Total"><s>-3063,14</s>
<ff>2221,25</ff>
<la>841,89</la>
</am>
<am c="Actuall"><s>-3063,14</s>
<ff>2221,25</ff>
<la>841,89</la>
</am>
<am c="Old"><s>0,00</s>
<ff>0,00</ff>
<la>0,00</la>
</am>
</dc>
</ca>
</o>
</d>
<d c="31.01.2014"><o>
<ca>
<dc>
<am c="Total"><s>-3097,57</s>
<ff>2221,25</ff>
<la>876,32</la>
<laa>34,43</laa>
<ds>31</ds>
</am>
<am c="Actuall"><s>-3097,57</s>
<ff>2221,25</ff>
<la>876,32</la>
<laa>34,43</laa>
<ds>31</ds>
</am>
<am c="Old"><s>0,00</s>
<ff>0,00</ff>
<la>0,00</la>
<laa>0,00</laa>
<ds>31</ds>
</am>
</dc>
</ca>
</o>
</d>
<togeth>
<ca>0,00</ca>
<dc>0,00</dc>
<laa>34,43</laa>
</togeth>
</py>
</data>

… and the result should be like this:

Also might be some deviations: Currency code: … & Payment type: … rows could be removed, if merged rows isn’t supportded.
SO, questions are:
1.Q How to map for results like above?
Best result what I could get is make record from am tag, but than dates etc. aren’t showing.
If record tag pointed to py tag, then tag values pulling together.
2.Q Is it possible to make merged columns like in result above for Currency code: … at every py tag start?
3.Q How to put in rows predefined texts, like ‘Together:’ ?

And another lighter source XML example for binding:

<data>
<transactions>
<transaction>
<from>2013-01-01</from>
<till>2013-01-31</till>
<type/>
<income>1800.00</income>
<outlay>0.00</izdev>
<dek>270.00</dek>
<currency>EUR</currency>
</transaction>
<transaction>
<from>2013-10-29</from>
<till>2013-10-29</till>
<type>A</type>
<income>10.00</income>
<outlay>0.00</outlay>
<dek>0.00</dek>
<currency>EUR</currency>
</transaction>
<transaction>
<from>2013-10-31</from>
<till>2013-10-31</till>
<type>N</type>
<income>50.00</income>
<outlay>500.00</outlay>
<dek>0.00</dek>
<currency>EUR</currency>
</transaction>
<transaction>
<from>2013-02-02</from>
<till>2013-02-29</till>
<type/>
<income>1200.00</income>
<outlay>500.00</outlay>
<dek>150.00</dek>
<currency>LVL</currency>
</transaction>
<transaction>
<from>2013-03-03</from>
<till>2013-03-29</till>
<type/>
<income>1300.00</income>
<outlay>500.00</outlay>
<dek>160.00</dek>
<currency>EUR</currency>
</transaction>
<transaction>
<from>2013-04-04</from>
<till>2013-04-29</till>
<type/>
<income>1400.00</income>
<outlay>600.00</outlay>
<dek>160.00</dek>
<currency>EUR</currency>
</transaction>
<transaction>
<from>2013-05-03</from>
<till>2013-05-29</till>
<type/>
<income>1500.00</income>
<outlay>600.00</outlay>
<dek>160.00</dek>
<currency>EUR</currency>
</transaction>
</transactions>
<together>
<income>1860.00</income>
<outlay>500.00</outlay>
<ap_income>1360.00</ap_income>
<dek>270.00</dek>
<ap_val>204.00</ap_val>
<up_val>66.00</up_val>
<currency>EUR</currency>
</together>
</data>

… and result from begining of XML:

Questions:
4.Q How to map to get result like above?
5.Q How to make last summary (together) row records in bold? For this, statusbar couldn’t be the best way…
(6.Q like 3rd) How to put in rows predefined texts, like ‘Together:’ ?
And last question about something else.
7.Q Does there are some way to save reordered columns sequence for users in cache or somewhere else, so user haven’t change order again and again. I’n not explore it in forum jet, bet maybe You can give the best and the powerful solution right now.
For now, this is it. I look forward to Your responses & continue to look for sollutions.
If something isn’t clear, I will answer! 🙂

[DimitarParticipant]

Hello kakao,

1) The provided XML data is not suitable for mapping. Please check out the Data Sources tutorial for some examples of XML data and its mapping.

2) Merged columns are not supported. Please consider using grouping for Currency code and Payment type.

3), 5) and 6) For Together, you can use aggregates. Note the Custom Aggregates and Aggregates Renderer functionalities, too.

4) This XML is suitable for mapping. Here is how to map it:

var source =
{
    datatype: "xml",
    datafields: [
        { name: 'from', map: 'from', type: 'date' },
        { name: 'till', map: 'till', type: 'date' },
        { name: 'type', map: 'type', type: 'string' },
        { name: 'income', map: 'income', type: 'number' },
        { name: 'outlay', map: 'outlay', type: 'number' },
        { name: 'dek', map: 'dek', type: 'number' },
        { name: 'currency', map: 'currency', type: 'string' }
    ],
    root: "transactions",
    record: "transaction",
    url: url
};

7) The grid supports State Maintenance.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[kakaoParticipant]

BIG thanks to you, Dimitar!
I will consider grouping according to what result I will get from exported grid to Excel file.
About aggregates, is it possible to bind from xml in aggregate cells that <together> values? Auto sums etc. might be risky for this project.
For 2nd example I have realised replied sollution erlier, but result is just for transactions or <together> not for both.
My earlier idea for 2nd example failed – make 1 data adapter who binds to grid transactions and then another data adapter who bind in <together> records, but seems like jqxGrid don’t support that or there is some after bind statement who binds again at same grid. Any ideas?

[DimitarParticipant]

Hi kakao,

The grid can be bound to only one source.

Note that aggregates sum the values of cells correctly. You can put your own custom values there, however, by using custom aggregates/aggregates renderer. An example can be seen in the forum topic Grid aggregates accessing other column summary data. You can load the <together> data from the XML in another data adapter from which to get the required values.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[kakaoParticipant]

Im tried & looked through forum how to bind in each (almost each) column aggregate field some other dataAdapter values (not from Grids dataAdapter, from different xml/json), but with no success.
Is there some good example, even small?

[DimitarParticipant]

Hi kakao,

Here is an example. The output aggregate is 101.

<!DOCTYPE html>
<html lang="en">
<head>
    <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
    <script type="text/javascript" src="../../scripts/jquery-1.10.2.min.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.aggregates.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxcheckbox.js"></script>
    <script type="text/javascript" src="../../scripts/demos.js"></script>
    <script type="text/javascript" src="generatedata.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            // prepare the data
            var data = generatedata(200);

            var source =
            {
                localdata: data,
                datatype: "array",
                datafields:
                [
                    { name: 'firstname', type: 'string' },
                    { name: 'lastname', type: 'string' },
                    { name: 'productname', type: 'string' },
                    { name: 'quantity', type: 'number' },
                    { name: 'price', type: 'number' }
                ],
                updaterow: function (rowid, rowdata, commit) {
                    // synchronize with the server - send update command   
                    commit(true);
                }
            };

            var dataAdapter = new $.jqx.dataAdapter(source);

            // initialize jqxGrid
            $("#jqxgrid").jqxGrid(
            {
                width: 670,
                source: dataAdapter,
                showstatusbar: true,
                statusbarheight: 25,
                altrows: true,
                showaggregates: true,
                columns: [
                    { text: 'First Name', columntype: 'textbox', datafield: 'firstname', width: 90 },
                    { text: 'Last Name', datafield: 'lastname', columntype: 'textbox', width: 90 },
                    { text: 'Product', datafield: 'productname', width: 170, aggregates: [{ 'Total':
                        function (aggregatedValue, currentValue, column, record) {
                            return '<span id="totalAggregates"></span>';
                        }
                    }], aggregatesrenderer: function (aggregates, column, element) {
                        return aggregates.Total;
                    }
                    },
                    { text: 'Quantity', datafield: 'quantity', width: 100, cellsalign: 'right', cellsformat: 'n2', aggregates: ["sum"] },
                    { text: 'Price', datafield: 'price', cellsalign: 'right', cellsformat: 'c2', aggregates: ["sum"] }
                ]
            });

            var aggregatesJSON = [
                {
                    "Aggregate": 101
                },
                {
                    "Aggregate": 102
                },
                {
                    "Aggregate": 103
                }
            ];

            var aggrSource =
            {
                localdata: aggregatesJSON,
                datatype: "array",
                datafields:
                [
                    { name: 'Aggregate', type: 'nember' }
                ]
            };

            var aggrDataAdapter = new $.jqx.dataAdapter(aggrSource, { autoBind: true, loadComplete: function (data) {
                $("#totalAggregates").text(data[0].Aggregate);
            }
            });
        });
    </script>
</head>
<body class='default'>
    <div id='jqxWidget'>
        <div id="jqxgrid">
        </div>
    </div>
</body>
</html>

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[kakaoParticipant]

I like your solution, Dimitar!
It’s working!
But until I change source to json datatype and take it from *.txt file, aggregates dissapear.That’s interesting!

[kakaoParticipant]

Dissapear just custom aggregates, not all (sum’s stay).

[DimitarParticipant]

Hi kakao,

These aggregates are shown when the data source is loaded in the data adapter. Until then, the aggregates cell will remain blank.

If we change the source definition to:

var aggrSource =
{
    url: "aggregates.txt",
    datatype: "json",
    datafields:
    [
        { name: 'Aggregate', type: 'nember' }
    ]
};

and have the data in the file aggregates.txt:

[
    {
        "Aggregate": 101
    },
    {
        "Aggregate": 102
    },
    {
        "Aggregate": 103
    }
]

everything works fine as with local data.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[kakaoParticipant]

Hello!
jqWidgets are working well for my project!
Maybe if all will work great, license purchase will be considered and things will go up, but before that I have some incomplete puzzles. Everything else is ok, but major problem is with binding.
In project are some parameters which are sent to server (using processdata: function (data) { … } ), but as You all know, 1 request to server is better than two or more.
1) Is there some way to map aaaa records and bbbb records in one source, that let bind all below values? Or some json structure edit for below json?
2) What is the best way to start grid binding (so sending server request) by button click not by $(document).ready( … ) ?
3) These aggregate values (except FIRST) in grid are showing until browsers window size aren’t changed, then they dissapear. Don’t know why.
There is some simple my structure examples:
– json (demofile.txt):

{
  "roote": {
    "maindata": {
      "some": "USER LARIS",
      "other": "25554197",
      "smth": "2013"
    },
    "aaaa": [
      {
        "a": "2013-01-01",
        "b": "2013-01-21",
        "c": "",
        "d": "1000.00",
        "e": "0.00"
      },
      {
        "a": "2012-01-01",
        "b": "2012-01-11",
        "c": "asdf",
        "d": "1100.00",
        "e": "0.00"
      },
      {
        "a": "2014-01-01",
        "b": "2014-01-01",
        "c": "qwer",
        "d": "1200.00",
        "e": "10.00"
      }
    ],
    "bbbb": {
      "b": "500.00",
      "c": "1360.00",
      "d": "270.00",
      "e": "204.00"
    }
  }
}

– code:

        $(document).ready(function () {
            var source = {
                datatype: "json",
                datafields: [
                // without any field declaration - works for aaaa if root : roote>aaaa

                // works for aaaa if root : roote>aaaa
                    {
                        name: 'a',
                        type: 'date',
                        format: 'dd.MM.yyyy'
                    }, {
                        name: 'b',
                        type: 'date',
                        format: 'dd.MM.yyyy'
                    }, {
                        name: 'c',
                        type: 'string'
                    }, {
                        name: 'd',
                        type: 'string'
                    }, {
                        name: 'e',
                        type: 'string'
                    }

                    // works if root : roote
                    //{ name: 'b', type: 'date', format: 'dd.MM.yyyy', map:'bbbb>b' },
                    //{ name: 'c', type: 'string', map:'bbbb>b' },
                    //{ name: 'd', type: 'string', map:'bbbb>c' },
                    //{ name: 'e', type: 'string', map:'bbbb>d' },
                    //{ name: 'nodokl_dekl', type: 'string', map:'bbbb>e' }

                    // didn't work if root : roote and root : roote>aaaa
                    //{ name: 'a', type: 'date', format: 'dd.MM.yyyy', map:'aaaa>a' },
                    //{ name: 'b', type: 'date', format: 'dd.MM.yyyy', map:'aaaa>b' },
                    //{ name: 'c', type: 'string', map:'aaaa>c' },
                    //{ name: 'd', type: 'string', map:'aaaa>d' },
                    //{ name: 'e', type: 'string', map:'aaaa>e' }

                ],
                url: 'demofile.txt',
                //root: 'roote'
                root: 'roote>aaaa'
            };

            var Adapter = new $.jqx.dataAdapter(source);

            $("#jqxgrid").jqxGrid({
                theme: 'energyblue',
                width: '100%',
                columnsheight: 120,
                source: Adapter,
                autorowheight: true,
                columnsresize: true,
                autoheight: true,
                showstatusbar: true,
                showaggregates: true,

                ready: function () {
                    var aggrDataAdapter = new $.jqx.dataAdapter(sourcis, {
                        autoBind: true,
                        loadComplete: function (data) {
                            var kopa;
                            var records = aggrDataAdapter.records;
                            var record = records[0];
                            var bb = record.b;
                            var cc = record.c;
                            var dd = record.d;
                            var ee = record.e;
                            $("#text2").text(bb);
                            $("#text3").text(cc);
                            $("#text4").text(dd);
                            $("#text5").text(ee);
                        }
                    });
                },
                columns: [{
                        text: 'FIRST',
                        datafield: 'a',
                        width: '20%',
                        aggregates: [{
                            '<b>Some text</b>': function (aggregatedValue, currentValue, column, record) {
                                return "";
                            }
                        }]
                    },
                    {
                        text: 'SECOND',
                        datafield: 'b',
                        width: '20%',
                        aggregates: [{
                            '': function (aggregatedValue, currentValue, column, record) {
                                var aggregatedValue;
                                return aggregatedValue;
                            }
                        }],
                        aggregatesrenderer: function (aggregates) {
                            var aggregatedValue = '<div id="text2" style="float: right; margin: 4px; overflow: hidden;">' + '</div>';
                            return aggregatedValue;
                        }
                    }, {
                        text: 'THIRD',
                        datafield: 'c',
                        width: '20%',
                        aggregates: [{
                            '': function (aggregatedValue, currentValue, column, record) {
                                var aggregatedValue;
                                return aggregatedValue;
                            }
                        }],
                        aggregatesrenderer: function (aggregates) {
                            var aggregatedValue = '<div id="text3" style="float: right; margin: 4px; overflow: hidden;">' + '</div>';
                            return aggregatedValue;
                        }
                    }, {
                        text: 'FOURTH',
                        datafield: 'd',
                        width: '20%',
                        aggregates: [{
                            '': function (aggregatedValue, currentValue, column, record) {
                                var aggregatedValue;
                                return aggregatedValue;
                            }
                        }],
                        aggregatesrenderer: function (aggregates) {
                            var aggregatedValue = '<div id="text4" style="float: right; margin: 4px; overflow: hidden;">' + '</div>';
                            return aggregatedValue;
                        }
                    },
                    {
                        text: 'FIFTH',
                        datafield: 'e',
                        width: '20%',
                        aggregates: [{
                            '': function (aggregatedValue, currentValue, column, record) {
                                var aggregatedValue;
                                return aggregatedValue;
                            }
                        }],
                        aggregatesrenderer: function (aggregates) {
                            var aggregatedValue = '<div id="text5" style="float: right; margin: 4px; overflow: hidden;">' + '</div>';
                            return aggregatedValue;
                        }
                    }
                ]
            });

            var sourcis = {
                datatype: "json",
                url: 'demofile.txt',
                root: 'roote>bbbb',
                datafields: [{
                    name: 'b',
                    type: 'date'
                }, {
                    name: 'c',
                    type: 'string'
                }, {
                    name: 'd',
                    type: 'string'
                }, {
                    name: 'e',
                    type: 'string'
                }]
            };
        });

[DimitarParticipant]

Hi kakao,

1) If you use the following source, after the load has completed you will have all your JSON data loaded in loadedData.

var source =
{
    datatype: "json",
    root: "roote",
    url: url
};
var loadedData;
var dataAdapter = new $.jqx.dataAdapter(source, { autoBind: true, loadComplete: function (data) {
    loadedData = data;
}
});

2) This is achieved in the demo Load from Table. You may also check out Refresh Data.

3) To see how to correctly use aggregatesrenderer, please check out the demo on the matter.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[kakaoParticipant]

As always, best answers!
2nd and 3rd works great, but I could not make out with 1st – loadedData.
Can You give some example code for binding data to grid?

[DimitarParticipant]

Hi kakao,

Examples of grid data binding can be found in the guide Grid Data Sources.

In the case of your data, however, my suggestion was about loading it and not binding the grid to it. Once you have all the data loaded in the loadedData variable, you can re-format it and, according to your requirements, bind it to a grid through another data adapter.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[kakaoParticipant]

Yes, that is what I need, code example to load in the loadedData, re-formating (and binding)!
How I understand and tried – loaded data in the loadedData like Your example (#52485), then I created new source (with my datafields, root and datatype: “json”, localdata: loadedData, not sure if I used right last two), then I created new dataAdapter with created source and at last I defined source with created dataAdapter to grid. And i get no data. 🙁

[DimitarParticipant]

Hi kakao,

Your approach does not work because the data needs to be re-arranged. We may be able to help you, but we would need to know what columns would your grid have related to the sample data you provided (a mock-up image will do fine). Please also re-post the source with more descriptive names for the keys.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[Author]

Posts