Syncronizing two tables with dbjoin

Nov 15, 2013
Syncronizing two tables with dbjoin
  • Let's say you have two tables: actions and limitations and you have created lists on both.

    In actions list you create a databasejoin or cascading dropdown element rendered as checkboxes (or multiselect) pointing to the limitations table. Purpose - to be able to mark there all limitations corresponding to any action. Let give it the name limitations_id.

    As you just created a repeatelement, new database table actions_repeat_limitations_id is created with fields id_ parent_id, limitations_id and params. Field parent_id is the foreign key pointing to actions.id , another is fk for limitations.id.

    Now we suppose you want to do something similar in limitations list - to create dbjoin/CDD checkboxes/multiselect element actions_id pointing to the actions table so that you could mark all corresponding actions to any limitation.

    As you choose rendering as checkboxes/multiselect, new table limitations_repeat_actions_id is created (with fields id, parent_id, actions_id and params).

    Different tables behind the two elements - that means you should fill the checkboxes separately.
    But there is still a way to synchronize the two tables as they had one common bridge table behind the dbjoin elements! For that
    1. rename one of the '_repeat_' tables. E.g actions_repeat_limitations_id . Give it the name e.g actions_has_limitations.
    2. rename the field parent_id to actions_id (or something else whatever you want)
    3. create an updateable view (that means with cascaded check option) selecting all fields from actions_has_limitations:
      • select id, actions_id as parent_id, limitations_id, params from actions_has_limitations
      • and name it as the initial table - actions_repeat_limitations_id
    4. delete (or rename) limitations_repeat_actions_id
    5. create an updateable view with the same name (limitations_repeat_actions_id) like you did above (except you have to select limitations_id as parent_id)
    NOTE: Don't include data from any other table (and field) to these db views, otherwise they won't work as expected!

    Done! Your two tables are synchronized! When you open actions form and check all limitations corresponding to this action, then you will see the same action checked at each submission data. actions_has_limitations works as a main bridge and the newly created views are bridges between this main bridge and your two data tables.
joomlamate and waby like this.