[Solved] Conditional Formatting with Sorting Question

[Randy]

Hi, I have attached an image and will provide my current code at the end.

I have a grid with 2 columns, withdrawals & deposits.
When withdraw amount > 0, then display as currency format with dollar sign, otherwise show empty string. (image label "1")
It is the same for deposit amount. (image label "1")

I can do this with conditional formatting, although I'm losing my currency formatting. (image label "3": format: "${0:N2}")

The biggest issue is that with conditional formatting, when I click on the row headers to sort, the columns are being sorted as strings instead of as numbers. (image label "2")

Is there some syntax that will solve all my issues?
If not, is there some trick where I can keep everything numeric so I can sort, but if the amount is not > 0, then hide the "$0.00" by making the font transparent?

Thanks,

Randy

HTML

<div id="gridHubSpendAccountActivity" style="height:100%;" data-win-control="Telerik.UI.RadGrid"></div>

JS

        var hubSpendAccountActivities = new WinJS.Binding.List(
        [
            {
                activityDate: new Date(2013, 3, 12),
                description: "ONLINE TEST TWO LINE TRANSFER FROM ACCOUNT NUMBER",
                withdrawalAmount: "121.34",
                depositAmount: ""
            },
            {
                activityDate: new Date(2013, 3, 13),
                description: "VISA APL*APPLE ITUNES PURCHASE",
                withdrawalAmount: "34.12",
                depositAmount: ""
            },
            {
                activityDate: new Date(2013, 3, 14),
                description: "MOBILE CHECK DEPOSIT FROM 1234789 XXXX2024567",
                withdrawalAmount: "",
                depositAmount: "173.74"
            },
            {
                activityDate: new Date(2013, 3, 15),
                description: "CHECK CARD PURCHASE XXXXX1234 APLAPPLE ITUNES STORE PURCHASE XXXXX5674 CA TEST THREE LINE TRANSFER",
                withdrawalAmount: "5.99",
                depositAmount: ""
            },
            {
                activityDate: new Date(2013, 3, 16),
                description: "ARCH DEBIT XXXXX123 TARGET DEBIT CRD ACH TRAN ABC XYZ",
                withdrawalAmount: "3.99",
                depositAmount: ""
            },
            {
                activityDate: new Date(2013, 3, 17),
                description: "CHECK CARD PURCHASE XXXXXXX8023 SAVE THE CHILDREN XXXXX123 CT",
                withdrawalAmount: "354.87",
                depositAmount: ""
            },
            {
                activityDate: new Date(2013, 3, 18),
                description: "ATM DEPOSIT FROM THIS ATM MAXXXX92FHHG123 FIFTH AVE",
                withdrawalAmount: "",
                depositAmount: "50"
            },
            {
                activityDate: new Date(2013, 3, 19),
                description: "CHECK CARD PURCHASE XXXXXX8789 BAGEL FACTORY LUNCH MEAL",
                withdrawalAmount: "154.45",
                depositAmount: ""
            },
            {
                activityDate: new Date(2013, 3, 20),
                description: "ONLINE TEST XX1234",
                withdrawalAmount: "79.85",
                depositAmount: ""
            }
        ]
        );

var gridHubSpendAccountActivity = new Telerik.UI.RadGrid(document.getElementById("gridHubSpendAccountActivity"), {
    dataSource: hubSpendAccountActivities,
    schema: {
        model: {
            fields: {
                activityDate: { type: "date" },
                description: { type: "string" },
                withdrawalAmount: { type: "number" },
                amount: { type: "number" }
    }
        }
    },
    columns: [
        { field: "activityDate", title: "Date", format: "{0:MMM dd}", width: "13%" },
        { field: "description", title: "Description", width: "55%", sortable: false },
        { field: "withdrawalAmount", title: "Withdrawals", format: "{0:N2}", width: "16%", template: "#=withdrawalAmount > 0 ? '$' + withdrawalAmount : ''#" },
        { field: "depositAmount", title: "Deposits", format: "{0:N2}", width: "16%", template: "#=depositAmount > 0 ? '$' + depositAmount : ''#" }
    ],
    sortable: "single, allowUnsort",
    selectable: "none",
    filterable: false,
    groupable: false,
    reorderable: false,
    resizable: false
});

[Tsvetina]

Hi Randy,

The main issue in this scenario is that the grid does not know that these values should be treated as numbers. This is so because the schema setting that you have cannot be applied this way. The schema is a child object of the dataSource and it cannot be set the way you have it. Furthermore, it can be used only when the dataSource consumes a remote service (via the transport settings) or has local array of objects assigned to its data property.

Since in this scenario you set the datasource to a WinJS.Binding.List, it cannot apply a schema, meaning that the amount fields are not converted to numbers. In other words, when assigning a WinJS.Binding.List as a dataSource of RadGrid, you need to take care of converting the values inside the list itself.

One easy solution in this case is to traverse and convert the numbers before declaring the grid:

hubSpendAccountActivities.forEach(function (item) {
    item.withdrawalAmount = parseFloat(item.withdrawalAmount);
    item.depositAmount = parseFloat(item.depositAmount);
});

Give this a try and let us know if it is an acceptable approach in your scenario.

All the best,
Tsvetina
the Telerik team

[Randy]

Thanks Tsvetina,
That works perfectly - don't mind doing that to retain the sorting, for sure!

One last question:
How can I get that "$50" on the grid to show up as "$50.00" while still keeping my sorting and conditional formatting?

{ field: "depositAmount", title: "Deposits", format: "{0:N2}", width: "16%", template: "#=depositAmount > 0 ? '$' + depositAmount : ''#" }

Thanks,
Randy

[Tsvetina]

Hello Randy,

To always output the number in the template with two digits after the decimal point, you can use toFixed() function on the return value of the template:

template: "#=depositAmount > 0 ? '$' + depositAmount.toFixed(2) : ''#"

Regards,

Tsvetina
the Telerik team

[Randy]

Hey Tsvetina,

The ..ToFixed worked great. Putting that in I suddenly noticed that it would be better to right-align my dollar amount columns.
Is that possible?

See the "TelerikGridSortingWithConditionalFormatting.jpg" pic I attached in my original post, the dollar amounts in the cells under columns labeled "Deposits" & "Withdrawals" - can we right justify the all the amounts in those columns?

Thanks,

Randy

[Tsvetina]

Hi Randy,

Do you mean right-justify the specific columns text? If so, you can use the attributes property of the column and specify a style only for it which will right-justify it:

{
    field: "depositAmount",
    title: "Deposits",
    width: "16%",
    template: "#=depositAmount > 0 ? '$' + depositAmount.toFixed(2) : ''#",
    attributes: {
        style: "text-align: right"
    }
}

Greetings,
Tsvetina
the Telerik team

[Randy]

Awesome Tsvetina!
That right aligned all my currency values for each row.
Now can we also right justify the header/title "Deposits" as well?

[Tsvetina]

Hi Randy,

The approach is the same for headers, just use the headerAttributes property to apply the style to the header cell of a column.

Greetings,
Tsvetina
the Telerik team

[Randy]

Thank you, you're awesome as always Tsvetina.

For any of you who right justify a column and find out the grid scrollbar now covers part of your data, you can pad right 20 to fix that (unless you have a more elegant solution Tsvetina :)

attributes: { style: "text-align: right; padding-right: 20px;" },
headerAttributes: { style: "text-align: right; padding-right: 20px;" }

Thanks,

Randy