You are viewing limited content. For full access, please sign in.

Question

Question

Calculations in a table from another table

asked on October 26, 2022 Show version history

Is it possible to have the 'Accounts Journal' table to be fully populated via selections made in the main 'Items' table?

 

for example, all lines in the 'Items' table with the tax code of 'Standard' and a GL Code of 'P3440' will have their NET added together and placed on a new line in the 'Accounts Journal', the same will apply for the VAT field. The GL code and description will also have to be populated in the 'Accounts Journal'

 

The aim here is to not have a human add up and complete the 'Accounts Journal'

 

I am willing/able to use workflow or JS to achieve this, it doesn't have to be on the form, but it does have to be created in an automated fashion. Any help is greatly appreciated 

0 0

Answer

SELECTED ANSWER
replied on October 26, 2022 Show version history

Here's a way to do it with Javascript, this might not be the most efficient, but it worked for me in a little test I threw together.

I'm doing something similar to create a posting file that we can import into our accounting system.  In my case, it's not going into a table, but instead is creating a CSV file, but the theory is kind of similar.

For this example, I have it loading the journal table from a button on the form.  You could make it run the function when the itemTable changes instead of when the button is clicked, but be aware that it's doing a lot of processing each time.  It won't be a problem as long as the item table doesn't get too long, but if it starts getting much longer, the delay may become noticible, and if it is dragging each time the user changes anything on the item table, that could be a problem for your users, which is the reason that I did it from a button.

You could also call the function when the form loads if you wanted to ensure the journal table is populated from a prior task/submission (just add a line like this:   populateJournalTable();   before line 3 of the code below).  However, if you run it upon form load, be aware that I haven't tested it on an archive/read-only version of the form, and I suspect it would have some issues.

Give your item table the class name of: itemTable

Give your journal table the class name of: journalTable

In both tables, the six fields, should have these six class names respectively: vatCode, glDescription, glCode, netAmount, vatAmount, grossAmount

For the button, use a custom HTML element with this code: 

<p style="text-align: center;" id="populateJournal"><button type="button">Populate the Accounts Journal</button></p>

 

Then this is the Javascript: 

$(document).ready(function() {
  
  //When the button is clicked to populate the journal table, call the function.
  $('#populateJournal').click(populateJournalTable);
  
  //Function to populate the journal table.
  //Any existing rows are deleted, and then rows are created as needed.
  //All fields in the table are set as readonly - this is done via Javascript
  //instead of via the Layout page to ensure values populated by Javascript
  //are saved in the fields.
  function populateJournalTable () {
    
    //Loop through each row of the journalTable, if the fields are not undefined,
    //then delete that row of the table, so we are starting with an empty table.
    $('.journalTable tr').each(function() {
      var vatCode = $(this).find('.vatCode select').val();
      if(vatCode != undefined) {
        $(this).find('.cf-table-delete').click();
      }
    });
    
    //Loop through each row of the itemsTable, and gather its values.
    //Then loop through each row of the journalTable to find the match,
    //and add the amounts.  If no match is found, add a row for the match.
    $('.itemTable tr').each(function() {
      var vatCode = $(this).find('.vatCode select').val();
      var glDescription = $(this).find('.glDescription select').val();
      var glCode = $(this).find('.glCode select').val();
      var netAmount = parseFloat($(this).find('.netAmount input').val()) || 0;     // the || 0 will return zero for "falsey" values (NaN from blank fields)
      var vatAmount = parseFloat($(this).find('.vatAmount input').val()) || 0;     // the || 0 will return zero for "falsey" values (NaN from blank fields)
      var grossAmount = parseFloat($(this).find('.grossAmount input').val()) || 0; // the || 0 will return zero for "falsey" values (NaN from blank fields)
      if(vatCode != undefined) {
        var rowFound = false;
        
        //Find matching values in journalTable.
        $('.journalTable tr').each(function() {
          var vatCode2 = $(this).find('.vatCode select').val();
          var glDescription2 = $(this).find('.glDescription select').val();
          var glCode2 = $(this).find('.glCode select').val();
          var netAmount2 = parseFloat($(this).find('.netAmount input').val()) || 0;     // the || 0 will return zero for "falsey" values (NaN from blank fields)
          var vatAmount2 = parseFloat($(this).find('.vatAmount input').val()) || 0;     // the || 0 will return zero for "falsey" values (NaN from blank fields)
          var grossAmount2 = parseFloat($(this).find('.grossAmount input').val()) || 0; // the || 0 will return zero for "falsey" values (NaN from blank fields)
          
          //Match was found, add amounts to journalTable.
          if(vatCode == vatCode2 && glDescription == glDescription2 && glCode) {
            rowFound = true;
            var netAmountNew = netAmount + netAmount2;
            var vatAmountNew = vatAmount + vatAmount2;
            var grossAmountNew = grossAmount + grossAmount2;
            $(this).find('.netAmount input').val(netAmountNew.toFixed(2));
            $(this).find('.vatAmount input').val(vatAmountNew.toFixed(2));
            $(this).find('.grossAmount input').val(grossAmountNew.toFixed(2));
          }
        });
        
        //No match was found in journalTable.  Add a new row and populate fields.
        if(!rowFound) {
          $('.journalTable .cf-table-add-row').click();
          $('.journalTable tr:last').find('.vatCode option').filter(function () { return $(this).html() == vatCode; }).prop('selected', true);
          $('.journalTable tr:last').find('.glDescription option').filter(function () { return $(this).html().replace(/&amp;/g, "&") == glDescription; }).prop('selected', true);  //this: replace(/&amp;/g, "&") is necessary to deal with & in the value, since we're dealing with the html not a string variable.
          $('.journalTable tr:last').find('.glCode option').filter(function () { return $(this).html() == glCode; }).prop('selected', true);
          $('.journalTable tr:last').find('.netAmount input').val(netAmount.toFixed(2));
          $('.journalTable tr:last').find('.vatAmount input').val(vatAmount.toFixed(2));
          $('.journalTable tr:last').find('.grossAmount input').val(grossAmount.toFixed(2));
        }
      }
    });
    
    //Loop through each row of the journalTable and make the field readonly,
    //hide the buttons to add rows and delete rows.
    $('.journalTable tr').each(function() {
      $(this).find('input').each(function() {
        $(this).attr('readonly', 'true');
      });
      $(this).find('select').each(function() {
        $(this).attr('readonly', 'true');
      });
      $(this).find('.cf-table-delete').each(function() {
        $(this).hide();
      });
    });
    $('.journalTable').find('.cf-table-add-row').hide();
    
  }
  
});

 

Here's how it looks before I click the button:

 

And then after clicking the button:

2 0
replied on October 28, 2022

Thanks Matthew, this solution has worked very well. I set some fields as read only in the items table and it still works as expected. now only the vat code, gl description and gl code are editable on the items table.

 

Thanks again, this has been a big help

 

 

 

1 0
replied on October 28, 2022

Readonly in the items table should not be an issue for the code since it is just reading from those fields.  The journal table, on the other hand, could have issues if you set fields to readonly since they are being populated by the Javascript.  You might find they don’t save after submission if they are set readonly from the Layout page.  Which is why I have the script do that step.

I’m so glad to hear it worked for you!  Have a great day!

0 0

Replies

replied on October 26, 2022

You can access the other tables in your calculations configuration from one table to the next. In your second table's field, you can sum the total of another table's column just the same as if you were working with a standard isolated field.

So the answer to your primary question is Yes, but I think the answer to the secondary question, adding rows with a calculation would be No.

I don't think there is any way to add rows to a table using calculations, it must be done with either a variable (set by Workflow or previous submission) or a Lookup.

The 2 options I can think of are using Rules and Tables (between submissions) or Javascript to add rows in real time.

It looks like your creating your own version of Quickbooks here.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.