Generating a list from pieces of other lists

B

bespokeappstt

Hubstaff freelancer
I have four lists, lets call them A, B, C and D. List A has a unique number that is pulled as a multiple select dbjoin in B, single in C and D. B, C and D have bits of important information that are associated with the oringial unique number generated in A. How can I generate another list (E), that will have the bits and pieces of information of B,C and D associated with each unique number from A? Hopefully this is not too difficult.
 
@juuser I chose the first option and created a copy of List A. I naturally thought this would create a new table but I'm seeing the same table for the copied list? Is this supposed to happen?
 
Freight OPS is the original list and Freight OPS Status is the copied list:
upload_2022-1-24_14-27-8.png

The second list(list B) is called clearance and this is what the data looks like:
The vendor PO comes from the Freight OPS and it is a multiple select db join with a concat:
upload_2022-1-24_14-30-10.png

This is the join:
upload_2022-1-24_14-30-58.png

Thus far the data is empty:
upload_2022-1-24_14-31-34.png
 
This is what Freight OPS looks like:
Before the join the Freight OPS Status is identical to the Freight OPS. When I join, it becomes empty.

upload_2022-1-24_14-36-14.png
 
One "leg" of a join must be a primary key. If you are using Fabrik defaults it's the "id" element (element type internalid) , which has to be also used in your dbjoin value (id[recommended])
 
Even when I use the PK (id) it doesn't work. Even though it's not working, another question is, all I have to do is keep adding joins for the other list until I get all the pieces of info into this one list? This is the new config for the join that I tried and still no data.
upload_2022-1-25_11-40-44.png
 
Yes, it will create a copy referring to the same database table. Otherwise you would not have any data in your list (from List A).
@juuser I chose the first option and created a copy of List A. I naturally thought this would create a new table but I'm seeing the same table for the copied list? Is this supposed to happen?
Click to expand...

Didn't dig in deeper in your setup, but in most cases you would want to use LEFT join instead of INNER JOIN.
 
Last edited:
The left join only give info on the first List, in this case freight ops:
upload_2022-1-25_14-19-16.png

the right join only give data on the clearance list:
upload_2022-1-25_14-19-56.png

And the inner join is blank. Any suggestions? I need bits of info from all the lists. So right now it's just the freight ops and the clearance I'm using. Left join shows the freight ops and right join shows clearance. Now it's just to really join them. Thought 'inner join' would have done it.
 
Last edited:
Is there a memory issue with Joins? Sometimes even when I'm looking at the left join or the right join, the data take a while to load. Do I need a specific value for php.ini for better performance?
 
Might seem like a stupid question, but are you sure you have records in "a_clearance" table where the field "vendorpro" has matching values from "a_freight_ops" table id field? Otherwise LEFT JOIN for this setup should definitely work if I haven't missed something obvious.

About performance, it shouldn't be an issue with your type of setup unless you test in localhost or have a really lousy hosting service. Things that might slow down loading times are e.g. if you have created the tables manually, so Fabrik hasn't created proper indexes, you have many calc elements calculated on the fly ("Only calc on save" set to "No") etc.
 
@juuser I am using localhost and 'Output buffering' was on, so I guess this was my bad. Issue addressed. The "a_clearance" table has 5 matching values from the "a_freight_ops" table. They are, 99875642, 125469, 234567,99875642, 8888888888. Here are those numbers in the "a_freight_ops_" table:
upload_2022-1-29_11-38-37.png

And here they are in the 'a_clearance" table udner the column "Vendor PO#" which is table column "vendorpo". As you can see 234567 repeats in the 'a_clearance' table. "Vendor PO#" is a multi-select dropdown dbjoin, that has a concat on 'Vendor PO' and 'Customer' from 'a_freight_ops'.
upload_2022-1-29_11-41-20.png

Last but not least, here is my join:
upload_2022-1-29_11-43-46.png
 
OK, "multi-select dropdown dbjoin" is the key here. If multi-select is enabled, Fabrik creates a separate repeat table for the values like "tablename_repeat_elementname" . So you should join to this table's parent_id field instead. And then another join from the repeat table to your a_clearance table.

I don't have access to similar test case atm, so I cannot test how it actually works.
 
Okay I made some changes but it doesn't work. Where did I go wrong? This is my assessment and don't worry as the list is spelt 'clearance' but the table is 'clearence'

Tables 'a_freight_ops' and 'a_clearence_repeat_vendor_po' are linked via 'a_freight_ops.id'='a_clearence_repeat_vendor_po.vendorpo'

and tables 'a_clearence_repeat_vendor_po' and 'a_clearence' are linked via
'a_clearence_repeat_vendor_po.parent_id'='a_clearence.id'

I verified this when I ran the sql:
Code: 
Select frt.vendor_po, clr.docket_number

from a_clearence clr, a_freight_ops frt, a_clearence_repeat_vendorpo rpt

where frt.id=rpt.vendorpo and rpt.parent_id=clr.id

The output matches back with the 'Clearance' list info as shown:
upload_2022-1-29_13-54-40.png

This is what I have for the jons:
upload_2022-1-29_13-56-16.png

The page gives an error. What's wrong?
upload_2022-1-29_13-56-54.png
 
I didn't follow the complete thread.
But a repeatable join/group can never be 'from something to id', id is unique, PK...
 
You must log in or register to reply here.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    Total: 58 (members: 1, guests: 57)
    Back
    Top