Formulas

So I’ve got a ton of columns, I’m rolling up using ARRAYUNIQUE, I get ", " between each item getting rolled up. I tried adding ARRAYUNIQUE(values, “”) to the formula which doesn’t work, it does in a rollupfield for ARRAYJOIN, but not ARRAYUNIQUE. So I got the idea in the rollup to add the formula ARRAYUNIQUE(ARRAYJOIN(values, “”), which amazingly worked, got rid fo the commas, just one catch it duplicates the first value (it does the same if I create a formula in the column next to it so my main rollup column is a ARRAYJOIN and then formula column next to it is ARRAYUNIQUE but it duplicates the first value), any advice on a better way to do this? FYI for the record what I have now is the rollup table using ARRAYUNIQUE, with a field next to it using a Substitute formula to get rid of the ", " the Arrayunique is adding in, thankfully my data doesn’t have any commas but what if it did? What would I do? Cheers, Skyler

Hi all, I’ve got a request about a specific formula. I’m creating a rental database and have ‘Start Date’ and ‘End Date’ columns that link to calendar events (jobs). I’m looking for a formula that I can put in a ‘Status’ column. Is there a way to have an IF statement that returns “Booked” if the start date is before today, “In Use” if today is between the start and end dates, and “Available” if today is after the end date. I just can’t quite wrap my head around it since most date formulas return either a 1, or 0 and I’m working with 3 return values. Right now I’ve got the following: IF(IS_AFTER({Start Date}, TODAY()), “Booked”, IF(IS_AFTER(TODAY()), {End Date}), “Available”, “In Use”)) but it is not working. any help is appreciated, thanks!

Hi, I have the following formula that works by displaying a ! when col1 is empty: IF({Col1} = BLANK(), “!”, “”) However I would like to add OR statement so that the ! is displayed if either Col1, Col2 or Col3 have no entries. I have tried varying different ways with no luck, such as: IF({Col1} or {Col2} or {Col3} = BLANK(), “!”, “”) Thanks in advanced.

I generate 50,000 records per year. Is possible has in Airtable 500,000 records (10 years) without problems?

I have 3 tables; one is the main table that has two linked fields to the two other tables. The other two tables have an attachments field with one attachment. I want to add a new column on the main table that shows the attachments of both the the attachments in the associated tables. Is there anyway to do that?

Hi All. I’ve scoured the topics and many come close but I can’t seem to track this one down exactly. I have a base I built for my small farm. I have schedules (when to plant, where it goes, when to harvest etc etc). I recently incorporated an IFTTT app to pass recent rain values into a table (Precip Tab). I have the new entries auto-numbered. Long story short, I want to pull the most recent rain amount into my “Schedule” tab, or any other tab for further calculation. I saw the articles on using a “Calc” tab and linking to all records, but my hang up is this; do you have to manually link every time a new record is created? New records here come in automatically, and I don’t want to have to manually add these new records every time it rains. Wishing a formula could be used to link to records but I don’t think that’s an option. I think I could figure this out based on other questions/replies if I weren’t trying to link to a moving target. Hopefully that makes sense… thanks

Hi everyone, I’d like users to submit entries via an Airtable Form and confirm their email. With Zapier, I can react to a new entry and send a confirmation email. Easy. But how do I close the loop? What kind of link could the user click on, that would trigger a confirmation in the base? Any creative idea? Thanks, Tim

Hi, I use Airtable as a todo checklist. Each day I add new checklist items (as individual records) to my table. On any given day I might have 5 or 10 items in the table. I want to add a new field in each record called “Time Estimate” (or something to that affect) in which I can type the amount of time I think it will take me to complete this task. Then, I’d like to find a way for Airtable to automatically add the numbers for me, so that I can see how long the sum total of all tasks for that day would take me to complete. Rollup seems like it would do that, but from what I understand only works with linked records in a different table. I want to see my summed total in the same table, and don’t want to have to go through the trouble of linking records each time I create new ones (which is frequently). Is there a way to do what I’m asking?

Need a little help…I have created a column that I want to contain a street address and city, pulling this from two existing columns. I was able to create a concatenate formula to add those two columns together but the resulting text is not separated by a comma. I need to figure out how to separate the street address and city with a comma. For example, I want the text to read 123 Main Street, Chicago but with my current formula it reads 123 Main StreetChicago. Any help is much appreciated. My current formula looks like this … CONCATENATE(Address,City)

I am trying to add a formula field that replaces all spaces in a long text field with “+”, and all paragraph breaks with “\n”. How do I designate a paragraph break in a formula? SUBSTITUTE(SUBSTITUTE({Event Details},’ ‘,’+’),< p >,’\n’) won’t even save, and if I put quotes around “< p >” it saves but of course doesn’t take out actual paragraph breaks. Same thing if I use \ n or %0A. (I had to add spaces in < p > here, so the above paragraph wouldn’t split over 2 lines!) Any suggestions would be greatly appreciated! Thanks

I have a database that I track service calls with. i have a start time, end time, and a field called invoice time. I would like the invoice time to calculate the hours i am going to invoice the customer for based on the start time and end time entered for the service call. For example: start time 8:00am End Time 9:00am = invoice time 60 minutes or 1 hour. Another example: start time 8:00am End Time 8:45am = invoice time 45 minutes. It would be nice if i could pick a time on the start and end colums with a picker, right now i have to manually enter the times.

I have a column with a value in seconds which I can easily set up as a mirror showing duration using the formatting option. I now want to concantate that with an integer so I end up with something like “234 - 01:04:32” where the formula is CONCATENATE({integar field}, " - ",{duration field}) trouble is that the latter field converts itself back to seconds! How I can I stop it doing that? Many thanks

I am trying to create one record for each Monday of the year. The following formula is one that I found on this forum. It works, of course, but I have to enter the date {Date} which defeats the purpose. Is there a way for it to point to the previous record’s date field? (Like, last week’s Monday plus 7?) IF(1-WEEKDAY({Date})<=0,DATEADD({Date},1-(WEEKDAY({Date}))+7,‘days’),DATEADD({Date},1-(WEEKDAY({Date})),‘days’))

Hey there! I have to make a very simple formula but having problems on how to write it correctly I have two date fields (arrival, depature) I have a Total days field ( calculates the date difference) -working I have a price field that shows price for the spesific amount of days I have the formula: IF({Dager} = 1, "590, ") & IF({Dager} = 2, "690, ") & IF({Dager} = 3, "840, ") & and so on all the way to 21 days ( everything here is working fine) BUT, after day number 21, the price is gonna add 20 for each day. It’s going to be something like this: If({dager)>21, ({dager},-21*(20),1490, ") Example with 29 days - If({dager)>21, ({29},-21*(20),1490, ") Here the days over 21 is 8. Then multiply 8 by 20, then + 1490 (price for 21 days) And the total is then 1650, which is correct. When I do this in the calculator I get the correct price. When I did this in Caspio Cloud, it al

We have multiple remodel projects running at the same time with similar tasks for each project. Is there a formula that would let us know that is time to order a certain item (flooring) or execute a specific task? Example: flooring takes 5 weeks from time order is placed and it would be great to get a notification/alert reminding us to place the order 6-7 weeks prior to deadline. We are currently using free version, is upgrade to blocks needed? Thank you.

Hi everyone! I’m trying to make a formula where I can only see the date of the project is about to expire. I don’t know if that’s something it can be done, but it would really help me because I don’t want to be seen always all the dates, but only the ones I’m about to work. I hope someone can help me. Thank you in advance!

I am simply trying to divide two numbers but am receiving an Error for a result. The numerator is a # column were the denominator is a formula. Numerator: {{Lease Total}} Denominator: IF(AND({Leasing Co.}=“Ascentium”,Credit=“E”),.0499,IF(AND({Leasing Co.}=“Ascentium”,Credit=“D”),.045,IF(AND({Leasing Co.}=“Ascentium”,Credit=“C”),.0325,IF(AND({Leasing Co.}=“Ascentium”,Credit=“B”),.0295,IF(AND({Leasing Co.}=“Ascentium”,Credit=“A”),.0285,IF(AND({Leasing Co.}=“Ascentium”,Credit=“P”),.0279,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“E”),.0405,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“D”),.0405,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“C”),.0305,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“B”),.0291,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“A”),.0281,IF(AND({Leasing Co.}=“FDGL (Clover)”,Credit=“P”),.0271,IF(AND({Leasing Co.}=“FDGL”,Credit=“E”),.0399,IF(AND({Leasing Co.}=“FDGL”,Credit=“P”),.0265,IF(AND({Leasing Co.}=“FDGL”,Credit=“A”),.0275,IF(AND({Leasing Co.}=“FDG

I am trying to make a grocery list that would take the price of an item ($0.88) and multiply it by the quantity needed (2) so that it outputs the total amount ($1.76) I have tried the formula Price * Quantity which hasn’t worked. How would i create a formula to output the total price as stated above? Screenshots

Hi! I’m trying to create a formula where my Primary Field is a number that I need to pull from a URL link: Currently, I am using the formula RIGHT() to tell the formula to find the last three characters from the right of the URL link, which won’t work once we get to 4 digits, or if for some reason the URL structure changes adding an extra character at the end. Is there a way to find only a number from a URL and return it as such? Thank you.

I have a table “Ethnicity”. For the purpose of this post, I’ll use Asian ethnicities as an example, but my Ethnicity table will also include ethnicities of other racial groups. On this table are entries like “Asian Indian”, “Bangladeshi”, “Bhutanese”, “Taiwanese”, “Chinese”, etc. I have another table “Contacts” which contains a column that links to Ethnicity. Each record in Contacts can be linked to multiple ethnicities. I now want a column that marks whether a record’s ethnicities fall under larger racial groups. For example, if the record is linked to “Taiwanese” or “Chinese” or both, I want the record to be marked “Yes” under the column, “Asian” What’s the best way to accomplish this?

Hello! I need some help of this community because I lack the knowledge to develop formulas. I trying to create an automated financial system using some linked tables inside a base. I have expenses categorized in a field with the options ‘‘Credit’’ and ‘‘Debit’’, which I use to separate the payment methods. The expenses also have a ‘‘Date’’ field and a ‘‘Cost’’ field. I want to create a field using a formula that brings me the data from the ‘‘Cost’’ field if the expense is payed in ‘‘Credit’’ within a specific month (actual month) in order to separate this values so I can link them to another table and use a simple SUM formula to calculate my total expenses in Credit for the actual month. So its something like: If method of payment = Credit, If date = actual month, ‘‘cost’’ I know I could simply use a filter view to see only ‘‘Credit’’ records, but as the data will be linked to another table for other operations, I cant. I would be extremely grateful if yo

I have two tables, and I want one to display a sum of a column if it meets Table 1 House # | Free Nights Total | Total Nights Used| House 1 | 5 | 4 | House 2 | 3 | 2 | House 3 | 9 | Table Two Guest Name | House # Used | Free Nights Used Guest 1 | House 1 | 2 Guest 2 | House 3 | 5 Guest 3 | House 1 | 4 What Im looking for is the “Total Nights Used” column in table 1 to total up each Houses used Free Nights Hopefully that makes sense??? And apologies if this question has already been answered!

I’m trying to insert a timestamp into a field when a value on a record changes. I’ve got this but it’s not really updating like I was expecting. IF(Stage=“3. Fruiting”, NOW(),0) It seems to be updating randomly. Any ideas?

Hi so I’m trying to write a formula to concatenate a name and a date but the date keeps coming back in long format. Is there anyway I can just have the short date (i.e. 09/05/2019) shown?

Hello! I don’t know how to do this. To simplify, let’s say I have 2 tables, one called topics with a list of article topics, and one called content with a list of articles, with a single select field categorizing the articles by country. In the topics table, I need a look up field of the articles per country - for now I just have one lookup field that shows all the articles, in all countries. So I need some sort of formula that would say “only show me the articles from the articles table which have the single select field of [country]=Belgium” Hope this makes sense - and hope it’s possible! Thank you :slightly_smiling_face: