Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Automatic input in column shouldn't create new record in linked table

1583 1
cancel
Showing results for 
Search instead for 
Did you mean: 
LeviM
4 - Data Explorer
4 - Data Explorer

Hey there,

I have the following set up.
There are 2 columns in table 1.
Column A “Monday of the week” is a formula field that calculates the Monday of a week.
Column B “Manual weekly entry” which is a linked field to table 2.

Table 2 has a primary field “Monday of the week”. There is also a roll up column that rolls up all the record in Table 1 that are part of that Monday of the week.

When copy pasting manually Column A in table 1 to Column B in table 1 everything works perfectly and there is only 1 Monday of the week with a roll up of all the record that should be in that roll up.

However, when trying to automate the copy pasting through Airtable Automation (or Zapier, the result is the same) then the pasting is happening correctly, but in Table 2 we see that a duplicate Monday of the week in the primary field is created with each time only 1 record in the roll up.

Is there a way how this copy paste can be automated without creating a duplicate Monday of the week with a single record in the rollup in Table 2?

Thanks in advance!
Levi

6 Replies 6

Sounds like there is something wrong with your automations. If the text of the Monday doesn’t exactly match an existing record in the other table then it will create a new record.

Are there extra spaces that are being inserted with your automation? can you post some screenshots?

LeviM
4 - Data Explorer
4 - Data Explorer

Schermafbeelding 2022-08-03 om 16.27.26

I presumed as well that this was the case , but the automation is simply an input of the “Monday of the week” aka column B field into the “Manual weekly entry” field aka column A.

I’m not sure how it can make any difference compared to a manual ctrl + c and ctrl + v

Is {Monday of the week} a Date or String?

LeviM
4 - Data Explorer
4 - Data Explorer

Schermafbeelding 2022-08-04 om 09.59.41

This is the formula we’re using for {Monday of the week}. I presume the result is a string, but I’m not sure tbh…

The formula in your screen shot produces a date/time value. If the original {Reported At} has a time in it, that time is part of the formula value, even if you are not displaying the time. Thus if the original times are unique, the formula results will also be unique even if they look like the same day.

The easiest fix is to nest your existing formula inside a DATETIME_FORMAT() function that shows only the date and not the time. The result will be a text string. Note that this may also have implications for the primary field in the linked table.

LeviM
4 - Data Explorer
4 - Data Explorer

Thanks for the info.

I tried formatting the date through Zapier, but I’ll try it with the datetime format formula as well.
Thank you!