======================
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 
:doc:`view_form </refs/client/item/at_view_form>`
is defined in the 
:doc:`on_view_form_created </refs/client/task/on_view_form_created>`
event handler declared in the task client module.

We will change it in the
:doc:`on_view_form_created </refs/client/item/on_view_form_created>`
event handler in the tracks client module

.. code-block:: py

  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

.. code-block:: py

  item.table_options.height -= 200;
  
create a 
:doc:`copy </refs/client/item/m_copy>`
of invoice_table, set its 
:doc:`paginate </refs/client/item/at_paginate>` attribute to false and call the 
:doc:`create_table </refs/client/item/m_create_table>`
method to create a table that will display the sold tracks

.. code-block:: py

  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 
:doc:`on_after_scroll </refs/client/item/on_after_scroll>`
event handler:


.. code-block:: py

  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 
:doc:`on_after_scroll </refs/client/item/on_after_scroll>`
event is triggered whenever the current record is changed.
So when the track is changed we call
:doc:`open </refs/client/item/m_open>`
method, pre-setting the filter and order 

.. code-block:: js

  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
:doc:`close </refs/client/item/m_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 
:doc:`on_view_form_created </refs/client/item/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:

.. code-block:: js

  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 
:doc:`detail </programming/data/details>` 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 
:doc:`create_table </refs/client/item/m_create_table>`
method the function that will be executed
when user double click the record:

.. code-block:: js

  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:

.. code-block:: js

  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
:doc:`edit_record </refs/client/item/m_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 
:doc:`on_after_open </refs/client/item/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
:doc:`locate </refs/client/item/m_locate>`
method.

Finally we will check the 
:doc:`lookup_field </refs/client/item/at_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 
:doc:`on_view_form_created </refs/client/item/on_view_form_created>`
will be as follows:

.. code-block:: js

  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();
      });
  }

.. image:: _images/two_tables.png
	:align: center
	:alt: two_tables.png