Solved

Hello, Cell value 0 is blank Cell value 0.00 is blank Cell value empty is also blank How to treat cell with 0 value and empty cells differently? All the following forumas are returning result for both the {Buy} cells that has zero value and the {Buy} cells that are empty. I want result only for the cells that has 0 or more value not the cells that has empty values. IF(AND(IF({Buy} >= -1, TRUE(), FALSE()),{Sell}), (Sell-Buy)*Lot*Qty) IF(AND(IF(NOT({Buy} = 0), TRUE(), FALSE()),{Sell}), (Sell-Buy)*Lot*Qty) IF(AND(IF({Buy} = BLANK(), FALSE(), TRUE()),{Sell}), (Sell-Buy)*Lot*Qty)

Hello Community, i have a “problem” about shared view with my clients that doesn’t have an Airtable account. They can open the view normally and see data, click buttons and so on without any problem. The issue occurs when they click the button that opens a form to upload new attachments: the problem concerns the update of new attachment in the shared view, they need to reload the view to see the new attachment while in my view this happens in real time without reloading. Anyone have a solution? Thank you, Alex

Hello, I’m trying to generate a monthly report can someone give me an idea on how to do it please other than Filtering the date.

I would like to switch from Pro plan to Free plan for a few months while it will not be getting used. I currently have several thousand records, far more than the free plan will allow. When I switch back to Pro plan will all my records still be there?

Hello people, My goal is to use sweet airtable to store the data from my psychology experiment. I would like to create 5 table (Metrics, 1-4 experiment). Each experiment needs to contain a lot of fields. Data schema is -probe[i]-id -probe[i]-feature1 -probe[i]-feature2 -probe[i]-feature3 -probe[i]-feature4 Each experiment will have i === 192 which means I need to create 960 fields. I was searching for script to do it automatically, however I did not find per-defined one. I found this site (Airtable Blocks SDK), however I have not clue how to get is going (tried automation, apps). Im not sure, if the tool is not suitable for the task or my programming skills are not suitable for the tool. I would be grateful, once I will get advice how to overcome the problem. Cheers

I’m probably overthinking this, but struggling to find the right formula to accomplish the following: I have a list of addresses in abbreviated format “123 Main St”, but some addresses have numbers on the end (“123 Main St #2A”). I’m trying to find a way to extract the “St” without affecting addresses with street names like “State” or “Sterling”. It doesn’t need to be perfect, so addresses with St that have a number after them can remain in that format. I’m thinking I need a formula that combines IF, RIGHT and SUBSTITUTE, but can’t quite get it. IF “ST” is contained in the last two characters, extract them. Thanks, Airtable!

Hi everyone, struggling to pin down a formula… My goal is to track investments over time (i.e. date of deposit, how much was deposited, what the total amount was in my wallet at the time of that deposit, and finishing with profit/loss since I began). The formula I need is for profit/loss since starting. I figure this value is “Total Amount After Deposit” minus the SUM of all values in the “Converted” field (which are all deposits converted from EUR to USD on the investment platform, i.e. Converted1, Converted2, etc)… So how do I get the SUM of ALL Converted values into the formula? (See screenshot for reference) Any questions/clarification required, please let me know. Thanks! Christopher

Hello Airtable community! I’ve been cobbling together a Base for managing my freelance business which includes tracking bank transactions, expense categories, clients, projects, billable hours, etc. I’m attempting to recreate the 2021 Tax Rate Schedule for the Single filing status from the 1040-ES. I believe I’ve gotten most of the income tax bracket arguments to trigger properly but am stuck for any amount over $209,425.00. Adding even $0.01 to that quantity ends up with a seemingly empty result. Where am I goofing up my formula? IF(AND(INCOME >= 0, INCOME <= 9950), (INCOME - 0) * .1 + 0, IF(AND(INCOME > 9950, INCOME <= 40525), (INCOME - 9950) * .12 + 995, IF(AND(INCOME > 40525, INCOME <= 86375), (INCOME - 40525) * .22 + 4664, IF(AND(INCOME > 86375, INCOME <= 164925), (INCOME - 86375) * .24 + 14751, IF(AND(INCOME > 164925, INCOME <= 209425), (INCOME - 164925) * .32 + 33603, IF(AND(INCOME > 209425, INCOME <= 523,600), (INCOME - 209425) * .35 + 4

I have 2 different kinds of {App Pseud} formats, e.g. kay_obsessive goblin fic (Kiestan) Then I have a formula to spit out a URL, where the concatenate is the important bit: IF(OR({Work Submission Pseud}!={Synced Participant},{Assigned Writer(s)}=BLANK()),BLANK(),CONCATENATE("https://autoao3app.firebaseapp.com/#/mfd2021_round2/user/",SUBSTITUTE(TRIM({App Pseud})," ","%20"))) And it spits out these URLs: https://autoao3app.firebaseapp.com/#/mfd2021_round2/user/kay_obsessive https://autoao3app.firebaseapp.com/#/mfd2021_round2/user/goblin%20fic%20(Kiestan)n The extra character on URLs where the app pseud has a parenthesis always matches the character just before the ). I don’t see what is causing it in the formula. Help!

Hi, I am currently connecting to airtable data via the API. I am able to sort my response in certain fields using desc and asc. However for non text fields in particular single select fields where in the UI you would sort First-Last. What would the parameter be for sorting these fields?

Part of my custom APP takes a number of different user inputs from a UI, and writes some of the resultant information as a new record in a table. In particular I want to do the following: Write a string value to a linked field in an existing table The string value to be passed is already guaranteed to exist as one of the linked options (since the user selected from a Select menu which is in turn populated from the table. *There are some other fields which need to be written as part of this record. The code looks something like the following. `` let machineNameField=tableTasksByMachineNumber.getFieldByNameIfExists(‘Machine Name’); let machineNamesRecord=useRecords(tableTasksByMachineNumber,machineNameField); let machineNamesList=machineNamesRecord.map(record=> {return(record.getCellValueAsString(machineNameField))}); let machineNamesListFormatted=machineNamesList.map(x=>{return({label: x, value:x})});` Machine Selection <Select

I’m developing an API on Node.js + express on Heroku, and every time I fetch anything with POST, I get a 400 Bad request error. Postman requests to the same endpoint work perfectly. So the only hypothesis I have is that I need a ‘Host’ header in my Airtable request (when I remove the ‘Host’ header in Postman. I get the same 400 Bad request error), but I don’t know what this ‘Host’ header should be. To reproduce: Create a simple Node.js+express app on Heroku where you return the same body as you get: app.post('/', (request, response) => { response.status(200).send(request.body); }) Create Airtable automation with fetch (or an app with remoteFetchAsync): let response = await fetch('https://....herokuapp.com/api/', { method: 'POST', body: JSON.stringify('Hello World!'), headers: { 'Content-Type': 'application/json; charset=UTF-8', 'Accept': 'application/json'

Hi, So I’m trying to have a single select field type of small mammal species of the world, that should have a list of more than 300 species to choose from; so when i select an option from this field A, i need a formula field B to be automatically filled out with the relevant ‘Genus name’ based on field A. I have tried using SWITCH, but I am allowed to enter a maximum of only 150 species. Is there a solution that would let me add 300+ options to the formula field, without having to create or link multiple tables?I have no experience in coding, so don’t know if this can be done using the scripting app? Here’s what the formula in Field B-‘Genus name’ looks like: SWITCH({FIELD A},"Hairy-footed Flying Squirrel","Belomys", "Namdapha Flying Squirrel","Biswamoyopterus", "Grey-bellied Squirrel","Callosciurus", "Pallas’ Squirrel (Red-bellied Squirrel)","Callosciurus", "Grey-headed Flying Squirrel","Petaurista", "Large Red Flying Squirrel","Petaurista ", "Spotted Giant Flying Squirrel","Petaurist

I have a table for Users, the primary field is UID, I have email, name, created time. I have a table for Orders. It has Order IDs, email, final price, order place date. I have a table for Cancellations. It has cancellation ID, email, cancellation date. I have a table for User updates. This ha User ID, email, new email (if updated), new name (if updated). All tables are populated automatically from Zapier upon events in another system. I would like to show the SUM of all order placed for the specific User ID under Users, however I can’t seem to link this via UIDs? Does this mean I have to change the primary column in both places to Emails? I need a custom field under Cancellations, that looks up the last date an Order was placed and adds a month to it. However, Cancellations does not have user IDs… will I also have to use emails here? Last but not least, User Update rows might indicate an email update. E.g. a user might now have a new email. What do you suggest I do here if I start usin

I’m using a base to record survey answers. The questions are constructed in a way that all answers are Yes/No. I’m using a single select field for the answers and I want two fields that will provide the total number of “Yes” answers and the total number “No” answers for each record. I realize I can create two additional fields for every question that will store numeric values for each answer choice and then use two SUM fields to provide a total for each type of answer, however this will result in two additional fields for every question. I’m hoping someone has an idea for a more elegant solution; most likely involving a script.

Hi! I’m trying to create a prefilled form URL, but I believe the prefill isn’t working because I’m trying to prefill information with hyphens. Any ideas on how I can make this work? Example: Field Name: Type Name Example data: Apples - Michigan Form URL: “https://airtable.com/shrxxxxxxxx?"&“prefill_”&ENCODE_URL_COMPONENT(“Type Name”)&”="&ENCODE_URL_COMPONENT({Type Name}) How do I make sure “Apples - Michigan” doesn’t break the URL (and in turn, not prefill the form)?

Hello! I am organizing and archiving all the films shown from our local cinema. We started in Google Sheets andhave now imported into Airtable. Now we have seperate fields for the Year the film was shown, the month and the day. Is there a formula that combines the numbered fields to a date field? I can’t seem to find it anywhere. Can anyone help? I asume this isn’t the hardest formula, but I don’t know how to do it. Thanks!

Is anyone seeing any errors for Integromat scenarios which are querying Airtable? Getting “Operation Timed Out” errors across multiple scenarios and accounts. Ruchika

Hi - I think I need a scripting app to solve my problem, but I’m new to Airtable scripting and so am reaching out for help. I have a table that I’m populating every day, but I want a 2nd grid/view with only the latest record showing. So I wanted to use a ‘latestrecord’ checkbox column, and use a script that would fire on a create record trigger to basically reset the checkbox on every row, and then only set it for that latest record that was just inserted. My 2nd view would be filtered on this checkbox - which would then be used in a combination of charts (on all content) and summary apps (just on the latest record) in a dashboard. I’m struggling with the scripting syntax, and so just felt it would be easier to stand on the shoulders of giants, and ask the community! Many thanks in advance, Dave. Note: I’m struggling with even reset all the checkboxes - the following code runs but just doesn’t seem to work (resetting/clearing all of the ‘LatestRecord?’ checkbox columns to be blank/un

So when I first built this IF statement, it brought back “Drabble” correctly when the Word Count was 100 words: IF({Word Count}=100,“Drabble”,IF({Word Count}=200,“Double Drabble”,IF({Word Count}>39999,“Novel”,IF(AND({Word Count}>17499,{Word Count}<40000),“Novella”,IF(AND({Word Count}>7499,{Word Count}<17500),“Novelette”,IF(AND({Word Count}>1000,{Word Count}<7500),“Short Story”,“Ficlet”)))))) Word Count was using the formula provided by airtable help (which is bunk), that is IF({Text}=BLANK(), 0, LEN({Text}) - LEN(SUBSTITUTE({Text}, ’ ', ‘’))+1) When I pasted in a long text that I already knew was 200 words and it brought back 185, I knew I was in trouble. Long story short, it turns out that word count formula in the official help counts the number of spaces and only works if you limit yourself to a single paragraph. So… Now I have the Space Within Paragraph Count field: IF({Text}=BLANK(), 0, LEN({Text}) - LEN(SUBSTITUTE({Text}, ’ ', ‘’))+1) And the Paragraph Count

I’ve done a ton of searching and can’t find any answer to the possibility of using an Array (list) in one field of a base as a search input for another field. I do not want to ARRAYJOIN(values) as I am not searching for the total string, I want to take an input SEARCH list of any values, and search each list item independently within another field. I’ve built a complex Theater scheduling base where there are company members, teams which contain company members, staffing which is a member, availability of each member, and junction tabled them to fit into a scheduling table that is connected to a Show table (date,title) My goal is to be able to compare the list of members on a team against their individual availability. Is it even possible to search each item independently (Team Member) that is within an ARRAY/List (Team) within another field that also contains ARRAY data (Member Availability)? I’m assuming this will need scripting to take the input search list then go one after the oth

I think this may not be possible based on Access fields from linked record in formula without creating Lookup fields - #2 by Kamille_Parks but I’m going to try anyway! I have a pretty complex base based on some longterm needs and incoming records and trying to squeeze things into Airtable that aren’t quite possible. Right now, I’m dealing with two tables that are linked together and linking existing records together. Table 1: Main table. Table 2: Form submission. Table 1: Over a years’ worth of information about people, including linked records of people who are in relationship with one another. Table 2: People who exist in Table 1 one are submitting updated information about themselves AND other people that exist in Table 1. When someone submits information via a form, we link their record to their record in table 1 (usually by the VLookup automation, but since this breaks a lot, often manually.) Table 2 has an important field called “Can you continue to pay for groceries?” that is fi

Hi all! I’ve been working on utilizing scripting within automations for a project. When a record in a table “MPList” is updated, I want to see if that record’s sort name matches any sort names in another table “memList”. If there is a match, I want the trigger record (from MPList) to be updated. I’m attaching my code below, and I think that one issue I’m running into may be with the input.config() process. The “toMatch” string in the test output window displays the ID, Name, and color for the text field it was pulled from. So, I’m not sure whether the Name of toMatch is correctly being tested for equality with the “Sort Name” field from the other sheet on line 9. let toMatch = input.config().toMatch; let mpList = base.getTable("Imported"); let memList = base.getTable("Members"); let query = await memList.selectRecordsAsync(); let result = false; let member = false; for (let record of query.records) { if (record.getCellValue("Sort Name") == toMatch) { await mpList.updateRec

Hi Team, Looking for some assistance on a formula. I want to know if the created date and time is within the last 12hrs. Thanks in advance!

Hello! Im trying to create an automated reminder - after setting a certain date record, a new field will be automatically filled with a date + 30 days Example: I filled the 1/1/21 in Date 1 - and I want Date 2 to be auto-populated with 1/2/21 Help please :pray: :pray: :pray: Thank you! Liran