Discussions

Expand all | Collapse all

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

QuickBaseCoach Dev./Training05-30-2018 20:55

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

    Posted 05-18-2018 17:15
    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.


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

    Posted 05-19-2018 01:19
    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.


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

    Posted 05-25-2018 21:12
    Thank you for the response!

    I'm not sure Quick Base Actions are the best solution because we need to not just add records to the centralized table but also modify/delete them when the corresponding record in one of the 20 tables is modified/deleted. Is it possible to do this with a QuickBase Action?

    And for the "formula to link back to the original document," what formula would you use?


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

    Posted 05-26-2018 11:02
    The centralized table could have a relationship back to each of the 20 source tables. So 20 relationships. Then it could self detect if the Parent was deleted and either be hidden from reports or deleted by an Action or automation.

    That could also be useful to use a lookup for any Text fields on the source records, that way you would not need to deal with edits by Actions as they would just be lookup fields.


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

    Posted 05-26-2018 11:06
    The formula URL to display a clickable link to a file attchment in another table is like this

    URLRoot() & "up/" & [_DBID_PROJECTS] & "/a/r" & [Related project] & "/e30/v0"

    Replace e30 with e and fid of the field holding the file attachment. The v0 just says to always show the most recent version if the attached file.

    .


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

    Posted 05-26-2018 11:09
    You can get this working once and do all your testing and write down the steps.

    Then take say 2 hours max to replicate this 19 more times. Note also that you can �Copy Table�. That can save a bit of time.


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

    Posted 05-30-2018 16:23
    I appreciate the idea. But it seems that now we are straying away from the main goal.

    Originally, I wanted to gather records from all 20 tables into one centralized table with the goal of running a report using the filter "<some attached file> contains the word <ask the user>." But with your idea I would have a field of links, not file attachments. These would not be searchable.

    Do you know of a way to go around this?


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

    Posted 05-30-2018 17:05
    The link would appear as the name of the file and hence quite searchable. 


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

    Posted 05-30-2018 17:15
    But the file attachment (the content within the file attachment) will not be searchable on the centralized table.


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

    Posted 05-30-2018 17:28
    Oh, so you want to search to content of the file attachments and not just the name.  That is a showstopper.

    your original post said this

    "report that searched the file attachment field using "contains the value <ask the user>" filter."

    I assumed that you meant the file name and not to search the content of the file attachments themselves.


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

    Posted 05-30-2018 18:33
    >That is a showstopper.

    Hardly.

    >Do you know of a way to go around this?

    With script you could just create 20 hidden <iframe>s with the individual file content search results and then just scrape out the results and display the final result in a tabular format on the host page. The net result would appear as a single report on the host page spanning all 20 tables of documents. 


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

    Posted 05-30-2018 19:32
    Sorry for the confusion. Yes, I am trying to search the content of the file attachments. Is there a native-to-Quick-Base way of gathering all the file attachments on one table, a table that updates automatically, to achieve that?

    In response to the <iframes>s, I can't quite picture how the report would work. When you say "individual file content search results," are you imagining one search bar at the top of the hosted page that would then show the results below as a single report (even though they are 20 individual <iframe>s)? And how labor intensive would it be to set this up?


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

    Posted 05-30-2018 20:48
    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.


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

    Posted 05-30-2018 21:13
    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?


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

    Posted 05-30-2018 21:37
    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.


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

    Posted 06-01-2018 14:01
    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). 


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

    Posted 06-01-2018 14:28
    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.


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

    Posted 05-30-2018 20:53
    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?


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

    Posted 05-30-2018 20:55
    Please post a new question.