1. Fabrik 3.9 has been released. If you have updated Joomla to 3.9, this is a required update.
    Dismiss Notice
  2. If you update to Joomla 3.9.16, you will have to update Fabrik from the latest GitHub version, to fix a bug introduced in Joomla, which makes it impossible to list front end folder locations in backend JForms. This affects things like template selection.
    Dismiss Notice

Database join plus cascading dropdown

Discussion in 'Community' started by mauna, Jun 5, 2009.

  1. mauna

    mauna New Member

    Level: Community
    Hello everybody,

    I'm a new subscriber, and here is my question.

    I want to use Fabrik plugin to publish something like the classic Microsoft Access "Northwind" database.

    To make it easier, I have three tables with the following fields and "one-to-many" joins

    CustomersTable
    CustomerID
    CustomerDescription

    OrdersTable
    OrderID
    OrderDescription
    CustomerID --> CustomersTable__CustomerID

    OrderDetailsTable
    OrderDetailID
    OrderDetailDescription
    OrderID --> OrdersTable__OrderID

    Now, using Fabrik plugin, I have created an "OrderDetailsTable" table starting from the database "OrderDetailsTable" and I get an "OrderDetailsTable" group too.

    Within the "OrderDetailsTable" group, I define the "OrderID" element as "database join" element pointing to the "OrdersTable" table with Key = "OrderID", Label "OrderDescription" and Filter type = "dropdown".

    All works fine and I see a dropdown showing all the "OrderDescription" and a table of details showing the "OrderDetails" filtered on the "OrderID" selected within the dropdown.
    If I change the value within the dropdown, the values within the rows of the table change too.

    Now I want to filter the "Orders" on their "Customer".
    So, within the "OrderDetailsTable" group, I define the "CustomerID" element as "database join" element pointing to the "CustomersTable" with Key = "CustomerID", Label "CustomerDescription" and Filter type = "dropdown".
    I also change the "OrderID" element from "database join" to "cascading dropdown" pointing to the "OrdersTable" with ID = "OrderID", Label "OrderDescription", Watch element = "CustomerID", Foreign Key = "CustomerID" and Filter type = "dropdown".

    Now I see two dropdowns: the first one showing all the "CustomerDescription" and the second one all the "OrderDescription" related to the selected "Customer" within the first dropdown but the table of details doesn't show the "OrderDetails" related to the second dropdown value.

    If now I change the first dropdown to the "Please select" value then the second dropdown becomes empty as expected but the table of details is now filled with the "OrderDetails" related to the previously selected value in the second dropdown.
    Am I programming wrong or there is a bug?

    Thanks in advance.
     
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    Are you using 'on change' or 'on submit' as your filter trigger for the table (i.e. do you have a Go button)?

    If you are using 'on change', try using the submit option so you have a Go button, see if that helps.

    -- hugh
     
  3. mauna

    mauna New Member

    Level: Community
    Hi, thanks for your help but it doesn't solve my problem.
    In the meantime, I have thought that it wants that both the filtering values must be present in the filtered table.

    So, I created a view, named "viewOrderDetailsCustomers", joining the CustomerID to each row of the OrderDetailsTable and than I have redone all the previous steps using this view instead of the "OrderDetailsTable".
    As I supposed, all works and the table of details is now filled with the "OrderDetails" related to the OrderID I select.
    Well, you could say, here is your solution but, unfortunately, now I have two new problems:
    1 - if I set the property "Link to Details" of the "OrderDetailDescription" element I don't see the "Details" while I see them if I set the property "Detailed view link" of the "table" "viewOrderDetailsCustomers"
    2 - even if I would use the workaround at point one I'm not able to make any changes to the record because, obviously, I am working on a view not on a table.
    I hope this could be useful.

    On the fly, do you know where the sql statements created by the plugin are stored?

    Thanks, bye
     
  4. rob

    rob Administrator Staff Member

    Level: Community
    can you point us a the page where this is happening?

    In the fabrik table that connects to the mySQL view do you have a pk selected?
    Are you using the latest nightly build?

    The statements are built on the fly each time the tables are viewed, in the table.php model - _buildQuery() method



    Cheers
    Rob
     
  5. mauna

    mauna New Member

    Level: Community
    Hi Rob.
    I have downloaded on 28 of May.
    By the way, is there a page where I can read the running fabrik version and its components?
    Sorry, I'm working on a virtual server on my PC, however tell me if there are some files that I can send you.

    Yes, in the fabrik table that connects to the mySQL view I have set a pk equal to the OrderDetailID pk of OrderDetailsTable.
    bye
     
  6. mauna

    mauna New Member

    Level: Community
    Hi Rob.
    I have downloaded 1866 revision and:

    1 - I think I have understood why I am not able to filter my table "OrderDetailsTable" by "CustomerID" and "OrderID": when I create the new element "CustomerID" it is added as a new column to the "OrderDetailsTable" (I suppose it was so in the previous revision too) but in this way it is always empty so the table can't be filtered by the value selected in the dropdown "CustomerID", it will never match. But, for me, this is a wrong way to manage a one to many relationship

    2 - when I set the "OrderID" element as cascading dropdown now I can select only the current "OrderDetailsTable" not any other, so I can't link it to the "CustomerID" "database join"

    Hoping this could be useful,
    bye
     

Share This Page