Formulas

I’m struggling with the following problem. I’ve made a small system for a clock in a clock out, which tells how much money we’ll be invoices at the end of the month. However sometimes staff manage to clock in and out twice, which makes my end of month salary calculations in accurate. I’ve been trying to think up a way to check if the same staff has logged the same date twice. I’ve found a way that involves a sperate linked table where each row contain it’s own date and each staff has its own column with a bunch of look ups. However its very messy an inflexible and way more time-consuming than meticulously combing through all the clock ins at the end of the month. Sofar I’ve come up short, I have the feeling it’s not possible, but is there anyone who has any suggestion?

I’ve got a table of rates with “effectivity dates”. Such that when an order comes in, I need to find the “current” rate associated with that order, on the order’s date. My structure so far is as follows: Table Order - Line Items (Link to Order Line Items) - Order Date Table Order Line Item - Order (Link to Orders) - Order Date (Lookup to Orders.Date) - Line Item Total - Line Item Vendor (link to Vendor) - Effective Vendor Rate (?????HOW????) Table Vendor - Name - Rates (Link to Vendor Rates) Vendor Rate - Start Date - End Date - Rate (Percentage)

Hi wonder if there is anyone who can help. I am trying to make a deadline field. I need a formula that adds a certain amount of time to the created date if another field has a certain word in it. hope this makes sense

I’ve got a table that already has a link that is working. In this case it is Order Line Items to Products. I’d like to have a column in my Products table that has the total of Order Line Items (line item total) within the past 60 days. I could change the link to be filtered by a View, but then I’d not be able to see the Total Lifetime Orders. How can I filter the linked records during Rollup?

I’m looking for a formula for the following. I have a Checkbox Field “Msg’d” & Date Field “Date Msg’d” & another Checkbox Field “Responded” I’m wanting to find a formula where, If “Msg’d” is checked & “Responded is NOT checked” & it’s been more than 3 days after “Date Msg’d” it is marked “FOLLOW UP”

I’m trying to get a formula that allows we the take the total of numbers in the linked filed to be summed together For example Total Amount, Amounts used, Total Amount used, Currently in Stock 10 1,4,5 10 0 Idk if this makes any sense at all i just have a list of items in one table with all their information in another table any time i use the item i track the date and the amount used I would like my stock table Currently in Stock number to be able to subtract the total amount used since i may use a product multiple times in different areas or times My Total Amount is to help me track the amount i started to help know if we should get less or more incrase our amount

I have one table of people, and another with records of their visits. In the people table, I am trying to add a field that will tell me the date of the earliest visit… after 9/30/2016. I tried adding a formula field in the visits table to return the date of the visit only if it is after 9/30/16, but I have no idea how to build a specific date into the formula. I really don’t want to add another field to the table that will force me to add “9/30/16” to every single visit record just so I can do an IS_AFTER formula with two date fields. (Which reminds me, why don’t default values you set for certain fields show up when you add new records in the mobile app? Real bummer. But I digress.) IF(IS_AFTER(DATE,9/30/16),DATETIME_FORMAT(DATE,‘MM/DD/YYYY’),"") returns #ERROR everywhere. I am really hoping my format “9/30/16” is the problem, but I have tried other variations with no success. Meanwhile, if it is possible to get that formula to work, will a roll-up field in the people table let me pul

Hey everyone, I have an update issue I can’t figure out. In my database, I have filtered my projects by brand type which will show up on my Android phone. However, on my boss’s iPhone the filter isn’t showing up. The other issue is that I can search for a specific part number based on the item description such as 1/4" ratchet and find it in my Android app but my boss can not. Once he types in the ", no record will show up. Any tips?

I have two tables - one for income transactions and one for expenses. I’m trying to create a summary table that aggregates values from the two other tables to show monthly balance. Is there a way to: Aggregate rows with dates into months? Pull in amounts from income and expense tables that map to those months?

Without going to Google sheets, how can I format an imported date/time from 2019-09-06 7:52 PM in the formula field to 9/06/2019 7:52 PM? Everything I’ve found just sets everything past 12pm to 12:00 AM. Also, is there something I’m missing using the regular date/time field? This doesn’t seem like a big stretch, but when I change the field type it just clears everything. Thanks!

Hi, I have a base that covers a script breakdown. One table within the base includes all information in regards to a Scene within an Episode, including all characters that need to be recorded within that scene (Script Breakdown). Another table is a more granular version of this that breaks out each character of that scene to it’s own row. Both tables are linked via the Episode and Scene number. In this table I have a check box that I tick if a particular character voice has been recorded (Script Recorded Breakdown). What I would like to do is create a formula within the Script Breakdown table that says something along the lines of ‘if all of the checkboxes within the “Script Recorded Breakdown Table” that link to this particular scene are checked’ than give the answer ‘recorded’ otherwise leave blank or something along those lines. I hope this makes sense? In excel I would use a combination of if/vlookup but I’m not too sure what to do in airtable. Any ideas??

Greetings to all. I’m trying to build a filter that only shows me records from specific items that are linked from another table. I have two tables linked together. One shows the names of users on my network along with other general information. My second table is a list of licenses and prices per licenses. The license table is linked to the user table so that if a user have 2 licenses I will be able to see which licenses the users has from the user table. There are some other roll up columns that calculate total cost of users licenses. Every time I create a regular filter on the user table to see a list of specific licenses that I can group by the user department, it treats every combination of licenses as a different criteria. I want to be able to filter specific licenses only. Not sure I’m explaining it the best way. I have images but I guess I can’t upload them on a post Any help will be greatly appreciated.

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 in

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’))