4 Connected Tables Merged into 1 Table

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • In Progress
  • (Edited)
Here is a fun one...
I have 4 simple document tables coming in form 4 different apps. All have the same fields. I need to make one table from all of these tables. (not talking about files for any of these, just links / urls to files)

The first table has global project documents and the main Project ID. All of the other tables have documents for those projects, but from different teams' apps. Each one of these projects can end up with dozens of documents. 

I have them all connected down into a compiling app called The Hive, where I take multiple tables and compile them. But this many and multi problem is getting to me.

In the end, I would like to connect this merged table down to different production teams' apps, so that there is only one table being connected down with all of the documents from all 4 apps.
Photo of Meredith Moore

Meredith Moore

  • 556 Points 500 badge 2x thumb
  • Confused / Stuck

Posted 8 months ago

  • 0
  • 1
Photo of Debbie Taylor

Debbie Taylor

  • 664 Points 500 badge 2x thumb
HI

In Quick Base, you can assign people to production teams AND you can set permissions so that each person can only see (or edit) her/his team's documents. So, in The Hive

First
  • Create a Projects table
  • Create a Project Teams table.  This table has a relationship: one project to many teams.
  • Create a Documents table.   This table has a relationships: one project team to many documents.  

If you have global documents that you want all project teams to be able to access, create a project team called " Global Team."  Select this team when you add global documents.

Second - Decide how to handle permissions.

There are several different ways to set up permissions. 

A) If most people are on the same team all the time, you can create a role for each team.  Then, use the "Users" tab to assign people to roles.  And, grab a snack.  You are done creating table.

B) If people switch project teams frequently (or can be on multiple teams or need to access documents from previous teams), you need to build more tables.
  • Create a People table.  In addition to the text fields for names, create a field for user name.  This field lets Quick Base recognize who is logged in and tailor permissions.  Add a formula -numeric field with this formula-  If([QB User ID]=User(),1,0).  You can call the field "User as Numeric"
  • Create a People - Project Teams table.  This table should have 2 relationships:  One Person to many Project Teams.  One Project Team to many People.  Bring that "User as Numeric" field from the People table into this table as a lookup field.
  • Then, move that "User as Numeric" field into the Documents table as a look-up field from the Project Teams table.
  • And, finally, set the permissions on the Documents table so that users can only see records where User as Numeric equals "1."
CloudBase Services built systems like this for ATT, Pepsi and many other companies.  Contact us through our website for more help.

Debbie
Photo of Gaurav Sharma

Gaurav Sharma

  • 5,758 Points 5k badge 2x thumb
Hi Debbie,

Just want to confirm this point.

--->> Add a formula -numeric field with this formula-  If([QB User ID]=User(),1,0).  You can call the field "User as Numeric"

We have created the same formula but, used the formula-checkbox field and have seen some changes while using to determine the users for the permission.

Just wanted to double check with you.. what is the best approach to use formula-numeric field or formula checkbox? I know both give the same results but, what your's insights?

Thanks,
Gaurav
Photo of Meredith Moore

Meredith Moore

  • 556 Points 500 badge 2x thumb
Thanks, I am at AT&T, and this is not an ask about permissions, this is an ask about data merging.
Photo of Meredith Moore

Meredith Moore

  • 556 Points 500 badge 2x thumb
This isn not about permissions. This is about merging the data into one table. Here is a visual. (numbers indicate number of documents per global project number that are in each app.)
(Edited)
Photo of Debbie Taylor

Debbie Taylor

  • 664 Points 500 badge 2x thumb
Meredith -

Happy to help you - gratis, of course.  I'm still not understanding the sticking point.  You can reach me at myfirstname at cloudbaseservices dot com.

Debbie
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
Meredith,

You can have a formula URL button to push which would wipe the central table and then run sequentially 4 saved table to table imports with API_RunImport.  The target table would have a clickable link to display the attachment,  and perhaps a link back to the source record, but the file attachments would continue to live on the individual 4 tables.

But them someone needs ot push that button each day.

You could probably maintain the table automatically once loaded using an ACTION on each table.  Where that can get messy is to deal with edits to other fields in the record.  But I suppose that those can all be lookup fields, so that would take care of that.

You would need to deal with what if a user deleted a record, then an Action would need ot delete the corresponding record.  Ahh, but ACTIONS cannot delete.

So either they edit the record to flag it for deletion (and hide from users via permissions) or sign up for early access to Automations due to be in GA release on the May release.  Automations can delete. 
Photo of Meredith Moore

Meredith Moore

  • 556 Points 500 badge 2x thumb
My problem is that these are all connected tables (coming in from different apps) it seems to be a real speed bump. I am on the Automations early access. :)

Do you think they could come in and ordered by date creation? I'm really stretching in a way to sequence these things for import.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
Why do you care what order they get created in?
Photo of Meredith Moore

Meredith Moore

  • 556 Points 500 badge 2x thumb
I don't. I'm just trying to get them to all come together.