Autofilling table column based on values filtered from a different table

App Building
1

Hello,

I’m trying to populate a column with values from another table that have been filtered and then summed.

The screenshot shows 2 data sources:

  • a table called runTable has a column called totalSamples that I am trying to automatically populate

  • a query called getSamples loads a resource table called projects, shown in the bottom right.

To calculate the value of the totalSamples column in runTable, I’d like to use the string in the Run ID column in runTable to filter the getSamples output. Then in the resulting getSamples output, I’d like to sum the column called sample_number.

In this example, the totalSample column should fill to 50.

Should I be looking to transformers to accomplish this? Any help is much appreciated!

Autofill Total Samples
Autofill Total Samples1920×1202 143 KB

2

Hi @DylanSeq

Welcome! Like most problems, there's a lot of ways to approach this and someone may have a more eloquent or efficient way of doing it, but I would approach this with JavaScript in the Value field of the totalSamples column. If I'm understanding correctly you're taking the ID from the runTable and trying to add all the sample_numbers from getSamples that have the same run number. I can't see the ID of the 'Run ID' from the table so replace that with the runID below, but paste this into the Value field of the totalSamples column and it should get your really close:

{{ _.sum(formatDataAsArray(getSamples.data).filter( x => x.run === currentSourceRow.runID).map(y => y.sample_number)) }}

image
image476×644 30.6 KB

Hope this helps!

1 Like
3

If you don't need the rest of the data from getSamples I'd do the summing in the SQL.

select
   run,
   sum(sample_number) as total
from
   projects
where
   run != ''
group by 1

This would give you a row for each run and the summed sample_number. Then you'd just map the totalSamples column to that value.

{{getSamples.data.find(x=>x.run === currentSourceRow.runID).total}}

If the dataset is large I'd consider joining your two tables in SQL and not doing any summing or mapping on the frontend, as they'll not scale very well. These approaches are fine for 15 rows though.

4

Hi,

Thanks for the help! The first solution is working well @Matt_N

What does the currentSourceRow command do? I couldn’t find much information on it.

Thanks,
Dylan

5

Within the table component currentSourceRow can be used to reference "the underlying source rows data".... pulled from here:

Basically in a table its a way to reference each row in the table - handy for a problem like yours where you're trying to manipulate data by row.

Here's an example of referencing a customer_name column, and adding text to the end of it:
retool_currSrcRow

In my original code we're using the currentSourceRow.runID (runID of each row in the runTable) to filter the getSamples data.

Hopefully that helps, and doesn't make it more confusing! :grin:

1 Like