1. NOTICE: If you are using Fabrik and update to Joomla 3.10, you will need to update to Fabrik 3.10. And, if you are using Fabrik, do not upgrade to Joomla 4, we do not have a supported version ready for release. More information on a release date coming soon. Also, please note that Fabrik 3.10 will not install on any Joomla sites less than 3.8.
    Dismiss Notice

Joining Lists

Discussion in 'Community' started by bespokeappstt, Sep 8, 2021.

  1. bespokeappstt

    bespokeappstt Member

    Level: Community
    If I have 3 list and the only common element is a customer name. How do I create a list that will have all 3 list joined and grouped by Customer Name?
     
  2. juuser

    juuser Well-Known Member

    Level: Community
    In Fabrik joins one of the join columns must be a Primary key.

    So it is highly recommended you make some data restructuring in your tables like having separate Customers table and in other tables dbjoin elements referring to Customers table id element.
     
    troester likes this.
  3. bespokeappstt

    bespokeappstt Member

    Level: Community
    That's good news. I do have a central customer list and all customers in any other list pulls from a dbjoin. Hopefully implementing the actual join is not complicated.
     
  4. bespokeappstt

    bespokeappstt Member

    Level: Community
    Using a Fabrik Join or creating a View on the database and then import it as a list. Which is the better way to go?
     
  5. juuser

    juuser Well-Known Member

    Level: Community
    Fabrik joins are recommended in most cases as mysql views are generally considered as read-only although with some exceptions.
     
  6. bespokeappstt

    bespokeappstt Member

    Level: Community
    Well yes, the data in the view in this case is to be used only for viewing. It was intended to be used with visualizations. So it's really 'read only' data. Should I use it?
     
  7. juuser

    juuser Well-Known Member

    Level: Community
    In normal cases I always use list joins which is much more straightforward (for me). If it's only read-only and you feel comfortable with mysql views, then you can of course use them.
     
  8. bespokeappstt

    bespokeappstt Member

    Level: Community
    Okay, suppose I want to create a 'joined' list but I want this to be a separate list. So I have list A and list B. I want the look of those lists and their form layout to remain as is, unchanged. But I want to create a new list C which will be list A joined to list B. Can I do that? Looking at the wiki here https://fabrikar.com/forums/index.php?wiki/list-joins/ . It looks as though you're modifying the original list so the list view changes. I want to create a brand new list that comprises of two previous list. How do I do that?
     
  9. bespokeappstt

    bespokeappstt Member

    Level: Community
    bumped into a little issue that i'd rather not deal with right now. The Fabrik dbjoin that renders as a checkbox, those values are not showing up on mysql backend, at least using the phpmyadmin. I figure it's a Fabrik thing and I'll avoid it for now if I can get what I want from Fabrik. Thus the new question.
     
  10. bespokeappstt

    bespokeappstt Member

    Level: Community
    In the phpmyadmin the Fabrik dbjoins look like:
    upload_2021-9-15_15-47-16.png

    That is for the column 'serial' in the table rad_rentals which is the list rad_rentals in Fabrik. A normal select statement
    Code (Text):

    SELECT * FROM `rad_rentals`
     
    shows 'serial' as null
    upload_2021-9-15_15-50-45.png
    Just mentioning it in case someone knows how to pluck out the data. But my main question still stands. Can I create a new and separate list from joining two lists?
     
  11. lousyfool

    lousyfool Active Member

    Level: Community
    As for multi-select dbjoins such as checkboxes and multi-select dropdowns: correct, Fabrik leaves the cell in the "main table" empty, but creates a new "repeat" table where the values are stored.
     
  12. lousyfool

    lousyfool Active Member

    Level: Community
    You don't want or need a "brand-new" list (from scratch and without any data).

    Instead, copy e.g. list A, then use a list join (as per Wiki link you provided). This "copy of A joined with B" is just as good as the list C you're imagining.
     
  13. bespokeappstt

    bespokeappstt Member

    Level: Community
    @lousyfool, thank you for that info. I can now create the view on the db end.
     
  14. bespokeappstt

    bespokeappstt Member

    Level: Community
    Yes please. I would like to create a separate list, called C and the data in C is joined data of A and B. So I want to join list A and list B but as a new list.
     
  15. bespokeappstt

    bespokeappstt Member

    Level: Community
    Should I just create a new list, by choosing the same table and then put the join on this new list ?
     
  16. juuser

    juuser Well-Known Member

    Level: Community
    Like lousyfool said, copy the list A and join list B to a newly copied list. For that, in the backend check list A and then press Copy button above.

    If you create a new list and point it to the same db table, you will lose some element settings. So use the "copy" method instead.
     
    Last edited: Sep 15, 2021
    lousyfool likes this.
  17. bespokeappstt

    bespokeappstt Member

    Level: Community
    I'm going the way of the mysql View on the back end. Closing this for now. I like to know all sides so I asked all the questions.
     

Share This Page