Jam.py documentation

How to link two tables

We’ll explain how to link two items on example of the tracks and invoicetable items from the demo application. We’ll link the record of tracks with the corresponding list of sold tracks from invoicetable that contains all sold tracks from invoices.

The default behavior if view_form is defined in the on_view_form_created event handler declared in the task client module.

We will change it in the on_view_form_created event handler in the tracks client module

function on_view_form_created(item) {
  item.table_options.height -= 200;
  item.invoice_table = task.invoice_table.copy();
  item.invoice_table.paginate = false;
  item.invoice_table.create_table(item.view_form.find('.view-detail'), {
      height: 200,
      summary_fields: ['date', 'total'],
  });
}

Then we reduce height of the table that displays tracks data by 200 pixels

item.table_options.height -= 200;

create a copy of invoice_table, set its paginate attribute to false and call the create_table method to create a table that will display the sold tracks

item.invoice_table = task.invoice_table.copy();
item.invoice_table.paginate = false;
item.invoice_table.create_table(item.view_form.find('.view-detail'), {
    height: 200,
    summary_fields: ['date', 'total'],
});

For this table we set the height to 200 pixels and define to summary fields.

This table will always be empty if we won’t define the following on_after_scroll event handler:

function on_after_scroll(item) {
    if (item.view_form.length) {
      if (item.rec_count) {
          item.invoice_table.set_where({track: item.id.value});
          item.invoice_table.set_order_by(['-invoice_date']);
          item.invoice_table.open(true);
      }
      else {
          item.invoice_table.close();
      }
    }
}

The on_after_scroll event is triggered whenever the current record is changed. So when the track is changed we call open method, pre-setting the filter and order

item.invoice_table.set_where({track: item.id.value});
item.invoice_table.set_order_by(['-invoice_date']);
item.invoice_table.open(true);

This method sends a request to the server, that generates sql query, executes it and returns a dataset that contains sold records of this track ordered in descending order of invoice_date field.

If the tracks dataset is empty we clear the sold records dataset by calling the close method.

Because controls in Jam.py are data-aware every change of sold records dataset will be displayed in the table that we created in the on_view_form_created event handler.

Now every time the track has changed the application send request to the server to renew the sold tracks. This is not effective and sometimes can lead to delays. To avoid this we use the JavaScript setTimeout function:

var scroll_timeout;

function on_after_scroll(item) {
    if (!item.lookup_field && item.view_form.length) {
        clearTimeout(scroll_timeout);
        scroll_timeout = setTimeout(
            function() {
                if (item.rec_count) {
                    item.invoice_table.set_where({track: item.id.value});
                    item.invoice_table.set_order_by(['-invoice_date']);
                    item.invoice_table.open(true);
                }
                else {
                    item.invoice_table.close();
                }
            },
            100
        );
    }
}

This function guarantees that the data will be updated no more than once every 100 milliseconds.

Since the invoicetable is a detail it has the master_rec_id field that stores a reference to invoice that has this record, we can show the user an invoice that contains the current sold record. To do so we pass to the create_table method the function that will be executed when user double click the record:

item.invoice_table.create_table(item.view_form.find('.view-detail'), {
    height: 200,
    summary_fields: ['date', 'total'],
    on_dblclick: function() {
        show_invoice(item.invoice_table);
    }
});

and define the function as follows:

function show_invoice(invoice_table) {
    var invoices = task.invoices.copy();
    invoices.set_where({id: invoice_table.master_rec_id.value});
    invoices.open(function(i) {
        i.edit_options.modeless = false;
        i.can_modify = false;
        i.invoice_table.on_after_open = function(t) {
            t.locate('id', invoice_table.id.value);
        };
        i.edit_record();
    });
}

In this function we create a copy of the invoices journal and find the invoice. When the open method is executed we will show the invoice by calling its edit_record method. But before calling it we set its attributes so that it will be modal and the user won’t be able to modify it.

Besides we dynamically assign on_after_open event handler to the invoice_table detail of the invoice we get. In this event handler we will find the current record in the sold records by calling the locate method.

Finally we will check the lookup_field attribute of tracks. This attribute is true if the item was created to select a value for the lookup field when a user clicks on the button to the right of lookup field input. We will make so that the sold tracks are not shown when the user selects the value for the lookup field.

In addition, we add an alert informing the user about the possibility of seeing the invoice.

Finally the code of the on_view_form_created will be as follows:

function on_view_form_created(item) {
    if (!item.lookup_field) {
        item.table_options.height -= 200;
        item.invoice_table = task.invoice_table.copy();
        item.invoice_table.paginate = false;
        item.invoice_table.create_table(item.view_form.find('.view-detail'), {
            height: 200,
            summary_fields: ['date', 'total'],
            on_dblclick: function() {
                show_invoice(item.invoice_table);
            }
        });
        item.alert('Double-click the record in the bottom table ' +
          'to see the invoice in which the track was sold.');
    }
}

var scroll_timeout;

function on_after_scroll(item) {
    if (!item.lookup_field && item.view_form.length) {
        clearTimeout(scroll_timeout);
        scroll_timeout = setTimeout(
            function() {
                if (item.rec_count) {
                    item.invoice_table.set_where({track: item.id.value});
                    item.invoice_table.set_order_by(['-invoice_date']);
                    item.invoice_table.open(true);
                }
                else {
                    item.invoice_table.close();
                }
            },
            100
        );
    }
}

function show_invoice(invoice_table) {
    var invoices = task.invoices.copy();
    invoices.set_where({id: invoice_table.master_rec_id.value});
    invoices.open(function(i) {
        i.edit_options.modeless = false;
        i.can_modify = false;
        i.invoice_table.on_after_open = function(t) {
            t.locate('id', invoice_table.id.value);
        };
        i.edit_record();
    });
}