Skip to main content

Hello!



I’m trying to merge the data from 2 separate fields whenever they are updated into another field to keep a history of those changes.



The first field is date and time, the second field is a single select of location. Ideally, this information would just automatically copy to the next line down of a long text field whenever it’s changed.



I have no idea the best way to go about this. Thanks for your help in advance!

Hello,


If I understand right - you want to create a new record every time the date/location is updated?


In such case, I think that a simple automation can help - you can see an example here


This won’t create a long text field but a list of changes in another table,


which you can link back if needed.


Just a note to endorse @Chen_Pro’s approach. I do this in a few places and it works well. Specifically, I have a number of automations that each watch a particular field. When that field is changed, it creates a new record in a table called Changes with columns:







  • Name. Formula for Created Date




  • Field Changed. Text. Automation just writes the name of field its watching.




  • New value. The value of the watched field.



Hello,


If I understand right - you want to create a new record every time the date/location is updated?


In such case, I think that a simple automation can help - you can see an example here


This won’t create a long text field but a list of changes in another table,


which you can link back if needed.


Thanks for the response! I’m attaching some screenshots of how I would like it to work, I’m sure there’s a prettier way to do it but this is what I’m looking for. The first screenshot is just showing what fields I’m talking about and the second is showing how I’d like the data to look in the long text field. You’ll see that Item 1 went to Kevin’s on the 1st, Jeff’s on the 5th, and the Rock Hall on the 8th. That information would be copied to the long text field as it is changed.






Just a note to endorse @Chen_Pro’s approach. I do this in a few places and it works well. Specifically, I have a number of automations that each watch a particular field. When that field is changed, it creates a new record in a table called Changes with columns:







  • Name. Formula for Created Date




  • Field Changed. Text. Automation just writes the name of field its watching.




  • New value. The value of the watched field.



Thanks for the response. Replying to you as well so you can see the info I added above.


Thanks for the response. Replying to you as well so you can see the info I added above.


I think you could use an automation that watches either field for a change and on any change grabs the out-to-history field, concatenates the other two values, and saves it back.



Note I bet you will have some difficulty getting the line break you want.


I think you could use an automation that watches either field for a change and on any change grabs the out-to-history field, concatenates the other two values, and saves it back.



Note I bet you will have some difficulty getting the line break you want.


commenting to flag so that i can come back to this after putting some more thought into it.


How did you get it into the field in your screenshot did you just type that in for examples sake?


i also agree with automation but it will have to be set up sort of specifically. happy to help just need some time to play with it.


commenting to flag so that i can come back to this after putting some more thought into it.


How did you get it into the field in your screenshot did you just type that in for examples sake?


i also agree with automation but it will have to be set up sort of specifically. happy to help just need some time to play with it.


Awesome. I just typed that in for example, yes. I’m fairly new to automatons, so all help will be appreciated. Thanks.


Awesome. I just typed that in for example, yes. I’m fairly new to automatons, so all help will be appreciated. Thanks.


Example Base Link


Video Of How i Built the Automation


I recommend watching that video on mute and 2x speed lol.


Example Base Link


Video Of How i Built the Automation


I recommend watching that video on mute and 2x speed lol.





use that button and it should give it to you





use that button and it should give it to you


Well this is fantastic and working great in the test base! Thank you so much. However, when I try to set it up in the actual Base I’ll be using it in, I keep getting an error on the Actions part of the automation. The error is ‘“Fields” could not be converted to a string.’



Any ideas?


Can you post please a screenshot of this error?


btw, I have a similar question - I need to get a list of dates from two colmns, like so:





(I want to update the “note” column only the amount of days from “start” to “end”)


is it possible?


Thanks!


Can you post please a screenshot of this error?


btw, I have a similar question - I need to get a list of dates from two colmns, like so:





(I want to update the “note” column only the amount of days from “start” to “end”)


is it possible?


Thanks!






use that button and it should give it to you


Forgot to reply directly to you earlier. Let me know if you have any thoughts on the error I’m receiving. Thanks so much!


Can you post please a screenshot of this error?


btw, I have a similar question - I need to get a list of dates from two colmns, like so:





(I want to update the “note” column only the amount of days from “start” to “end”)


is it possible?


Thanks!


Hi,



Yes.


you can insert script step inside (after trigger) and use output for step 3


i don’t learn yet how to work with date in JS, so i found some function, reconstructed and tested, and it worked, but for “ISO” format, YYYY-MM-DD , so you should change your field, or adjust the code.





regarding variables for input config(), i tried just “insert” field, but it wprked with zero arraay lenght, then did some fixes yet and it worked OK


sometimes “continue” - “uppercase(or capitalize)” works where “insert” - not.


but i’m not sure, maybe insert will work for you




p.s. for (let dt=date1; dt<=date2; dt.setDate(dt.getDate()+1))


Forgot to reply directly to you earlier. Let me know if you have any thoughts on the error I’m receiving. Thanks so much!


Maybe the formatting of the calculation field is set to date, and that why it can’t get a string?



Hi,



Yes.


you can insert script step inside (after trigger) and use output for step 3


i don’t learn yet how to work with date in JS, so i found some function, reconstructed and tested, and it worked, but for “ISO” format, YYYY-MM-DD , so you should change your field, or adjust the code.





regarding variables for input config(), i tried just “insert” field, but it wprked with zero arraay lenght, then did some fixes yet and it worked OK


sometimes “continue” - “uppercase(or capitalize)” works where “insert” - not.


but i’m not sure, maybe insert will work for you




p.s. for (let dt=date1; dt<=date2; dt.setDate(dt.getDate()+1))


Thanks a lot!


-I’ll try it…


Maybe the formatting of the calculation field is set to date, and that why it can’t get a string?



Here’s what the formatting says?




Here’s what the formatting says?




ok not a date formatting… what is your formula then?


ok not a date formatting… what is your formula then?




This should work normally…


Is the three dots in the fields input set to “Field values”?



This should work normally…


Is the three dots in the fields input set to “Field values”?



It sure is. Is that correct?


It sure is. Is that correct?


Yes it should be “Field values”.


Can I copy an example of your table?


-Can’t figure out what went wrong…


Yes it should be “Field values”.


Can I copy an example of your table?


-Can’t figure out what went wrong…


I don’t think so. There’s too much confidential information in the table that I’d have to remove, even if I don’t include the records. Maybe @Rebecca_Elam will be able to chime back in and see an issue. Thanks for your help thus far.


I don’t think so. There’s too much confidential information in the table that I’d have to remove, even if I don’t include the records. Maybe @Rebecca_Elam will be able to chime back in and see an issue. Thanks for your help thus far.


I see…


the only thing I can think of is the little gap in your formula-





-but I tried it this way and it worked too…


hope you’ll find a solution… good luck!


Just a note to endorse @Chen_Pro’s approach. I do this in a few places and it works well. Specifically, I have a number of automations that each watch a particular field. When that field is changed, it creates a new record in a table called Changes with columns:







  • Name. Formula for Created Date




  • Field Changed. Text. Automation just writes the name of field its watching.




  • New value. The value of the watched field.



It’s always scary to see so much activity regarding an extremely simple requirement. It suggests Airtable may be missing something very basic, and surely it is missing many very basic things; change logs is one of them.





This is a good approach but it has some downsides -







  1. The context of the change history is external to the data related to the history. This can be overcome with linking as pointed out, but even so, this can be disconcerting and less than an ideal UX for every-day people.







  2. Complexities and volume created as a result of logging relationships can create a number of performance issues depending on the velocity of change.







  3. When it comes to sorting, reporting, exporting, and a variety of possible compliance objectives associated with the logs, it’s a double-edge-sword. What if the log data needs to be tamper proof?







@Chris_Messina1 asked for a very simple approach; I’m not sure there is one and I wonder why the folks at On-to-Air don’t have a tool for this?


Reply