Best Formula Configuration

Welcome to the community, @Nathan_Vandermost1! :grinning_face_with_big_eyes: Unfortunately a formula isn’t going to work. Unlike spreadsheet rows, Airtable records have no inherent knowledge of each other. Each record is a largely-independent entity. When executing a formula, the formula executes at the record level, and only has access to data from the record in which it’s running. That’s why your original formula only removed 1 each time. When Airtable encounters a field reference, each record only retrieves the value from that field in the same record. Using the SUM() function wrapper doesn’t tell the interpreter to collect the contents of the field from any other records.

In the past, some users have come up with fairly complex workarounds to do the kind of running total calculation that you’re looking for, but I don’t recommend going that route.

The main question that I have is this: is there any true value to seeing that number change row by row, or is it the final number that’s important? Looking at your setup, I can’t see the value in knowing that the number drops by one on some records and not on others, but I do see the value in knowing the total number of leads remaining out of the original pool of 50.

No matter how many records are in your table, you could calculate the number of remaining leads by setting the summary bar option at the bottom of the {Credit Issued} field to “Filled”. If you have 50 records and there are 40 with no credit applied, your balance is 10, and that matches the 10 records with something in the {Credit Issued} field. If you increase the record count to 75 records and 62 of them have no credit applied, that means 13 have credits, and you have 13 leads remaining.

Now, if you need to use that leads-remaining balance in other calculations elsewhere in the base, that’s not going to help because the summary bar value can’t be retrieved by anything (not even a script). However, I didn’t get the impression from your demo that this balance number was used for anything other than your own reference, and the summary bar gives you that.

On a side note, I would also suggest that you consider an alternative method of marking records as having credit applied. While your current method works, consider changing it to a date field. That would serve two purposes: the “Filled” summary count would still work to track how many fields contain dates, and the date itself provides you with a record of when the credit was applied. You can easily fill that field with the current date by using the CMD ; (Command-semicolon) hotkey.

@Justin_Barrett - Thanks for your feedback! Very helpful!

I recorded a folllow-up video with some additional feedback.

Any other suggestions are appreciated!

@Nathan_Vandermost1 Thanks for the detailed overview of your system! In terms of interfaces, getting a quick number count of filled records in that “Credit Issued” field is doable, but it’ll be a slightly different process.

In your interface, the settings for a Number element have two pieces that can help in different situations. Under the “Number” section, there are two choices: “Record count” and “Field summary”. For some things, the options under “Field summary” would work, but unfortunately there isn’t currently a “Filled” option (even though there is an “Empty” option; no idea why).

Thankfully there’s the option to add a filter to this element, and that’ll do the job. All you’ll need is a single filter option: where “Credit” is not empty. That will mimic what the summary bar’s “Filled” choice is doing in a grid view, giving you a count of all credited records, which is essentially also telling you how many un-credited leads remain.