Best Formula Configuration

[Nathan_Vandermo]

Greetings, I put together a video that illustrates what I am looking for.

Transcript of video:

Greetings or table community. So what I’m trying to do is I’m trying to have this column, the leads balance column reflect the amount of leads that are left.
So we can see that the, each, each row is a new lead and it’s organized by the date. And we can also see that we have our two columns here, requests credit and credit issued.
So the workflow is that a client would potentially request a credit just by selecting the column and by selecting. Yes.
And if they select, yes, then I ideally would get an email notification and then would have to request a credit.
Once the credit is approved, I would place a one in this column. And then that would show the balance here.
So in the, our leads balance, I’m using the customized field type, I’m using the formula and I’m trying to figure out what formula to use.
I’m used to Excel some, having some difficulty trying to figure this out. So like for example the first column there would be 49 49 leads.
Let me see here. Okay. Let me just change the field type, just to give an example, is that right. So the first one, there would be 49, no, sorry, 49.
And then this would be, would still be 49 because this lead was generated, but one issue was credit was credited saying here same for this cell.
And then here, this would be 48 so on and so forth. So, so just to reiterate, I’m looking for a formula for the leads balance.
So right here for the, I’m looking to see what the answer would be right here. So any feedback would be very much appreciated.
Thank you so much.

[Justin_Barrett]

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.

[Nathan_Vandermo]

@Justin_Barrett - Thanks for your feedback! Very helpful!

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

Any other suggestions are appreciated!

[Justin_Barrett]

@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.