Sort by calc issue

[ranwilli]

As long as all rows produce the same number of digits (see example below), the sort is accurate. However, if one or more rows ends up with a larger number, the sort is off (again - example below)

It seems I need a "format string", but I have no idea what to enter there.

example:

27
24
20
181
13
10

re-entering the columns that are calculated to change the list yields:

27
24
201
181
13
10

So it is clearly sorting as text... how do I display it as integers to make the sort correct?

here is the code from the field:

$en = '{fasttrack___enrollments}';
$enpt = ($en * 1);
$vanc = '{fasttrack___vandc_packs}';
$vancpt = ($vanc * 1);
$mk = '{fasttrack___member_kits}';
$mkpt = ($mk * 1);
$dir = '{fasttrack___directors}';
$dirpt = ($dir * 5);
$mt = ($enpt + $vancpt + $mkpt + $dirpt);
return (int)$mt;

Thanks in advance!

 

[ranwilli]

I found some other posts here suggesting %g as a profile string...

doesn't create any new problems, but it doesn't make the list sort properly either...

So maybe the issue is the (int) preceeding the variable that I return.

I assumed that returned my "result" as an integer (all of the fields we are calculating are 8/0 integers), but clearly that isn't so...

It also seems that ANY sprintf we apply is going to return a string, so it seems that is not a cure either.

Any idea how I can get the calculated element to return an integer?

 

[ranwilli]

Turns out my $mt variable IS an integer, but the table still sorts it like a string...

Adding a sprintf like "%04d" doesn't help either...

0009

still sorts above (it's a "DESC" sort)

0014

Does that seem right?

 

[ranwilli]

A gentle bump - urgent

 

[pkjoshi]

I am not sure I understand your question what exactly you want.
If I am right you want the sorting on a column(element)which is a calc field and it is not working.

 

[ranwilli]

I group the rows by another field, and sort by the total from all the four fields that are summed by the calculated field.

But they sort WRONG

it's like the table is sorting strings instead of numbers...

they sort (DESC) like this:

9
24
18
17
15
14
123
As long as all sums have equal number of digits, sort is fine, but it blows up whenever the digit count varies...

Does that make more sense???

 

[pkjoshi]

OK, What I find is that if you select number format to "Yes" then sorting/ordering works otherwise not even if you have selected integer or decimal in formatting options.

 

[ranwilli]

It's a calculated field...

Where is that setting?

 

[pkjoshi]

Oh, Yes I am wrong, there is no option for number format in calc element.

I can confirm that sorting is not working on calc field.

 

[ranwilli]

So where do I go from here? can I code a copy of the calculated total into an integer field?

 

[pkjoshi]

I don't think that will be necessary.
I am sure Rob will give a better solution on this.

 

[ranwilli]

Well, I need a work-around very quickly, but I appreciate your responses...

Does anyone know how I can copy the calculated result to a field element?

 

[ranwilli]

If I run the following script:

$setcol =& JFactory::getDBO();
$setcol->setQuery("UPDATE `fasttrack` SET `integer_total` = `month_point_total`");

It copies the calculated totals to another column of integer fields, which sort just fine...

BUT,
when I try to run that script from the form submit plugin ->
end of form submission (OnAfterProcess)
$setcol =& JFactory::getDBO();
$setcol->setQuery("UPDATE `fasttrack` SET `integer_total` = `month_point_total`");

Nothing updates...

Any ideas?

 

[rob]

youneed to do a

$setcol->query();

at the end of the code to acutally run the query

 

[ranwilli]

Thanks, Rob... I knew it had to be something on the simple side...