If Formulas and Counting Down Formulas

[krista_krebs]

I’m super new to airtable and even if formulas… I’m looking for a formula that says basically

If the first item is A, then the units are 1
If the item is B, then the units are 2
If the items is C, then the units are 3…

AND THEN:

If we are logging calls and this is a billable situation: But if we are logging calls and we know there is a two hour allotment and each call reduces the allotment by the duration of minutes, how do I set that field up?

I spent hours pouring over this stuff and I get close and then it’s like blocks… boom

Thanks - Krista

[Mohamed_Swella1]

Hi @krista_krebs,

For the first part, your best choice is the Switch formula. Its something like Switch({Field Name}, A, 1, B, 2, C, 3)

For the second part, you can do the following:

1. Make a table for the calls (billable ones), link it to the primary table where you have the calls allotment.
2. Make a Field for the call duration
3. In the table where you have the allotment (which is the primary table), roll up the call duration. Make a new field Balance Duration, with a formula that subtracts the rollup field from the allotment field.

BR,
Mo

[Justin_Barrett]

To add to what @Mohamed_Swellam said, I suggest not trying to do everything in a single formula. Divide and conquer. Do the units calculation in one field, and the call billing in another. This lets you later (if needed) use the result of your units calculation for something else, rather than have it buried inside a bigger formula.

[krista_krebs]

You guys rock… I’ve spent a lot of time watching videos :slightly_smiling_face: . and although fairly smart, this keeps me hopping but super fun when you see it all come together. Keep posted.!

[krista_krebs]

Ok… I have one more. In the billable table- how do i figure out what members HAVENT been called? right now I have a table with all of the members data, then the billables… so the staff asked me just now how to show a report or data for the calls they still have to do?

Thanks! Hope everyone is able to wfh! Crazy business in the world.

[Justin_Barrett]

This wouldn’t be set up in the billable table, because that table (if I understand the setup correctly) only links to members in the main table who have had calls. To find those members who haven’t been called, make a new view in the main members table, with a filter to only show records where the link to the billable table is empty (again assuming that no link means no call has been made).

[krista_krebs]

So, I started that way then actually, and I have my link set up to that table, and then using a look up, looking for the billable check box in THAT billable encounters table with the condition that the type of call made was an MCA (as an example)… I’m not getting anything pulled in though and it feels like it should be a formula?

[Justin_Barrett]

Perhaps I’m misunderstanding the way your base is set up. I got the impression from comments above that the [Billable] table (assuming the actual table names for now; please correct me with the true table names so we can both be on the same page) contains records that link to members in the [Members] table, and that the link itself is the primary indicator of whether or not a member has been called. If there’s no link on a given [Members] record to a record in [Billable], the member has not been called, and therefore should be on the “not called yet” list that is being requested. Is that not the case?

[krista_krebs]

So I have a “members” table that lists enrollment date, name, unique id etc…

on a "encounters’ table, there is a link to tie in the call or visit but only pulls the name of the person who is working with the member . There are types of visits, but one particular visit/call has to be made every month. The encounters table, unless that member was called using a calendar of due dates in the members table, wouldn’t have every single member listed. It collects only the call data about the member and time of call, duration. I can’t sort that one for missing members as by the nature of the table, the members are not all listed in that table unless they were all called. So in a nutshell, yes, im looking for the ones not called.
\

[Mohamed_Swella1]

Hi again @krista_krebs,

What @Justin_Barrett is saying is when you did the new Billable table, it created a Field in the Members table (whatever that field is, it will have data if there is corresponding record in Billable Table and it will be empty if it does not have a corresponding record). So, from this logic if you filter this to Empty it will have only the Members whom you didnt call.

Take care that field might be now hidden if you are working on a view in the members table that is not the main grid view.

If you still need help, feel free to share screenshots with us or send us View Only links to your tables or base.