If Statement from table A is X, then pull Column Y from Table A to Table B

Alrighty — hopefully I can explain this correctly –

So I have a table with all my Load information – the Master Table. I have a side table that has my Sales Reps info —contact, ppw, etc. What I would like to do is pull info from the Master Table - Load Information to my Sales Rep table. if the Sales Rep is Adam in the Master Table, then I want to take the corresponding Revenue Margin in the same row to pulled to the Sales Reps table and then ultimately added together so we can get a grand total of revenue this sales rep brought in.

Thanks guys!

What you want to do is configure a linked record field from the [SalesReps] table to the [Master] table. (I assume each record in [Master] logs a discrete sale for a single sales rep.) Instead of using a single select or text field to specify sales rep in [Master], you’ll select the correct linked record.

After that, in [SalesReps] you’ll define a rollup field that follows the link back to [Master] and references the {RevenueMargin} field using an aggregation function of SUM(values). That will take the values from all of the {RevenueMargin} fields linked to Adam’s record — that is, the ones where {SalesRep} = 'Adam' — and add them together.

If your data structure isn’t quite as I imagine, let us know; there are ways to get at the data.


P.S. If your current sales rep names in [Master] are the same as the names in the primary field of [SalesReps], you can simply change the field type for {Master::SalesRep} from single-line text to linked record, and it will automagically create the links you need.