Forms Table Lookup

asked on April 15, 2014

I am trying to build a Laserfiche Form (using forms 9.1.1) that contains a table. In this table one column with multiple rows needs to be populated with information using Field Lookup rules to a SQL database. I understand that it is not currently possible to populate individual rows with different data from a data source. This table then contains a drop-down list field to add, subtract, or no change. A new proposed value is entered into another field in the table, and the resulting calculation is then displayed in a separate field. I am attaching a screenshot of the table.

Here are my questions:

1. How can I populate the Current Value row fields with lookup information?

2. If I use a hidden field(s) to pass the value(s) to the current value rows, how do I accomplish this? I do not see individual

3. I want to have Current Value and Proposed Change(+,-,Nochange) and Proposed Value = Calculated Revised Total. Is it possible based on the selection for Proposed Change to add, subtract or have no change for the Calculated Revised Total? What would the Java script look like?

Any help is greatly appreciated. Thank you in advanced.

- Lance

Forms911_Table.png (9.43 KB)

| Download

0 0

APPROVED ANSWER

replied on April 17, 2014 • Show version history

You cannot directly fill table rows with individual values from a lookup. Instead, you'll need to fill a drop-down field outside of the table with the lookup values and then use JavaScript to appropriately modify your table. Give this drop-down field the filledField class.

This code assumes you have assumes a table structure like yours, with the currentValue, proposedChange, proposedValue, and revisedValue classes added to the appropriate table columns.

Here's some code to get you started. Replace #q4 with the identifier for the Add button for your table.

$(document).ready(function () {
    function fillColumn() {
        if ($('tbody tr').length > 1) {
            return;
        } else {
            var resultNumber = $('.filledField').find('option').size();
            for (var i = 1; i <= resultNumber; i++) {
                $('tr:contains("Row ' + i + '")').find($('.currentValue input')).val($('.filledField').find('option:nth-child(' + i + ')').text());
                $('#q4').trigger('click')
            }
        }
    }

    function sumtotal() {
        $('.cf-table-block tbody tr').each(function () {
            var increase = parseNumber($(this).find('.currentValue input').val()) + parseNumber($(this).find('.proposedValue input').val());
            var decrease = parseNumber($(this).find('.currentValue input').val()) - parseNumber($(this).find('.proposedValue input').val());
            var s = $(this).find('.proposedChange option:selected').val() == "Increase" ? increase : decrease;
            $(this).find('.revisedValue input').val(s);
        });
    }

    function parseNumber(n) {
        var f = parseFloat(n); //Convert to float number.
        return isNaN(f) ? 0 : f; //treat invalid input as 0;
    }

    $('.cf-table-block').on('blur', 'input', sumtotal);
    $('.filledField select').change(fillColumn);
});

The fillColumn function places the lookup values into the table; the sumtotal function performs the row calculations (based on "Increase" or "Decrease" as option values for the proposed change field).

As written, the code runs when a new option is selected in the filled field. You could change this to run when a button is clicked, and then hide the button after the table is filled in.

0 0

Question

Question

Answer

Replies

Sign in to reply to this post.