Pull data from multiple tables into primary table as tags with multiple entries

[amyberit]

Hello smart friends 🙂

I've got a base I'm building to track companies within a community with their contact info and other demographic data listed.

I'm pulling data from two different sources into the base (displayed as one primary table and 14 other segmented sublist tables) and need to incorporate the data in the following way:

- The primary / working list that has the entire list of companies in it needs to have the ability to be segmented by "affinity" tags

- The data to determine those tags is in each of the segmented lists, each of which has a handful of entries from the primary list but sorted by a particular affinity, i.e. "black-owned" or "women-owned"

- And since some companies will have multiple affinities, i.e. being both "black-owned" and "women-owned", the tag column needs to be able to account for multiple entries coming potentially from each of the indiviual segmented sublists. 

I can see a way to link a record from one table to the other, but that only seems to offer the option to pull over data from ONE table, not to search for the same business name across ALL sublists.

My sense is that I need some sort of formula that says something akin to "if X business name appears in tables 2-14 and matches, tag accordingly" but am clueless if this is even a thing that could be set up 🙂

Any suggestions are welcomed and appreciated... Many thanks!

[TheTimeSavingCo]

Can you provide some screenshots of your data now and an example of how you'd want the final output to look like? 

[amyberit]

Thanks for popping in to help! Here's a loom video that might be an easier way to describe what I'm trying to accomplish...

[TheTimeSavingCo]

Ahh yeah that helps a lot!  How to tackle this depends on how much automation you'd want I think.  A fully manual way to handle this would be:
1. In each of the tables besides the main table, create a linked field to the main table
2. In each of those, click the field header of the primary field so that the entire column is selected and hit CMD/CTRL+C
3. Click the field header of the linked field to the main table and hit CMD / CTRL + V
  - This'll link stuff up appropriately
4. In the main table, create formula fields to check whether the appropriate linked field is populated.  E.g. if the linked field to "Family Owned" is populated, make it output the tag you want
5. In the main table, create one more formula field that will consolidate all the formula fields created in step 4
6. Paste the value of the formula field from step 5 into the "Affinity Segment" linked field

If you want it to be fully automated, you could do this with one automation per table type, e.g. one for family owned, one for employee based etc, and the automation would basically look for the record in the respective table and update things accordingly

The final version of this would be to just write a script for this and it'd just take care of it for you.  It's a relatively simple script to write and if you DM me an invite to an example base of yours I can put it together for you.  The other two options would work, but they're kind of a pain to set up while the script is relatively simple to write so I figure I'll help where I can

You'd need to get a JavaScript developer to help you make changes in the future though so you may want to bite the bullet and do the non-script ones instead if you foresee changes being needed!

[alvitubit23]

1. Identify the primary table where the tags will be aggregated.
2. Determine the relevant tables containing the data to be pulled into the primary table.
3. Establish the relationship between the primary table and the secondary tables using common identifiers.
4. Use SQL queries or data integration tools to extract data from the secondary tables.
5. Transform the extracted data into tags format suitable for insertion into the primary table.
6. Implement logic to handle pdf cases where multiple entries exist for a single tag.
7. Ensure data integrity and consistency during the aggregation process.
8. Validate the accuracy of the extracted tags against the source tables.
9. Apply any necessary filtering or sorting criteria to refine the tags.
10. Test the integration thoroughly to identify and resolve any potential issues.
11. Document the process for future reference and maintenance.
12. Utilize pdf documents for further reference on data integration techniques and SQL queries, ensuring comprehensive understanding and proficiency in database management.