Summing extra column(s) with script

[highend]

Code: Select all

$chosenColumns = inputselect("Select column(s) to sum up", $columnHeaders, ",", 2+8192+16384, , 300, 400);

Change the last two values...

[Schuller]

This works great.
Thank you!

[Schuller]

highend, is there a tweak also to resize the second popup box after selecting items from the first popup?

[highend]

Sure, just replace the text... line with:

text $log, 900, 600;

or whatever size you want...

[Schuller]

Perfect!

[Schuller]

This should work for all column setups...

Code: Select all

    $columnHeaders = setcolumns(, 64); // ","-separated
    // E.g.: Name,Price $,Sold $,Modified,Created

    $visibleColumns = regexmatches(columnlayout(, "get"), "\+[^.]+");
    // E.g.: +Name|+Extra 1|+Extra 2|+Modified|+Created

    // Make sure both counts are equal, otherwise a "," is used as a caption in a column
    $cntColumnHeaders  = gettoken($columnHeaders, "count", ",");
    $cntVisibleColumns = gettoken($visibleColumns, "count", "|");
    end ($cntColumnHeaders != $cntVisibleColumns), "A ',' is not allowed in column header(s), aborted!";

    $chosenColumns = inputselect("Select column(s) to sum up", $columnHeaders, ",", 2+8192+16384, , 300, 400);
    end !($chosenColumns), "No column(s) chosen, aborted!";

    // Create report
    $selected = <get SelectedItemsNames>;
    $itemList = ($selected) ? 1 : 0;
    $report   = report("|", $itemList);
    // E.g.: 1|deep cool fans|$30,00||21.09.2023 14:15:03|21.09.2023 14:15:03
    //       2|dewalt - drill 12v|$25,00|$20,00|21.09.2023 14:15:09|21.09.2023 14:15:09
    //       3|dewalt - drill 20v|$100,00|$90,00|21.09.2023 14:15:21|21.09.2023 14:15:21
    //       4|tripod||$10,50|21.09.2023 14:15:36|21.09.2023 14:15:36

    // Remove line numbers if visible
    $report = get("#406") ? regexreplace($report, "^\d+\|") : $report;

    // Get the digits of the columns to autosum
    $chosenDigitColumns = "";
    foreach($column, $chosenColumns, ",", "e") {
        $chosenDigitColumns .= gettokenindex($column, $columnHeaders, ",", "") . "|";
    }
    $chosenDigitColumns = trim($chosenDigitColumns, "|");

    // Sum up
    $log = "";
    foreach($num, $chosenDigitColumns, "|", "e") {
        // Create and initialize count variables
        $var  = '$col_' . $num;
        *$var = 0;

        // Get the real column name
        $realColumn = gettoken($columnHeaders, $num, ",", "t");

        status "Calculating " . quote($realColumn) . " ...", , "progress";
        foreach($line, $report, <crlf>, "e") {
            $value  = gettoken($line, $num, "|", "t");
            $value  = regexmatches($value, "[0-9,.]+");
            *$var  += $value;
        }
        // Create log
        $log .= "Sum [" . $realColumn . "]: " . *$var . <crlf>;
    }
    end !($log), "No log created?";

    text $log;

Hi,

It seems this script is no longer working after Version 26.70.0600.
I upgraded a while back, maybe a month or so ago and noticed it was not working so then I downgraded back to 26.70.0600 and it has worked fine. Now today, tried updating again to the latest version and its still not working, so I downgraded again to 26.70.0600 and it works fine.

Is there something in the script that needs to change to work with the latest versions?

[highend]

Tried it with the current official version, no problems here?

1.png (26.07 KiB) Viewed 157 times

[Schuller]

Would your test on the latest version of XY work for the attached?

2025-04-23(column header 1).png (33.49 KiB) Viewed 128 times

The column headers, Building (Class 4), FFE (Class 20), Total CCA, are custom columns and not extra columns and they get there data from the filename. Again, summing these custom columns was fine before the upgrade. I know when I originally asked for a summing script I was referring to extra columns, however back then I didn't even know what a custom column was. Not sure now if the script is only meant for extra columns.

The script for the custom column Building (Class 4) is: return gettoken(gettoken(<cc_base>, 2, "["), 1, "]");
The script for the custom column FFE (Class 20) is: return gettoken(gettoken(<cc_base>, 2, "{"), 1, "}");
The script for the custom column, Total CCA is: return gettoken (<cc_base>, 2, "$", , 2);

Here is what I get when I sum up on version 27.00.0300

2025-04-23(column header 2).png (61.93 KiB) Viewed 128 times

Here is what I get when I sum up on version 26.70.0600

2025-04-23(column header 3).png (68.72 KiB) Viewed 128 times

[highend]

report() is still broken (for custom, not for extra columns), I guess Don will fix it soon...

Old (26.70.0600):

Code: Select all

1|2012-03-01(2011-528, Accum.Depreciation)[4295.94]{170.33} $4466.27.pdf|4295.94|4295.94|4466.27|23.04.2025 16:09:56|23.04.2025 16:09:56
2|2013-03-01(2012-528, Accum.Depreciation)[0.00]{0.00} $0.00.pdf|0.00|0.00|0.00|23.04.2025 16:09:56|23.04.2025 16:10:58
3|2014-03-01(2013-528, Accum.Depreciation)[5131.31]{622.99} $5754.30.pdf|5131.31|5131.31|5754.30|23.04.2025 16:09:56|23.04.2025 16:11:53

Current (27.00.0300):

Code: Select all

1|2012-03-01(2011-528, Accum.Depreciation)[4295.94]{170.33} $4466.27.pdf|{:d-17}|{:d-18}|{:d-19}|23.04.2025 16:09:56|23.04.2025 16:09:56
2|2013-03-01(2012-528, Accum.Depreciation)[0.00]{0.00} $0.00.pdf|{:d-17}|{:d-18}|{:d-19}|23.04.2025 16:09:56|23.04.2025 16:10:58
3|2014-03-01(2013-528, Accum.Depreciation)[5131.31]{622.99} $5754.30.pdf|{:d-17}|{:d-18}|{:d-19}|23.04.2025 16:09:56|23.04.2025 16:11:53

[Schuller]

Okay, will use what is working for now.

Thanks

[admin]

report() is still broken (for custom, not for extra columns), I guess Don will fix it soon...

Is there some minimal example to isolate the problem? I can't see right now where those {:d-17} come from.
Just saw that jupe gave an example.

Fixed in next beta.

[Schuller]

Thanks!