Skip to main content

I am trying to solve the following problem:


One table lists all our available products, another table lists projects/jobs in which our products were used and sold for. A rollup on the inventory table counts how many times each product was sold.


But here is the problem - in the project/job table I have an option column that allows me to list a job either as “bid” or as “actual sale”. A custom view in that project table allows me to easily view bids and sales, but the rollup in the inventory table counts both options, bids and sales.


Is there a way to tell the rollup to only count sales and ignore bids? That to me sounds like a pretty standard database function, but I cannot figure out how to do it in Airtable.


I am really grateful for any advice.

Markus – the way to do this right now would be to create another field on the “Jobs” table that is a formula. You could make it something like this:

IF(Type = “Sale”, Amount, 0)


So this field would be filled with either the sale amount (duplicating the actual “Amount” field), or zero, depending on whether the job was a bid or an actual sale.


You could then apply the rollup field on that formula field, rather than the “Amount” field directly. Does that make sense?

Markus – the way to do this right now would be to create another field on the “Jobs” table that is a formula. You could make it something like this:

IF(Type = “Sale”, Amount, 0)


So this field would be filled with either the sale amount (duplicating the actual “Amount” field), or zero, depending on whether the job was a bid or an actual sale.


You could then apply the rollup field on that formula field, rather than the “Amount” field directly. Does that make sense?


Thanks Howie,


that makes sense for counting the money coming in. But what I want to do is count the number times our products were actually used/sold. They can be resold again and again - since we are only selling licenses that are not exclusive.


On the jobs table I list all the (linked) products that were used in a particular job and since they can be sold/licensed many times they show up multiple times in various jobs. On the inventory table, there is a column (rollup) showing a number that tells me how many times a specific product has been sold/licensed. On the same table I also have a column that lists all the jobs each product was used in/licensed for. But right now that also includes bids.


Could your solution also work for what I am trying to do?


Thanks in advance.

Thanks Howie,


that makes sense for counting the money coming in. But what I want to do is count the number times our products were actually used/sold. They can be resold again and again - since we are only selling licenses that are not exclusive.


On the jobs table I list all the (linked) products that were used in a particular job and since they can be sold/licensed many times they show up multiple times in various jobs. On the inventory table, there is a column (rollup) showing a number that tells me how many times a specific product has been sold/licensed. On the same table I also have a column that lists all the jobs each product was used in/licensed for. But right now that also includes bids.


Could your solution also work for what I am trying to do?


Thanks in advance.


Yes, you can achieve that with the same formula + rollup approach. Feel free to email support@airtable.com if you’d like to jump on a screenshare!

Yes, you can achieve that with the same formula + rollup approach. Feel free to email support@airtable.com if you’d like to jump on a screenshare!


I just did. Thank you in advance.

I have a similar, but slightly different question:


I need to be able to rollup only the most recent submission. I could use your suggested answer to @Markus_Wernig if I could use full spreadsheet-like formulas eg:

=IF( Date = MAX( FILTER( Date, Name={THIS NAME} ) ) , Availability, “”)


Is this capability planned? Do you have any other suggested work-arounds? Is it possible to roll-up only the most recent submission?

Yes, you can achieve that with the same formula + rollup approach. Feel free to email support@airtable.com if you’d like to jump on a screenshare!


But aggregate fields such as roll-up, count and lookup, need to have a condition able to compare with the values in any column of the linked table. Adding field is not a suitable approach in case of multiple values to roll-up, for it is time consuming for structuring the base.

Arrived here from Google search, so posting for everyone that follows:

this is now a feature → https://support.airtable.com/hc/en-us/articles/360046636794-Conditional-counts-lookups-and-rollups

Reply


Terms & Conditions