Nov 14, 2023 11:14 AM
I have a table of grants data for 20+ foundations, many of whom have overlapping grantees. I'd like to create a linked record that will pull the name of all the foundation that support a common grantee but cant figure out how to do it. Grateful for any tips/advice. Thanks!
Nov 14, 2023 12:37 PM
Hi,
If you have a field with comma-separated grantees, you need duplicate it, and then turn duplicate to a Linked field, and when choosing 'linked to', choose New table.
New table will contain a list of unique grantees, each linked to 1 or more foundations. By 'Add lookup fields' you can retrieve name(s) of a respective foundations for grantees
If the list of grantees is, for example, multi-select or lookup, try to create formula field and use ARRAYJOIN({Field}) or CONCATENATE({Field}) to get comma-separated. If they are separated, but not with comma, use SUBSTITUTE to change separator to comma.
Nov 14, 2023 01:01 PM
Thank you. Let me further describe the dataset I have and what i'm tying to do for better clarity. Each row in the primary table is for for a different grant. Column A lists the foundation and column B lists the grantee, with additional information like grant amount, grant term, etc. in the ensuing columns. When I group the sheet by grantee it's easy to see that several have 4 or more foundations that have given them grants.
I'm using the primary sheet in Airtable to inform fields in a Softr website and would like to be able to create a field that shows other foundations that have given grants to a specific grantee. Is there a way to do that by linking the primary Airtable sheet to another sheet or linking to specific fields in the primary sheet?