Solved

Hi! I would like to use the WORKDAY function to count the number of working days from an input date, but I would also like to include the input date as well. For example, if I count 6 working days from 19-Aug, the WORKDAY formula would count until 27-Aug. However, I want 19-Aug to be included in those 6-days – so by right, 6-working days from 19-Aug, including 19-Aug itself, should be 26-Aug. How do I include that in a formula? Thanks in advance for any help! :grinning_face_with_smiling_eyes:

Hello, I’m trying to calculate an hourly rate using a duration field and a currency field. I have a formula field that calculates $/hr: ({Earnings}*3600)/{Hours Worked} However, I have a few records where I don’t have the number of hours worked, just the earnings amount. The formula calculates this as “infinity,” which messes with my sum of the entire column. Is there a way to tell this formula to leave the cell blank if there’s no hours worked?

When using Sanity it’s really important all of our entires in our base have unique id’s not just on tables but in the base itself. Are the id’s for the entry unique to the table or the base?

what is the simplest way to convert a text field which is the three letter month '-'two letter year (Dec-02, May-18, etc) to a usable date field of the same format. The day of the month isn’t important, could be set to 01.

I need to be able to calculate the number of working days between two dates determined in “Start Date” and “End Date” columns. I know the DATETIME_DIFF formula exists, but it includes weekends and public holidays – I need only working days to be calculated. Is this something that can be achieved with a Script block?

I would like to capture the record number that appears on the LHS of each record in a particular view in a field (when you hover over the record this number turns into a check box). I want to print labels for each record, and I want to have a field which shows what number the label is out of the total records in that view (i.e. “3 of 10”). Or is there another way to calculate the order of records using formulas? Any help would be much appreciated!

I would like to know how to Count records from different table with specific conditions? For example I have a table called “Stats” where I would Like to put some spesific information (automatically) for example number of boys and girls (based on another table called Students with a field called sexe) ?

Hi! I have a rollup field (using ARRAYUNIQUE) that should always produce a single array value. I want to pretty it up and remove the encompassing quotation marks, but I can’t seem to figure it out. I’ve tried all of the following formulas, but none of them seem to work. SUBSTITUTE({Vendor Rollup}, '"', '') SUBSTITUTE({Vendor Rollup}, '/"', '') SUBSTITUTE({Vendor Rollup}, """, "") SUBSTITUTE({Vendor Rollup}, "/"", "") Can anyone help me shed some light on my issue?

Is it currently possible to limit the selection options based on the single select conditional field? Example: Single select is ABC or DEF, which makes the next multi-select field visible based on the ABC condition being met. Can I limit the multi-select options based on the ABC selection?

Hi, We have a base that, amongst other things, generates requests and records when the request is completed. To provide feedback on performance, we measure the number of working days it took to complete the task using: WORKDAY_DIFF({Request Date},{Time Processed}) We can then use a Summary Block to give an average “headline" of the number of working days taken to complete. We then have then used the following to report the average during the previous month: IF(DATETIME_FORMAT({Request Date}, “YYYY-MM”) = DATETIME_FORMAT(DATEADD(NOW(), -1, “month”), “YYYY-MM”),WORKDAY_DIFF({Request Date},{Time Processed}),BLANK()) So here, during Aug, it will give the average for July. How can we have this a little more “live” by reporting the number of days taken to complete over the past 30 days?

Months Active is a formula that returns what appears to be a number. IF({First Gift Date}, DATETIME_DIFF({Last Gift Date},{First Gift Date},‘month’),"") Number of Gifts is a count field I’m trying to create a field that returns an frequency {Months Active}/{Number of Gifts} But it is throwing an #Error What am I over looking? I’m pretty sure it has something to do with the DATETIME_DIFF returning a date and not a number

Hi I simply need to add some records to a table into a "Linked Record’ field. I struggle to understand why it’s so difficult. I have this VIEW: and I want to add single and concatenated ‘Linked Orders’ via a script. This my array of records I’d like to add… Here is my code and results… full code let table = base.getTable("Daily Orders"); let ws = base.getTable("Production Required"); let view = table.getView("Unfulfilled Daily Orders"); let sku = table.getField("Line Items: Sku"); let ordernum = table.getField("Order: Name"); let currsku = ""; let ordstr = ""; let result = await view.selectRecordsAsync({ sorts: [{field: sku, direction: "asc"}] }); output.inspect(result); let ordersArray = []; for (let record of result.records) { ordernum = record.getCellValue("Order: Name"); currsku = record.getCellValue(sku)[0]; ordersArray.push({ fields: { "Order_Num": ordernum, "SKU": currsku }

I have built a dashboard pulling in data from 2 different views, both of them filtered by a formula field that prints out the month. For instance, if it’s August the formula returns 8 and I manually filter both views by number. Once that’s done my blocks get updated and I can see the monthly dashboard. I would like to achieve this dynamically from the dashboard by having an input field/dropdown, in which I would just type in the number to filter the view, rather than having to do this manually in the view itself. I’ve looked at the Views API but I can’t find anything that would the job. Is this achievable?

Trying to automate adding/remove users from systems. Working fine… except I can’t understand how to get a “forever” value to return “KeepUser” while using this formula. IF(IS_AFTER({Time math}, {Now}),“KeepUser”, “RemoveUser”) My select values are all numbers, except “forever”. I’d like to keep it as a text option, but I’m not sure how to get around this in a formula?

Hi I am converting a Google sheet to Airtable. In Google sheet I have a formula that: Counts down 7 days from Last touch to 0, this acts as a reminder. If a follow up date is set, it counts down the number of days from the reminder date If certain conditions are met (e.g. dead/stop) shows '-" You can see it working here: https://docs.google.com/spreadsheets/d/1sca1LgSu46ouOAigppAQYfQNwW48vOgu3c9wcux5HCE/edit?usp=sharing The formula I am using is: =if(and(F2>0,F2>E2),F2-today(),if(or(E2=0,C2=“11 - stop”,C2=“11 - dead”,C2=“09 - lead (Booked a call)”,C2=“10 - sale”,C2=“08 - email follow up sent”),"-",E2-today()+7)) Can you please help me to write this in Airtable? Thank you

Hi, I would like to know if it is possible to dublicate data from a table to another one (with more fields) For example a table called STUDENTS (with students informations) And another one called MARKS (for students marks), so basically student information must be in MARKS table too. and if I update a record in a table the record must be updated automatically in other one! How can I do that please?

Hi everyone, I’m new to Airtable. I’m hoping I’m missing something really obvious here! When linking to a record in another table, AT gives you the option select multiple records. However, it doesn’t let you chose the same record twice. Why is this, and is there any easy way around it? For context, I’m trying to set up a base for a non-profit diaper bank. Diapers come in about 8 sizes. Sometimes, families have two or more children who wear the identical size of diaper (e.g. twins). Currently, the base will let me use a single field [“Diaper Bundles”] to indicate that a family received size 1 diapers and size 5 diapers, for example. But if I want to indicate that they received multiple qualities of size 1 diapers, as well as size 5 diapers, things get complicated in a hurry! Any suggestions here? I’d love some advice!

Hi, I am a new user… can someone help how to get the amount of the time duration? Thanks,

Hello, Sorry for bad English. I created a payment tracker, which allow me to track every rent from tenants. You can find the sheet here (translate in English). Usually, rents are grouped by tenants, but for the translation I do not have copy them. When I receive a payment, I write on the record the date of receipt, the means of payment and I check the “Done” field. To quickly see what rent I need to claim for the month, I am looking for a formula that shows “LATE PAYMENT” in red for every rent that I do not receive when the “Due” date is over. Exemple : Every rent for September must be paid at least on 5th September. If a tenant do not paid the rent on 6th September, the formula field in “Rent of September” will be shows “LATE PAYMENT” in red. Can you please tell me what formula I need to write to make this happen ? Thanks.

Hi new to Airtable, and getting stumped combining data on 1 table from 2 other tables. using 1 table to reference another table. The setup is: I have 3 tables Staff/Projects/Billing, in the projects table I assign staff to a project and count the hours in the staff table I have each staff member and their rate and location in the billing table I list a billing line item link it to a project and then from the projects table I pull in the assigned staff member and the hours worked. I then want to perform a lookup on the staff table using the staff name pulled from the projects table, using this to get the country and the price. But this is where I am getting stumped. The obvious answer is that I pull the staff data to the projects table and then pull that data again to the billing table, but I want to avoid the doubling up if possible as in the long term this will be used on a largish database so any optimization I can achieve will help. FYI in this situation, linked columns are single

So, I have thirty columns of test result records in which some students did not submit work. In these cases, there is not a zero but a blank. This maintains an average in the average formula column which is unskewed by zeros from lack of submissions. However, I want to automate a running total of test results recorded (not the score, just the presence of a score being recorded), so that I can see who is falling behind in their submissions and it really only needs to total the number of populated fields to the left of it. I am a beginner and assume that this requires a formula of some sort. :slightly_smiling_face:

I have created a new empty table with a form view. I want the user to be able to select records in the form, that are pulled from a different table with two columns (make & model, years) Not sure how to achieve this. Every time I create a linked record it just pulls the make and model of the car. Not the years.

Hi all Hope you can help. Using the scripting block, how could I loop through the records and return the value of a lookup field. All other fields are fine but linked and lookup fields throw an error. Here’s my code… let table = base.getTable("Daily Orders"); let view = table.getView("Unfulfilled Daily Orders"); let sku = table.getField("Line Items: Sku"); //Lookup Field let result = await view.selectRecordsAsync({ sorts: [{field: "Line Items: Variant Id", direction: "asc"}] }); for (let record1 of result.records) { output.text(record1.getCellValue(sku)); // throws an error } ERROR Error: Unknown value ‘AlexSaleBeanieAdultCharcoal’ passed to output.text() at main on line 12 Please help! Thanks Claire

Wondering if it’s possible to format within the chart block. For example, I have a scatter chart, and the dots are tiny. I want bigger dots!!! Possible? I didn’t see it. Thank ya

Hello Community! How are you? My name is Federico and I am having a question about the bases. I have a big base where there are 5 tables inside. I need to change one of the column so I can create a new base to add the CRM. The question is: I have the column that says “Name” and i want to change the first column to a formula and then concatenate the name and surname. The link will be the same name but changes the field to a formula instead of the short desc. This table is connected to a table that give course to all the clients orders. Can I change the range of a register without changing the linked record with another base? If I change the row that is linked to another table, it will crush? I will be waiting for an answer!