calculate grand total in repeating group inside form

[AMTI]

/*
Joomla 2.5.9.
Fabrik 3.0.7 w/ latest Git
Any Browser
*/

I know this can happen:

I have 2 Groups: Group 1 is the Line Item Group, Group 2 is the Total Group.

The Line Item Group has a several elements and is repeating as a table. You can add and remove on the fly via ajax. The ones we are concerned about are:
Quantity(int), Price Ea. (int), Price (calc: Quantity* Price Each)

Price calculates perfectly.

The Total Group has only one element: Grand Total. I need this element to calculate the total of all the calculated price elements on the fly.

return '{tablename___price}'

only gives me the first value of the repeatable group.

I know these values have got to be stored somewhere inside an ajax array somehow.
How can I get these calculated price values to sum and return to the grand_total element?

Please see attachment for extreme clarification.

Thanks in Advance,
Trip

 

[rob]

hi

You would indeed need some js. I've added an example to the wiki :

http://fabrikar.com/forums/index.ph...pdate_a_master_total_element_in_another_group

 

[AMTI]

Thanks for updating the wiki! But the script is only producing NaN as a result. I tried updated the field of the line item calculated price from text to decimal with phpmyadmin, but that didn't work out. Should the calcuation field for grand total remain blank (as the script should be filling in the information?)

Also If I wanted the grand_total to update onBlur as I tab through the line-item fields how would that work out. Here is my code so far in a file called form_34.js in the proper folder.

function total() {
  // replace 1 with the form's id
  var formRef = 'form_34';
 
  // Replace with the element name (note it is prefixed with join___JOINID___)
  var elementName = 'join___51___purchasing_lineitems___price';
 
  // The element containing the line items total
  var totalElement = 'purchasing_requests2___grand_total';
 
  var total = 0;
 
  // Get the form's elements
  var elements = Fabrik.blocks[formRef].formElements;
 
  // Loop over the elements and if their key contains the elementName update the total
  elements.each(function (element, key) {
 
    if (key.contains(elementName)) {
      total += element.get('value').toFloat();
    }
 
  }
 
);
 
  // update the total element
  elements.get(totalElement).update(total);
 
}
 
 
// Add events when adding/deleting groups
 
Fabrik.addEvent('fabrik.form.group.duplicate.end', function (form, event) {
  total();
});
 
Fabrik.addEvent('fabrik.form.group.delete.end', function (form, event) {
  total();
});

Thanks again... almost there.

 

[AMTI]

For what its worth the values are coming out as strings, its as if it's concatenating everything which is producing the error NaN , because it's a string in the computed results, not a number or float or int.

________________________

Update: I found out that I had been calculating(sum) the element in the list view settings. Once I turned this off, the total started totaling.

However; If I go back to edit the record, the total is not displayed and it is not stored in the database. If i fool around with the calc on save ajax options, I can i get the grand total to display the first row's total, but not the others.

But the intended behavior is to have the record stored with the correct total. I have to Add and Subtract a row to calculate the grand total, but nevertheless, its never stored in the database.

 

[AMTI]

I changed the element from calc to field (decimal) and was able to get it to save to the database in combination with using the form php plugin...

But I still need the grand_total field to calculate when I tab through line items, (ie. changing quantity or price). The line-item calculation works. But the grand total only updates when I add or delete line items.

What's the code to make it listen to those changes as I tab through line items?

Really Really Close!

Thanks,
Trip

 

[rob]

I think you could do this via the admin, edit each element that is used to build the total and add a javascript blur event with the following code:

 total();

 

[AMTI]

I think you could do this via the admin, edit each element that is used to build the total and add a javascript blur event with the following code:

 total();

I put the blur events on both the qty and price ea. It really doesn't calculate the total until i tab through the price ea. (b/c price is calculated via qty * price_ea). So I added one to the next element you could tab through (PN#) and it updates quicker. I guess this is as close as I'm going to get.

Thanks for the help!

 

[AMTI]

I"m still getting NaN Issues from the JavaScript code. Could you take a second look at it and see if something is missing. It seems to keep trying to convert everything into strings. Even though the fields set to decimal and only numbers are typed in. I really need this to work as I tab through the fields and add and remove rows. I have spent way to much time on this one form and need to move on.

Also on the initializing of a new row how is it possible to have the price calculation run so that it is not blank. (I'm thinking this could be part of the problem.)

Here is all of the information starting with the form_34.js code:

function total() {
  // replace 1 with the form's id
  var formRef = 'form_34';
  // Replace with the element name (note it is prefixed with join___JOINID___)
  var elementName = ('join___51___purchasing_lineitems___price');
  // The element containing the line items total
  var totalElement = 'purchasing_requests2___grand_total';
  var total = 0;
  // Get the form's elements
  var elements = Fabrik.blocks[formRef].formElements;
  // Loop over the elements and if their key contains the elementName update the total
  elements.each(function (element, key) {
    if (key.contains(elementName)) {
        total += element.get('value').toFloat();
    }
  }
);
  // update the total element
  elements.get(totalElement).update(total);
}
// Add events when adding/deleting groups
Fabrik.addEvent('fabrik.form.group.duplicate.end', function (form, event) {
  total();
});
Fabrik.addEvent('fabrik.form.group.delete.end', function (form, event) {
  total();
});

The elements we are focused on are from the purchasing_requests table
QTY -> Plug-in: Field -> Default: 1 -> Format: Decimal (number formatting does not change a thing) -> Blur event: Total();
PRICE EACH -> Plug-in: Field -> Default: 0 -> Format: Decimal (number formatting does not change a thing) Blur event Total();
PRICE -> Plug-in: Calc -> Only Calc on Save: Yes -> Ajax Calculation: Yes -> Calculation:

$total = 0;
$qty = '{purchasing_lineitems___qty}';
$each = '{purchasing_lineitems___each}';
$total = ($qty * $each);
return $total;

GRAND TOTAL -> Plug-in: Field -> Default: Blank -> Format: Decimal (number formatting does nothing) -> form_34.js used to calculate this total

FORM PHP Plugin to store grand total:

$rowid = $formModel->_formData[id];
$grandtotal = $formModel->_formData[grand_total];
if (!empty($grandtotal)) {
  $db = JFactory::getDBO();
    $query = $db->getQuery(true);
  $query->update('purchasing_requests2')->set("grand_total = '$grandtotal'")->where("id = $rowid");
  $db->setQuery($query);
  $db->query();
}

There is also a Form Receipt Plugin, and 2 Form Email plugins, but they have nothing to do with the calculation.

Please solidify this dilemma for us.

Thank you for all your help!!!

Sincerely,
Trip

 

[cheesegrits]

I'd need to see the page to debug it. If you want to fill in your Fabrik Sites with whatever I need, I'll take a look.

-- hugh

 

[AMTI]

The site is a corporate internal Intranet site and unfortunately I cannot open up the firewall.
I decided to move on to other forms and begin styling the site and revisit this.

So here I am.

I have this much to say. Upon debugging and adding watches: the error occurs because of this (from MooTools docs)

Fallback to Element Attributes:

var id = $('myDiv').get('id'); // returns "myDiv".
var value = $('myInput').get('value'); // returns the myInput element's value.

Notes:

• If the corresponding accessor doesn't exist in the Element.Properties Object, the result of Element:getProperty on the property passed in is returned.

That is exactly what is being returned to the total in firebug... instead of a number, it returns an the Object. NaN.

I put a band-aid on it with this

  if (isNaN(total)) {
      elements.get(totalElement).update('');
  }

Any further help or suggestions would be appreciated. To recreate the scenario is not that hard as per the detailed previous post I left.
One group in the form repeats (and add/removes) line items that total each line based on quantity and price to a calculated price.
Another group contains (hopefully) the total of the calculated prices of each line item.

Could you recreate and take another shot at this please?

Thanks in advance,
Trip

 

[rob]

hi
First its important to realize, that in your initial code, the get('value') is not asking to get the value from the <input> DOM node , but from the Fabrik JavaScript Object that represents the field. So the docs you are quoting from for mootools isn't actually what the code is doing.

Could you recreate and take another shot at this please?

Well its extremely hard to replicate these types of issues, as any one small change from what we have may result in something different from what you have, and thus we end up testing dis-similar set ups. The code works on my test page - its where I wrote it, but it could be a variety of things (such as mis-spelt names, joins being repeats, or whatever) that means that what we see is not what you see. That is why we really can only help with very specific coding if we can gain access to the site. Its a bit like asking a doctor to diagnose one patient based on the symtoms of another, yes they may well both have red spots on them but only by examining the patient does the doctor find out what the actual illness is.

So unfortunately, I can only advise on a more general level:

Your check is going to be fine, if you want to ensure that the total is only shown if all fields contain a valid numeric value.

If you wanted to show a total, of all fields except those for which their value is not numeric then you would replace:

 total += element.get('value').toFloat();

with

var v = element.get('value').toFloat();
if (total === 'NaN') {
// Remove this for debugging but it might help you figure out what is going on.
console.log('The element', element, 'whose value is, element.get('value'), 'could not be converted to a float');
} else {
// Remove this for debugging but it might help you figure out what is going on.
console.log('The element', element, 'whose value is, element.get('value'), 'was converted to a float');
total += v;
}

That might give you a clue as to what is going on. As the console should report exactly which element is causing the sum to fail

 

[AMTI]

I was able to get the field to calculate by using this:

var v = (Number(element.get('value'))).toFloat(2);

But now the only problem I'm having is that anything added together(no matter if its 1 line item or several) that has a grand total over 999.99, becomes.... NaN.

Suggestions on this one???

Thanks again,
Trip

 

[rob]

that sounds like a red herring, if I do this in the console, :

(Number('999.99')).toFloat(2) + (Number('999.99')).toFloat(2)

I get a value of '1999.98' outputted.
Did you try what I suggested in the previous post?

 

[AMTI]

Yes I did try your previous post. It looks like adding the total(); as a blur event as i tabbed through the form was really screwing some addition up. So removing them I found that I can total over 999.99

But in addition, now it looks like the latest Git Update I did yesterday and Git Update from just a minute ago - has done something to calculating the price per lineitem (qty * price each = price) in a repeatable join. The calc element keeps calculating the same values in the first array [0].

 

[rob]

Hi

... has done something to calculating the price per lineitem (qty * price each = price) in a repeatable join. The calc element keeps calculating the same values in the first array [0].

Hmm not sure how that would have ever worked, but I've fixed it so that it should work, changed in github

 

[AMTI]

Excellent. Thank you.