Auto-populating columns across tables

[Kellan_Baker]

I am using Airtable for a systematic literature review and have two tables in one base. In Table A, I am extracting individual outcomes from 300 studies, so this table can have multiple rows for a single study (e.g., Study C has 5 outcomes with data that need to be extracted, so Study C is represented in Table A with 5 rows). In Table B, I just want to keep an aggregate record on one row for each study, so I want to have a column in Table B titled “Outcomes” that is populated with all 5 of the outcomes in Table A, and so that if I change one of the outcomes in Table A, it changes in Table B as well. I have been experimenting with linked and look-up fields but haven’t been able to get it to work. Can anyone help me figure out how to do this?

[AlliAlosa]

Most importantly, you’ll want to make sure your tables are set up so that each Outcome is linked to one study on another table. Then you can use lookups and rollups to pull info over/make some calculations on the [Studies] table with the data from the [Outcomes] table.

If you explain a little more about what kind of data you’re trying to end up with or calculations you’re trying to make, I’m sure we can help further :slightly_smiling_face:

[Kellan_Baker]

Thanks so much for your reply. I want two things from these tables:

1. Using Table A, count the number of studies that have Outcome 1 (or 2, 3, etc.) and a “positive” finding for that outcome. For example, Study C assessed suicidality, and it found a significant association between anti-LGBT discrimination and suicidality. I don’t need to capture the effect size, just the fact that the association was positive.

2. Have Table B maintain an accurate accounting for all the data points spread out across Table A, so that with one glance I can remember which outcomes each study assessed.

In addition to the linking fields and look-up function, I am struggling with how to assign a primary key in Table A. Right now the primary key in Table A is “Study C,” “Study C copy,” “Study C copy copy,” etc. - one copy for each of the study’s outcomes. This seems wrong…

[AlliAlosa]

Hi there,

Thanks for the additional info! I can at least try to help with the table set-up, but without knowing what kind of data you’re extracting, it’s hard to give succinct advice. I put together the below example base to show how I would set up the two tables.

In the example base, I have 5 outcomes linked to Study C. I put in some bogus “p” values. Then on the [Studies] table, I have a rollup field that averages {p} together for each outcome. Next, a formula field compares {p} to {α} and returns either “Significant” or “Not Significant”. I haven’t taken statistics since college, so apologies if this is out of left field or just plain wrong statistical practice. :slightly_smiling_face:

Either way though, the relationship between tables is defined so that you can get any sort of data averaged or pulled out of the [Outcomes] table and displayed on the [Studies] table. If you’re comfortable sharing any sample data I’m happy to load that in to show how it could work.

Lastly - I would use a formula for the Primary Field in the [Outcomes] table so that the names are something like “Study C - Outcome 1”, etc. Click the link below and hit “copy base” to add this base to your workspace and check out the formulas yourself.

Airtable

Studies (Example Base) - Airtable

Explore the "Studies (Example Base)" base on Airtable.

[Kellan_Baker]

I’m so sorry for the delayed reply - I have been traveling and haven’t been able to touch this for a few days. This is super helpful, and I’ve been playing around with it, but I realized that I can’t see the formulas in the columns (I tried to add a screenshot but kept getting an error message: “you cannot add images to posts”). Could you advise how I can change the tables to be able to see the formulas?

[nea_lpatil]

@Kellan_Baker

Copy/Duplicate the base and in the resulting base you will be able to check formulas.

Copy-Base link can be found in the upper right part when you are accessing the base.

Neal

[Kellan_Baker]

I had done that, but it worked on the second try per your recommendation. Thanks!