Formulas

Porting something from Google Sheets for the relational goodness, but having trouble wrapping my head around this… don’t even have a formula to troubleshoot, sorry. I have a table called Legislators. Each record is a person. I have a table called Legislation. Each record is a bill. Each bill record has a linked field to Legislators, and contains the uniqueid of each Legislator who voted for the bill. Each bill also has a year, say ‘2020’. There are multiple bills per year in this table, and multiple years worth of bills. Now, in the Legislators table, I wish to have a field that indicates how many times this Legislators voted for any bill in 2020. I just want a number. That number is the number of times this Legislator’s uniqueid shows up in the voted for field of every bill in 2020. In Sheets, I use their SQL equivalent search. I started investigating wacky IF statements here, but I just don’t think that’s going to cut it.

I am trying to calculate the sum of man hours for two teams that have different production hours and different number of staff members. This works for one team member of {Staff (Team 1)} but I can’t seem to get it to multiply by {Staff (Team 1)}, let alone sum {Staff (Team 2} with same formula. DATETIME_DIFF({End (Team 1)},{Start (Team 1)},‘minutes’) / 60-{Breaks in H (Team 1)} My attempt to achieve this that obviously don’t work lol: DATETIME_DIFF({End (Team 1)},{Start (Team 1)}*{Staff (Team 1)},‘minutes’) + DATETIME_DIFF({{End (Team 2)}, {Start (Team 2)})/ 60-{Breaks in H (Team 1)}-{Breaks in H (Team 2)}*sum({Staff (Team 1){{Staff (Team 2)}) Thanks for your help in advance

Hello, We’re needing some help with a formula (or proper linking) to auto-price our services based on the service area - in this case, by Zip Code. The idea is Service Type + ZipCode = Price. For example… Currently, we have a form that takes customer info (Service Submissions). The Client/Customer selects the service and specifically enters the ZipCode in a required field. Through integromat, a record in Service Orders is created. This is the table our team works in. We’d like the price from ZipCode Pricing table above to be calculated and show up in the Service Orders and Invoices tables. Ultimately, the pricing data will be sent to Quickbooks, but that’s a later step. Any help would be greatly appreciated. If there’s any way I can explain this better, please let me know. Thank you in advance!

Hello! I have an extensive table where we log all data and statistics for our business’s Google Ads, social media advertisements, etc. My boss wants a daily recap of the standings for all of the ads in the table. But it would be too time-consuming to draft an email every day containing all of that info. Essentially, what I want to do is set up an email that automatically sends to him every morning at 8 am that includes all of the information in the table. Is this possible? I have a couple of basic automations running, but I can’t figure out how to make it happen daily no matter what, or how to include the entire table. Please keep in mind, I am a beginner when it comes to formulas and automation, so some hand-holding would be appreciated. Thanks in advance! Connor

I would like to do a lookup based on what is selected in a Single Select field. For example: IF single select field is A, Lookup Record X IF single select field is B, Lookup Record Y How can this be set up to avoid errors?

Good Evening, I must definitely use the “®” & “™” symbols in the text description of my products. I guess the property of my field should be RTF file. How should I do it? I also have a attached photo with my name (for example) that how I want to be done [I made it in Microsoft Word]. Thanks.

Hello! I need to compare two lookup columns with email-addresses. If column 2 contains an email from column 1, the formula should return true, otherwise false. Like this: Email from table1 | Email from table2 | Answer email1, email4 | email1, email2, email3, email4 | true email5, email4 | email1, email2, email3, email4 | true email5, email6 | email1, email2, email3, email4 | false

Hello! This is my first time on the forum and I’m excited to leverage everyone’s knowledge. Our product is in dire need of being able to list the “Top 10” of certain group values, essentially leveraging the Autonumber feature in different views. The best way to explain my question is with an example right!! In this image, I see that Airtable auto numbers the names(Pink), but I cannot figure out how to get that number in an actual field (RED), as this is filtered and sorted by the performance (Orange Arrows). Any and all help on this would fully allow us to move our entire product onto Airtable and to finally get us off Sheets. Thank you!

This feels really basic, but I don’t know what I am missing and new at the formula editing. The issue I am having. I am working off one date (Purchase Contract date) and trying to add a number of days (Home Inspection Contingency) to the contract date, to find (Home Inspection Due) due date. It is working for all number of day options eg: 5 days, 7, 14 etc, except when NONE is selected or when the selection is left blank. In either of these cases it still populates the date as a purchase contract date. Ideally in Home Inspection Due it would populate NONE. Is this possible? My formula is: DATEADD({Purchase Contract Date}, {Home Inspection Contingency},‘days’) below. #1 and #2 are wrong, #3 is correct

I found the following formula I am using to format currency. I want to round up or down and get rid of the decimals . Any ideas on how to modify the formula to do that? Thanks! IF( LEFT({NumberValue}&’’,1,1)=’-’, ‘-’, ‘’)&’$’& IF( ABS(VALUE({NumberValue}&’’))>=1000000, INT(ABS(VALUE({NumberValue}&’’))/1000000)&’,’& IF( INT(MOD(VALUE({NumberValue}&’’),1000000)/1000)<100, REPT(‘0’,3-LEN(INT(MOD(VALUE({NumberValue}&’’),1000000)/1000)&’’)), ‘’)&INT(MOD(VALUE({NumberValue}&’’),1000000)/1000)&’,’& IF( INT(MOD(VALUE({NumberValue}&’’),1000))<100, REPT(‘0’,3-LEN(INT(MOD(VALUE({NumberValue}&’’),1000))&’’)), ‘’)&INT(MOD(VALUE({NumberValue}&’’),1000)), IF( ABS(VALUE({NumberValue}&’’))>=1000, INT(ABS(VALUE({NumberValue}&’’))/1000)&’,’& IF( INT(MOD(VALUE({NumberValue}&’’),1000))<100, REPT(‘0’,3-LEN(

I currently have a formula that calculates the cost of a child attending a class over a number of weeks. Extrachildren*({Weeks FM}+{WeeksSR})*5 This works well until I was told if they book for 11 weeks then the 11th week is free (they never book for more than 11). So I need the formula to multiply by 10 if 11 weeks are booked. Can somebody please tell me how that should be expressed in the formula. Thanks in advance Phil

Sorry in advance - I am a very green user to Airtable and Databases in general. I would like to make a formula that can scan a record for a word and then automatically make the value 0. Example: Line Item ------------Unit ----------- Rate---------Total Cost Invoice - 0011-------1.00------------$100----------$100 Bid - 0100------------2.00------------$500----------$0 In this case I am using: Rate*Unit to reach the total cost. This will mean that the “Line Item” Bid-0100 will pop up with a total cost of $500, but I need it to show 0 as it hasn’t actually cost me anything. Is this possible?

HELLO! What fx do I have to build in “copy”, so that the value of the cell below is the summation with the value above? [quote=“Serge_Lacasse, post:1, topic:29840, full:true”]

Hello everyone, and @Jeremy_Oglesby in particular. I desperately need some assistance on this one. What I would really like is to be able to set up a product list with an automated parts inventory list. The moment I hit [PRODUCT SOLD], the designated parts to that particular product then should be subtracted from the parts inventory list automatically. To be able to do this I grouped records under a product type in a many to many junction table and told Airtable how many parts each product consists of. But… I cannot get this to work. Here you’ll find a copy of the base. The base is still somewhat of a mess. I translated the base as well as I could from Dutch to English in order for you folks to be able to peek in, please do:

I’m struggling with formatting my formula values. I’m trying to design a proposal in Page Designer. We list the price of all items, even when we don’t think we will use them so the client knows what they would cost if they are used. On the proposal, I want the subtotal value to be blank or * instead of $0.00. Easy enough, but then I want it to still format as a dollar value for the other items. I have column “Qty” where I manually enter the quantity of the item to go on the proposal. “Subtotal” multiplies the “Qty” and “Price”. “Qty for Proposal” and “Subtotal for Proposal” are the fields that are used for Page Designer. Is there a way to have my cake and eat it too? Thanks.

This has been asked a few times but I can’t seem to find the solution that works. I have a formula "CONCATENATE({For Formula},{Market},{Product Management},{Product Category},{Gender})" but its output adds unwanted quote marks like so: What I really want is to have it be separated by “/” but I’ve exhausted the time I have trying to fix that. How can I get this to output something with consistent quote marks - or comma separation - or preferably the / Thanks!

I have two columns that currently have the same fields. Column 1 is a synced column. Column 2 is a copy of Column 1, and we are asking collaborators to go into the base, and add or remove items from Column 2. In Column 3, I want a formula to show me all the items that were added or removed in Column 2 - mostly so we can track those changes and make sure nothing was removed or added incorrectly. Any thoughts on how to do that? Thank you!!!

I have a decently large data set, and I am trying to mine from it information to see what has value. On that I am trying to create a new sheet that has a list of keywords. I would like to then have a column next to each keyword that displays how many times that word appears in my main sheet (or a single column). I have seen several topics that cover something similar but nothing that seems to fit the bill. Can anyone help? Thanks!

Is it possible to put the Enter key in the formula? I tried the Enter key code (^p, it is Enter key code in the Word) , but it didn’t work. I want to implement the following display: EU Community Legislation: RED 2014/53/EU, RoHS Directive 2011/65/EU (10), RoHS Directive (EU) 2015/863; Product Standards: EN 301 489-1 V2.2.3, EN 301 489-17 V3.2.4, EN 300 328 V2.2.2, EN 301 893 V2.1.1, EN 62311:2008, EN 62368-1:2014/A11:2017; Adapter Standars: NA

How can I create a formula field that references an existing text field, but removes hard returns between lines of type?

Hi there! I’m setting up an editing task list for a wedding photo + video business. I have a lookup field set up for the wedding date, and would like to have one field that returns the deadline for each deliverable (various films, galleries, etc.), based on differing #s of days from the wedding date. E.g. preview photo gallery due in 21 days, versus a film due in 60 days, etc. If I use the formula shown below, it seems like I have to have separate fields for each of my different delivery timings (+21 days for some, +60 days for others, etc.), which really muddies up my table. This also doesn’t work well in cases where my deadline needs to deviate from the rule (for example, a scenario where we need to deliver in 14 days rather than our usual 21 days) I’d like to be able to enter the number of days post-wedding that the item is

How do we get this formula right? Now it always returns 0

Hi! I’m stuck on a formula for tracking attendance. IF({Attended 10/23}<>1, “New”, IF(AND({Attended 10/23}=1, {Attended 12/8}=1), “Yes”,“No”) ) I have had 2 classes, and I want to know how many people who came to the first one (on 10/23) also came to the second (on 12/8). If they came to 10/23 but not 12/8, I’d like it to spit out “No” (because I am calling that column “Returned”). However, if they came to just the second meeting, I’d like to fill in “New” because I will ultimately want to filter out those who didn’t attend both sessions when getting my return attendance rate. This way, I should have: *Who came to my first class *Who came to my second class *Who my new students were in class 2 *Who came to the first and returned for the second Ideally, I’d like to see % attended across all sessions by student. Open to other ways of getting these metrics and am grateful for the help!

I am trying to come up with a formula that when I select an item from my multiple select field it uses its separate value to add to the total quote total. Any advice would be greatly appreciated.

Hi, I have a base where we key in the ID numbers of each person in a column - Males have odd numbers and females have even numbers. Would like to create a column that automatically tags them as either Male or female based on this data but I cant seem to figure our what formula to use. Could someone help me? It would be of great help. Examples: XXXXXX-XX-5557 = Male XXXXXX-XX-5556 = Female.