Connected tables: Is it possible to set up a connection with 20 tables channeling data into one connected table?

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
Hello,

I am working with a document library.

There are 20 tables, and different users have permission to access different tables. All records have file attachments.

I would like to set up a connected table so that all records from the 20 tables sync into one large table. Is that possible? (A table-to-table import wouldn't be able to import the file attachments so the connected table seems like the best option.)

The end goal would then be to create a report that searched the file attachment field using "contains the value <ask the user>" filter.

Any advice?

Thank you.
Photo of HALEY

HALEY

  • 180 Points 100 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
A sync table can only have 1 source.  But you could have 20 Actions on 20 tables to create a record in the centralized table.  The centralized table can have a formula to link back to the original document such that wen clicked, it will display the file attachment even though the file attachment is not actually on the centralized table.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
Haley, there is not a native way to mirror the file attachments and actually copy them from one table to another..  if you have a strong enough need to I would suggest that Juiced Technologies, another QSP which specializes in add ons could probably do that for you.  Probably Trinity could also do it and probably others, but it would be custom scripting.

Dan's solution would work i assume, but you would need to contract with him to do that for you.  His stuff imho is not for beginners.  The other risk with Dan's solution is that the system would be doing 20 searches, so that would take 20 times as ling to complete as a search on a single table.  So there could be a delay for all that to finish, and also, it might slow down the app for other users while that was processing.
Photo of HALEY

HALEY

  • 180 Points 100 badge 2x thumb
OK, I appreciate your help.

One last attempt: if I were to set up 20 relationships, a "Keywords" lookup field for each relationship (words separated by commas, or just words without commas), and run a report on the keywords. Do you have recommendations on how to make the search more effective? So that a person looking for a document can type in a chain of words and actually get a few relevant results?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
OK, so back to a native solution - I'm all about "Native".

Yes, that will work, but it would mean that you would need to have the users enter key words manually and that does not sound very practical.  The search can include the file name,  and the description of the document (usually these document type tables have a document description field, and you could also have a field for key words.  They would be all searched.

The fastest way would then be to have the user type into the traditional search box at the top of the report and the report will filter as they type.

You can try this approach with just a couple of tables to get it working and confirm the approach.

The other approach is more radical.  Ask yourself why you need 20 tables.

Make just 1 table and use Role Permissions to control who can see and edit records based on the document type.  But then to need to lock someone in a room for a day to transfer the file to the new table.  If there were say 500 records, then at the rate of say 2 minutes each that would be 1,000 minutes.  I have no idea if you have hundreds or thousand of files.
Photo of HALEY

HALEY

  • 180 Points 100 badge 2x thumb
Thank you for your help and responses! 

I decided to manually transfer all records and attachments to one table and then set up the necessary reporting that way. It's cleaner this way and I'm using native Quick Base functionality.

It would be nice if Quick Base added the report option to search <some attached file> and <some field> at the same time, from one string of text entered by the user (one search bar). 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,326 Points 50k badge 2x thumb
I’ agree. A native combo search would be nice.

I believe I have an app where I had Dan Diebolt set up a combo search for me.
Photo of Taryn Cuningham

Taryn Cuningham

  • 80 Points 75 badge 2x thumb
With this being the topic. I'm having some trouble. I am working on taking two tables and combining them into one. Is there an easier way of doing this without having to connect relationships and creating a new table?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 48,596 Points 20k badge 2x thumb
Please post a new question.