Solved

Hi all, Is there a way to sum the number of years passed (as a whole number, rounded down) from a listed date to present? I’m using this for an asset register. There are 4 important columns: A) date of purchase B) original purchase value C) annual depreciation (as a %) D) current value I’m looking to write a formula for current value (D) that will multiply the annual deprectiation % © by the number of years that has passed since the date of purchase (years since A), then multiply that by the original purchase value (B). For example, if 3 years have passed since purchase date, the original price purchase value was $1,000, and the depreciation is 16.67%, it would be ((3*.1667)*1,000), returning a current value of $500.10. How do I get that 3 to caclucate automatically in a formula?

So I know this shouldn’t be all that difficult, and I get the results for my first two outcomes (Blank and 1 Year), but no years past Year 1 provide an answer. IF({Last Service Date}=BLANK(),BLANK(),IF({Service Frequency}=“1 Year”,DATEADD({Last Service Date}, 365, ‘days’,IF({Service Frequency}=“1.5 Years”,DATEADD({Last Service Date}, 547, ‘days’,IF({Service Frequency}=“2 Years”,DATEADD({Last Service Date}, 730, ‘days’, IF({Service Frequency}=“3 Years”,DATEADD({Last Service Date}, 1095, ‘days’, IF({Service Frequency}=“4 Years”,DATEADD({Last Service Date}, 11460, ‘days’))))))))))) “Last Service Date” is a Calendar Type and “Service Frequency” is a Single Select Type (Single Line Text had same result). In Excel I can use a different formula that works just fine, but is not recognized here

We have two columns that we need added to a third sum column. Our first column is our Admit Date for people using our program and the second is Exit Date for when they leave the program. We need our Admit Date and Exit Date columns to populate to a total days in care. Right now we have the formula as: SUM({Exit Date}-{Admit Date}) but it is giving us a message of NaN when an admit and exit date are plugged in.

I have a formula for calculating when I can plant seeds, according to the LFD (last frost date), which is and will remain 3/29/2020. Weeks is how many weeks before the LFD I can plant the seeds. The formula: DATEADD({LFD},-{Weeks Start},‘weeks’) Currently I have a whole column for LFD that is set to autofill 3/29/2020 into every row, of which I have 350+. Instead of this whole column for LFD, is there a way I can somehow reference this number one time within a formula, or within one cell somewhere instead of a column of 350 rows with the same number?

I have a formula to calculate how many plants I can fit in a 10x12" square. My formula goes: 10/Spacing * 12/Spacing. (Spacing is a column where I record the number of inches of distance a plant needs). The formula works, except for in two instances- 1. When I have not filled out the spacing column yet, the formula produces ‘INFINITY’ as the result. I’d like it to return a blank field instead of returning ‘INFINITY’. 2. When the plant requires two 10x12" squares (i.e. when the Spacing is 24"), the formula produces 0. I would like that changed to 2, or even better, an actual calculation with a decimal, like 1.5, 2.5, etc. Can anyone help? I’ve searched similar issues but can’t find anything that helps me.

As many would of done, I got the email today regarding scripting. I cant do the webinar due to it being 6am on a Saturday here in Australia but before I spend to much time researching this can scripts work off a submitted form to automatically create links to other tabs? We currently have this as manual process on our Real Estate base where a form is submitted with property details etc. We then have to manually copy the property address and put in in upto 2 new columns depending on a status column. This allows the linking and lookups created to work on other tabs. If not, thats fine, just thought I would ask because this would help us immensely in automating stuff!

I have a song database and I want to keep track of every date I’ve used/played each song, essentially a playlist. Ideally this would be a list of dates listed from newest to oldest. Any suggestions as to how to do this?

I am a researcher, I’ve set up my base so one table has data about my interviewees and the other table has the feedback I’ve gathered from the interviewees. I need to correlate between the feedback and categories in the interviewee table.

Dear community I am trying to add colour to a bunch of records, however, I do not want the colours to be linked to the content of the record. I wonder if there is a way to manually add colour to groups of records, so that not all records that have the same text have the same colour. Is there a way to do this? Best, T

I have images inserted as one of the fields on grid view, is there a way to bring them to show on calendar view as well?

Hey, So I’m trying to achieve a reservations / booking calendar type thing for a hostel. I have set up everything I need apart from the last part. I need to be able to see each rooms availability plotted across a calendar or chart, I achieved it in excel as per the screenshot below: I thought I would be able to achieve it with a pivot table but I have had no luck. I love the way airtable works so it would be great if anyone had any ideas or suggestions as to how I could achieve this! Thanks, Mark

Is it possible to convert integer values into ASCII characters in Formula columns? Pseudo code example: convertToChar(71) returns the char ‘G’.

Hi there, There is probably a solution I am not seeing here because I can no longer see the forest for the trees so to speak - if anyone has any advise it would be most appreciated! I have scoured support/community but cannot seem to figure it out. I have one table where the primary field may have duplicate entries for the same entity. For example, assume we have Table A: Mon - 13 units Tue - 25 units Wed - 5 units …etc Mon - 54 units Tue - 18 units …etc etc From the above, over a period of time each day of the week will have more and more entries. So how best to add up all the units for each day i.e. what is the total for units for ‘Monday’ for all time? My understanding is that, on another table (Table B), I would need to link a single record to each ‘Monday’ record on Table A, then create a rollup field and use the SUM() function on the Units field from Table A. But this involves having to manually add in the link for each Monday with each new week. And if I already have a list tha

I need to filter the output of a table to only include records that contain the word “attractions” in the Categories field. I know it’s not correct but here’s what I tried: If({Categories} = attractions) Can someone tell me where I’m going wrong in the code? Thank you in advance for any help.

Is AirTable available in Russian and other languages?

Hello there! I have a lot of text-rows with following time-layout: Feb 15, 2020 8:22 AM. I can’t get it to import to my airtable styled rows: 15/2/2020 8:54. Does anyone know what I can do? Thanks!

How can I get a custom block that I have $ block release'd back into a development state? Would I need to start a new custom block, point it at the same repo as a remote, and develop there?

I’m trying to implement a settings menu in my custom block. I have the settings component properly showing, and the settings inside working as I want. The only missing piece that I can’t figure out is how to trigger closing the menu from a Button component’s onClick() function, rather than having to click the settings icon again to close the menu. I have this in my main block component: const [isShowingSettings, setIsShowingSettings] = useState(false); useSettingsButton(function() { setIsShowingSettings(!isShowingSettings); }); I tried calling the useSettingsButton() function inside the onClick() lambda, but got this error: Error: Invalid hook call. Hooks can only be called inside of the body of a function component. This could happen for one of the following reasons: 1. You might have mismatching versions of React and the renderer (such as React DOM) 2. You might be breaking the Rules of Hooks 3. You might have more than one copy of React in the same app See https://fb.me/react-i

I have a multiline text field and I would like to use a formula field to take that multiline text and use the existing content but add a bullet point character in front of each new line. Is this possible? I’d like it to happen automatically without manually having to run a script Part 2 - If that works, I’d like to take the bulleted list and split it after 10 lines into a second field. Is this possible?

I’m trying out the new scripting block to try and make a utility for our base that will delete records based on a field and then delete the field itself. Is this possible? Use case: We use a table for semester data and then link it to all of the management tables. Each semester we make a new table and have to archive and update the linked field in all of the management tables. Semester data overlaps, so we can’t use the same table. We audit our fields and sometimes find that we no longer use the field and we want to archive all old records that used that field. Also, is it possible to export the filtered results to a CSV from a script? I would love to have this as part of the flow. I’m a beginner at code but, here is my attempt from looking at a bunch of examples: //Script to delete all records associated with a specific field and then delete the field. //6-3-2020 output.markdown('# Batch Delete Records and Field'); output.markdown('#### Before use, download CSV of sorted record

hellow everyone! bandwidth is limited for free plan? or i get unlimited bandwidth with free plan?

I have an age formula that returns an “error” if no birthday is available. How do I remove the “error”

I used a simple formula for column C (it is A-B), it shows up as -0 in one case and 0 for another? Why should zero have a negative sign? In my case although all 3 columns are setup to round to 2 digits, I am certain that there is no third decimal digit in either column A or B. Reason for asking is that it messes my colour codes where I’ve setup anything less than zero as red.

I have two tables, one that is built in-house (say, INTERNAL) and one that is supplied externally (say, EXTERNAL). I don’t trust my external data source, so what I want to do is find all records present in INTERNAL and missing from EXTERNAL. If I were working in SQL, the query I would write would be something along the lines of: SELECT a.*, b.* from INTERNAL AS a LEFT JOIN EXTERNAL as b ON a.primary_key=b.primary_key where b.primary_key IS NULL I want to avoid using record linking; there are over 100 records to do this for, and my understanding of linked records is that they have to be done manually. Having to do this manually would defeat the entire purpose of using a database tool to speed up my workflow. Can the above analysis be done strictly within Airtable, or will I need to write an API script to emulate a JOIN?

IF(AND({Total Pages}<“50”,{Total Pages} >“43”,"$3.12"),IF(AND({Total Pages}<“12”,{Total Pages}>“1”,"$1.67")) What am I doing wrong? I’m trying to calculate postage by ranges…stating if Total pages is less than 50 and greater than 43, then $3.12, if total pages is less than 12 and greater than 1, then $1.67 and so on.