Formulas

I have a number for every record in this format P01384. It starts with P00000. There are several records missing and I would like to see if there is a way to write a formula that would give me the numbers of the missing records in the sequence. Is this possible in Airtable?. I have found a way to do it exporting a .csv of the table and then, using a macro I found for Excel, find the missing numbers. A very convoluted way. Would love to just have this info in Airtable. Seems like it should be a simple thing but guess it’s not. Any guidance would be greatly appreciated.

Hello! I have built a database to record a patient’s treatment. I have shared with him a form to introduce the pills he takes… .but I still have to add up the number of tablets he takes in a day, a week or a month…. I have tried with sum and rollup formulas … BUT I DON’T KNOW HOW TO DO IT! Could you help me get it? What steps should I follow…. What can you think of? I could even share my database with some of you to help me make this column, sum, sum or rollup…. THANKS A LOT.

Hello, We have 2 Row From and To and its datetime ! How may i find the diffrent time from ( from ) until To in the Same day ? please check the image

Hi guys, Actually I want to use this tool - https://statically.io/favicons/ which automatically grabs and display favicon of a any website if I write the URL in the format - https://cdn.statically.io/favicons/domain.com For example - https://cdn.statically.io/favicons/google.com Now, I already have a formula column which has multiple domain URL as the row cell value. Now, I want to create a new column with the following cell structure as shown above. but here the domain .com should be replaced the domain URL written in that column. Now , what formula should I use to create a URL like this for different values of domain .com Any help will be

Im a little confused why this isn’t working correctly… I have {Job Date} field and a formula field with this… IF({Job Date}="","-",DATETIME_DIFF({Job Date}, TODAY(),‘minutes’)) This formula field does initially show how many minutes before my {Job Date} but it doesn’t actually count down. I assumed with this formula it would actually count down every minute with the new correct number of minutes before the {Job Date}. Am I missing something? Any ideas how to make this work? Thanks in advance!

This one has me stumped… Please help! I have two tables in my base. Table A has a field called ‘Brand URL’ Table B also has a field called ‘Brand URL’ I would like a formula field to find matches between Table A and B based on their Brand URL values. When a match is found, I would like to automatically link the records together. What’s the best way to do this? Thanks!

Another challenging one… Any advice appreciated! I have a long text field containing multiple customer reviews from Amazon, separated by strings of special characters. For example: " //Lorem ipsum lorem ipsum// //Lorem ipsum fake ipsum// //Lorem counterfeit lorem ipsum// //Lorem ipsum lorem ipsum// " I want to write a formula that will search this field for specific keywords such as “fake” and “counterfeit”. When the formula finds a match to these keywords, it will grab all the text between the special character markers wherein the keyword was found. So, for example, using the above long text value, my formula would output: " Lorem ipsum fake ipsum Lorem counterfeit lorem ipsum " Thanks for your help!

How can I edit this formula to leave the field blank if the number returned is negative? Thanks! IF({Update Cycle}=‘1’,({Days Since Update}-30), IF({Update Cycle}=‘2’,({Days Since Update}-60), IF({Update Cycle}=‘3’,({Days Since Update}-90), IF({Update Cycle}=‘4’,({Days Since Update}-120), IF({Update Cycle}=‘5’,({Days Since Update}-150), IF({Update Cycle}=‘6’,({Days Since Update}-180), IF({Update Cycle}=‘7’,({Days Since Update}-210), IF({Update Cycle}=‘8’,({Days Since Update}-240), IF({Update Cycle}=‘9’,({Days Since Update}-270), IF({Update Cycle}=‘10’,({Days Since Update}-300), IF({Update Cycle}=‘11’,({Days Since Update}-330), IF({Update Cycle}=‘12’,({Days Since Update}-360), “” ) ) ) ) ) ) ) ) ) ) ) )

This is admittedly an aesthetic concern, but I feel like it should be possible. I have a formula that tests a checkbox field, {Guild?}, to see if I owe fees for a given project to a guild I work with. If it is a guild project, then the formula should return a currency value equal to the amount received for the project, {Rec’d}, times the Cut field, which is a percentage. Currently, I have it written like so: IF({Guild?}=1, {Rec’d}*{Cut}, " ") This returns the right numbers for guild projects and leaves non-guild projects blank. Fine and good. However, I cannot use the field formatting options because the values are not all numbers. Is there a way to tell Airtable to return {Rec’d}*{Cut} as currency?

Not sure if this is possible, or if I’m even in the right topic. But essentially, I have set up a table for material cost for wood, but I want to use it mainly as a tool for giving quotes to clients. I have a material field, the dimension, unit cost, tax rate, and total cost after tax. I would like to set up the dimension field so that when I put a specific dimension in, it will automatically populate unit price and all that in the remaining fields. For example: For one project, I want to know how much it will cost me to get 100 board feet of 2x6 American Yellow Cedar. I want to put in 2x6 in my dimensions field, and have it’s unit cost appear in it’s appropriate field, and let the formula calculate the cost. Then for another project, I need to figure out how much 150 board feet of 2x4 walnut will be. So I’d select my material as walnut, put in the dimension, and then have the rest of the fields auto populate with data I’ve already put into the system. I hope this make

Hi guys, I have a problem with my base. On my base, I have 3 differents tables, bénévole = volunteer, événement = events and inscription événements = events registration. On the first, I have the list of the volunteer. For each volunters, I want that they have the first event that they have participated in Evénement A, the second in Evenement B… The Events are from the second table. On the second table, I have the list of the events with all the information about the event. On the third table, I have two view, the Grid one and the form one. With the form, the volunteer could registrate to the event (column Evenement). They can choose multiple Events. On the Grid view, I have the detail of the volunteers registrations.

Hi! I would like to use Airtable to do my accounting, but I need a formula that is way above my paygrade, maybe someone here can help? I have a (single select) column 'deposit/withdrawals" and an amount column where all amounts are positive. I’d like to have the withdrawal amounts show as negative. So I made a third column where I’d like to say “if column 1 says withdrawal, then please give me column 2 amount as -amount, if it says deposit, then please give me column 2 as +amount”. Maybe that’s not the right way to go (?) but in any case, I’d love some help if possible! Thank you!

Hi there, I have two Currency fields and a third field where I am concatenating the entered values in currency fields. But after concatenation, the format of currency disappears. eg- Col1- $12,500 Col-2- $10,200 Col 3 ( Concatenating Col1- Col2)- 12500-10200 what I am looking for is $12,500-$10,200. Is it possible? Thank You

Hi everyone, i feel like this might not be possible but thought i would give it a try. i’m building a library at my business where students can check out equipment to use at home. i will have a specific number of items and was hoping that i can have them check out the equipment using a form view. i cant seem to figure out how to build an inventory system that will stay up to date with the equipment that currently available. any thoughts or samples out there? appreciate any help on this!

Hello! I am trying to write a formula for tracking what month something was checked as billed. I want to be able to group by month billed to show income by month. Currently I use this Formula for date billed when checked. IF(Billed=1,TODAY()) This shows me the date it was billed. But for grouping purposes I would like a formula that would say when billed has been checked it will show month checked. I tried this formula IF(Billed=1,MONTH()) But it does not work, it shows #ERROR!. Is there a formula I can use to state only the month when billed is checked?

Hi Community, Appreciate if someone can help comment about this statement as its not working. Thanks. IF( {Event Type} = ‘Partner Tech Talk’, CONCATENATE(“URL/YYYYYYYYYY”,"?prefill_id=",ENCODE_URL_COMPONENT({Feedback Key}), CONCATENATE(“URL/XXXXXXXXXX”,"?prefill_id=",ENCODE_URL_COMPONENT({Feedback Key}) ) NOTE: I concealed actual URLs - assuming URL/YYYYYY works. In other words CONCATENATE statement works by itself. However, when trying to create IF statement picking the right URL based on Event Type field this doesn’t work.

I saw a post with the following formula: IF(IS_BEFORE({Due Date}, TODAY()), “ :boom: ”, " ") This formula puts the emoji in a column if the due date for something is past. I’m not really good with formulas. Is there a way to make it have that emoji if it is ON the due date AND after?

New to Airtable and loving it so far! I am creating a Table for calculating costs and processing invoices for a logistics company. I am looking for a formula that will give me the result below: If the number in column A is greater than the number in Column B, then it should be inserted into column C. BUT if the number in Column A is less than that in Column B, the Column B number should be inserted into Column C. In other words, the greater of the numbers in Columns A and B should be inserted into column C. Any help would be appreciated.

Hi there, I’ve been working on this and cannot seem to figure it out. I’m trying to write a conditional IF statements that essentially says - IF Field A= 1 and Field B=2- output 3 IF Field B=1 and Field A=2- output 4 IF Field C = X- Output X Here is the current syntax I have written- I’ve gotten it to do the first two parts, but cant get the last part running. Any help is wildly appreciated!! IF(AND({Are you in recovery for a mental health disorder?}=‘Yes’,{Are you in recovery from a substance use disorder?}=‘No’),‘MH’,IF(AND({Are you in recovery from a substance use disorder?}=‘Yes’,{Are you in recovery for a mental health disorder?}=‘No’),‘SUD’),IF {Field 60}=’CO’,’Co’,’Z’)

Hello, I have a formula for UTM and its pulling from the other columns in the sheet, but for some reason, it is not hyperlinking the entire URL. I not sure why, But I have doubled checked the formula to make sure there are no spaces. I am new to Airtable formulas so I am not sure what I am doing wrong. Thanks!

Hello, I’m writing a formula to tell me the DMA region of a client based on a list of zip codes. Here’s my formula so far: IF({Zip Code}=‘34471’,‘Tampa’,IF({Zip Code}=‘32703’,‘Orlando’,‘Other’)) I just need to know how to turn ‘34471’ and ‘32703’ into a list of zip codes (I already have the list), where ‘Tampa’ or ‘Orlando’ could be triggered for any match in that list. Thanks!

Hi, Can someone help me find % completed? So in my Airtable, there are various tasks and subtasks. For example, Legal will 5 tasks and the statuses are complete and not started. What I am trying to find out is if 3 of those 5 are complete then how can it display 3/5 in % terms in Legal. Same for Operations under Operations category. And lastly, I want to add total project completion percentage based on LEGAL and OPERATIONS.

I’m converting a table that has multiple text field columns into one multiple-select column. The old form had text input fields so the most of the data is like this: and now that form has condensed those fields into one multiple-select field, like this: . I don’t want to keep the old text columns around, and want to collate those fields into the single existing multiple-select column. I think the way to do this is in a step-wise fashion: create a lookup table that converts the text into the new multiple-select labels use a rollup field on the main table to concatenate or arrayjoin the data into one comma-delineated string, copy that data over into the existing multiple-select column delete the old columns and the reference table

Hi there! I have a Table called Projects and another called Updates (both are connected via Linked Records), both are in the same Base. I’d like to have a flag (a binary 0/1 or true/false so I can put them in a Count Block on a Dashboard) for all Projects (that have a Status of In Progress) if they don’t have an Update for them for the previous week. In the Updates Table I have the name of each record as the number of the week (calculated via a formula, with a little nuance for stragglers who might not update until Monday). Any idea how’d I’d go about doing this? Thanks in advance for your help! James

My goal is to have a formula to populate New Profile from either Legacy Profile or a Concatenate of Company Responsibility, Professional Background, Educational Background. Ideally for the Concatenate portion, I can have the three fields with a paragraph break between them, if that level of formatting can be achieved. (as demonstrated in the sample table below) My records will only have either the legacy field or the new 3-field combo populated with data. New Profile fx Legacy Profile Company Responsbility Professional Background Educational Background Amazing legacy profile Amazing legacy profile CEO responsible for big things.Worked at great companies like…Super smart becau