Best Way to Integrate Two Different Bases?

[Christopher_Can]

I have a main base with around 10,000 records and growing. Each record has as of now 78 fields.

Think of a list of people with a series of answers to questions.

I also have a series of bases (one for each year for three years) and within those bases are survey responses, with one tab for each survey.

Now, my issue is that the surveys ask questions that are only sent to people on the list of 10,000 records in the main base. The surveys all contain answers to different questions that aren’t in the main base.

If I went through and just added every single new answer to the right record in the main base, there would be hundreds of fields in totally. Maybe 300-400. This seems like it could be really unwieldy.

But at the same time I’d ultimately like this data to be connected to an entry in the main base in such a way that I could easily search through these answers (tied to the person back in the main base).

Right now there isn’t any link between an entry in the main base and a corresponding survey response, so any sifting would be a manual lookup.

Just curious if anyone has any ideas about best design principles here. How many fields is too many? Should I just have the one base with a whole bunch of fields? Is there something else I should do?

[Alexey_Gusev]

Hi,
At first, you may create second base for surveys, sync persons list there, maybe create multiple tables (one for each year or group other way, by subject of survey etc), where each field represent a question.

you may find it hard to do manually, but now tables can be created by script. even if you don’t know Javascript, having a list of questions, you may add formula (considering To concatenate double quotation marks, you need to use a backslash () as an escape character.).

'{name: \" ' & Question_Field & ' \", type: \"singleLineText\"},'
to transform in a list of colums for following

const tableId = await base.createTableAsync("Sometable", [

     {name: "Title", type: "singleLineText"},
     {name: "Second question", type: "singleLineText"},
     {name: "other field", type: "singleLineText"}
          
     ]);

then copypaste your field list (instead of “Title”,“Second question”,“other field” in my example), remove comma after last field, and run script

Also, you may read this article and think about join all your data in a single table with multiple views
https://support.airtable.com/hc/en-us/articles/360007520454-Combining-multiple-tables-into-one-table...

I have a tables with 2000 records and 160 fields and it works OK
I also have a tables with 25k records and 50 fields and it works good 95% of time, but i feel a little “freeze” when i perform field operations for all table or create/expand/collapse new grouping.
If you setup several views (with 50-100 fields per view), you table with 10k records may work OK, considering your work with a view, not the whole table.

[Justin_Barrett]

Alexey_Gusev:

To concatenate double quotation marks, you need to use a backslash () as an escape character.

Ironically for a backslash to appear in a forum post—which it didn’t in yours (guessing it was supposed to be inside the parentheses)—it must be escaped by itself. To get (\) you have to type (\\).

That aside, your comment isn’t entirely accurate. If your outer string is created with double quotes, then yes, double quotes inside that string need to be escaped. However, your example uses single quotes for the outer string, so no escaping is needed to add double quotes in that context.

'{name: "' & Question_Field & '", type: "singleLineText"},'