UX List "Count if"

[aprescott]

I feel like this is something easy, but I just can't seem to figure it out.

Have a list control in a UX. It's showing transactions on a deposit (filtered by deposit date and deposit number). Wanting to display a count of checks vs cash. We have a field in the list that shows payment type.

I can get a count of records in the list

var count = {dialog.object}.getListServerSideSummaryValue('CONTRIBUTIONS','ID_NUMBER','COUNT');

setTimeout(function() {
{dialog.Object}.setValue('COUNTER',"This deposit has "+count+" transactions.");
},100);

with COUNTER being a label control that is updated by the javascript. The Timeout was to give the list time to populate.

Is there a way to conditionally count? i.e. count where payment_type = "Check"

[gianfior85]

This would likely have to be a separate SQL query, irrespective of the list itself (as an ajax callback) where you insert the count into whatever control you'd like. You can link the ajax callback to one of the list events so that they populate around the same time, or in the same setTimeout function that you already have.

I'm sure there is also a way to conditionally loop through your entire dataset in the list clientside so you don't have to make another callback, but it isn't easy, and a quick count query costs virtually nothing.

[mikeallenbrown]

Perhaps an arbitrary column can be added to your list's SQL statement. You can use CASE in the SQL statement to set a 1 if it's a check. Then in the list control turn on the summary values and add up the new column.

Here is the SQL example...

SELECT
    *,
    CASE
        WHEN Payment_Type = 'CHECK' THEN 1 ELSE 0
    END AS Check_Count
FROM
    dbo.Invoice_Payments

For counting and totaling based on data in a list control check out the list event afterClientSideSummaryCompute.

[Pizzadude]

There are different ways to do this. Are you looking for a client-side count, or a server-side count?

[Jay Talbott]

I would do this as a javascript loop through the list.
Here's the pseudocode:
get a pointer to the list
get the count of the items in the list
set two variables: checkCount and cashCount
set each variable = 0
loop through the list
add one to checkCount for each check, and add one to cashCount for each cash transaction
display the results, either as an alert or stuff the values into fields on the form

Something like this

var lObj = {dilog.object}.getControl('list1');
var _d = lObj._data;
var len = _d.length;
var checkCount = 0;
var cashCount = 0;

for (i=0;i<len;i++){

if (_d[i].TransactionType == 'Check') {

checkCount = checkCount + 1;

{

if (_d[i].TransactionType == 'Cash') {

cashCount = cashCount + 1;

{

}
{dialog.Object}.setValue('CHECK_COUNTER','This deposit has ' + checkCount + ' check transactions.');
{dialog.Object}.setValue('CASH_COUNTER','This deposit has ' + cashCount + ' cash transactions.');

You will need to adjust the text in red to match your controls.

(by the way, I use single quotes in Alpha when writing javascript, but that may be a personal preference, not a requirement)

Hope that helps.
Jay