Skip to main content
Solved

Automations to create new records in a new table

  • October 23, 2024
  • 13 replies
  • 108 views
H
Forum|alt.badge.img+9

I'm a literary agent and I'm trying to create a way to track my submissions of clients work to publishers. 

I have a client table, a titles table and an editor table. It's tricky to do this in any of these because some editors will see multiple projects and keeping the feedback etc separate is giving me a headache. 

In the titles base I have an area where I add editors to a list when I've pitched to them and want to put them on my eventual submission list - at the moment this is a linked field with multiple select capability. 

What I'd like to do is have another table which essentially creates my submission lists and enables me to then go on an record submission dates, responses etc...I've tried to set up an automation so that a new record is created in the tracking table when I add editors to the multiple select field in the titles base so that I end up with a record that is basically TITLE X Editor A and the next field TITLE X Editor C... so that each one will be unique - however, at the moment, what I'm getting is the title and then all the editors selected in that field (so it shows up as TITLE X Editor A, Editor C, Editor Y etc). Is there a way to separate this out so that each time I add an editor it creates a new field? 

Thanks

 

Best answer by TheTimeSavingCo

Ah, thank you, that's really useful. OK, I've now got the formula working so in the formula column I now get - title A - Editor 1, title A - Editor 4, title A - Editor 9 etc...but even with the duplicated base I can't figure out how to automate each of those entries separated by a comma becoming a record in a new table. Even when I try the repeating option I end up with the record in the new table showing as 'title A - Editor 1, Editor 4, Editor 9...

I'd be really happy to pay for your time to help me figure this out...


Ah for that you'd just need an automation that pastes the value of that formula field into the linked field like so:

 

If you're still facing issues I'm happy to jump on a free half hour call to help you sort this and you can grab a time here!

13 replies

M
Forum|alt.badge.img+9
  • Matt_Jastremski
  • Participating Frequently
  • 20 replies
  • October 23, 2024

Have you considered instead of linking titles directly to editors, linking titles to submissions to editors?

Then you can build a titles interface with an "add new submission" form which lets you select editor. Then for every title you can see each submission, with its associated editor. From each submission you can see title and editor (with dates, statuses, etc). And from each editor you can see every submission (with associated title).

H
Forum|alt.badge.img+9
  • HannahS
  • Author
  • Known Participant
  • 21 replies
  • October 23, 2024

Have you considered instead of linking titles directly to editors, linking titles to submissions to editors?

Then you can build a titles interface with an "add new submission" form which lets you select editor. Then for every title you can see each submission, with its associated editor. From each submission you can see title and editor (with dates, statuses, etc). And from each editor you can see every submission (with associated title).


I'm not sure I understand this. 

At what point would the form come into play? And would the form then create a record in a new table? 

I haven't played with interfaces at all yet so I don't really understand what they do or when I'd need them. 

M
Forum|alt.badge.img+9
  • Matt_Jastremski
  • Participating Frequently
  • 20 replies
  • October 23, 2024

I'm not sure I understand this. 

At what point would the form come into play? And would the form then create a record in a new table? 

I haven't played with interfaces at all yet so I don't really understand what they do or when I'd need them. 


Interfaces let you build different ways to work with and view your data. They can start very simple and you can add complexity over time.

So you could have an interface page with all your Titles and on that page add a button that opens a form to add a new submission related to that title. Then on each title record display all linked submissions.

I super recommend trying out interfaces, they enable you to work in a much more structured way with your data. Happy to do a free 15min call if you want me to give you a quick intro!  https://app.reclaim.ai/m/matt-jastremski/matt-chat

H
Forum|alt.badge.img+9
  • HannahS
  • Author
  • Known Participant
  • 21 replies
  • October 23, 2024

Interfaces let you build different ways to work with and view your data. They can start very simple and you can add complexity over time.

So you could have an interface page with all your Titles and on that page add a button that opens a form to add a new submission related to that title. Then on each title record display all linked submissions.

I super recommend trying out interfaces, they enable you to work in a much more structured way with your data. Happy to do a free 15min call if you want me to give you a quick intro!  https://app.reclaim.ai/m/matt-jastremski/matt-chat


Oh, thank you, that would be super helpful - I'll book

TheTimeSavingCo
Forum|alt.badge.img+31

I realize you're already exploring solving this problem in a non-automation way, but in case you're still curious about an automations based solution:

If you add all your editors at once you could try using a repeating group to help with this; you'd use the linked field as the list and create one record per linked editor in that new table of yours

If editors are added one by one (e.g. maybe you add one today, and then another tomorrow), you could create a formula field that outputs "[TITLE] - [EDITOR]", and use your automation to paste that value into the linked field to the new table.  This would create one record per title / editor combination in that new table and the trigger would be whenever the formula field updates.  You'd then use another automation to create the proper links in the newly created record (i.e. linking it to the Editor record, perhaps automatically populating the date, etc)

Here it is working with a manual copy paste:

Link to base

Adam, Airtable Consultant - thetimesaving.company
H
Forum|alt.badge.img+9
  • HannahS
  • Author
  • Known Participant
  • 21 replies
  • October 24, 2024

I realize you're already exploring solving this problem in a non-automation way, but in case you're still curious about an automations based solution:

If you add all your editors at once you could try using a repeating group to help with this; you'd use the linked field as the list and create one record per linked editor in that new table of yours

If editors are added one by one (e.g. maybe you add one today, and then another tomorrow), you could create a formula field that outputs "[TITLE] - [EDITOR]", and use your automation to paste that value into the linked field to the new table.  This would create one record per title / editor combination in that new table and the trigger would be whenever the formula field updates.  You'd then use another automation to create the proper links in the newly created record (i.e. linking it to the Editor record, perhaps automatically populating the date, etc)

Here it is working with a manual copy paste:

Link to base


Oh, this looks exactly what I was trying to do! I hadn't thought to add the formula step in - that makes so much sense. I'll have a play and see if I can figure it out. Thank you

H
Forum|alt.badge.img+9
  • HannahS
  • Author
  • Known Participant
  • 21 replies
  • October 24, 2024

I realize you're already exploring solving this problem in a non-automation way, but in case you're still curious about an automations based solution:

If you add all your editors at once you could try using a repeating group to help with this; you'd use the linked field as the list and create one record per linked editor in that new table of yours

If editors are added one by one (e.g. maybe you add one today, and then another tomorrow), you could create a formula field that outputs "[TITLE] - [EDITOR]", and use your automation to paste that value into the linked field to the new table.  This would create one record per title / editor combination in that new table and the trigger would be whenever the formula field updates.  You'd then use another automation to create the proper links in the newly created record (i.e. linking it to the Editor record, perhaps automatically populating the date, etc)

Here it is working with a manual copy paste:

Link to base


Ok, I'm stumped...when my editors field currently has several in it, how do I do a formula that it only picks one? 

TheTimeSavingCo
Forum|alt.badge.img+31

Ok, I'm stumped...when my editors field currently has several in it, how do I do a formula that it only picks one? 


Hmm, which one would you want it to pick?  May I know why you'd want to do that though? 

Adam, Airtable Consultant - thetimesaving.company
H
Forum|alt.badge.img+9
  • HannahS
  • Author
  • Known Participant
  • 21 replies
  • October 25, 2024

Hmm, which one would you want it to pick?  May I know why you'd want to do that though? 


Ah, my question wasn't clear. So, in your example when the calculation runs you end up with 'Book 1 - Editor A' separately from 'Book 1 - Editor B' - all I've managed to achieve is 'Book 1 - Editor A, Editor B...' - I can't figure out how to write a formula so that it repeats Book 1 but chooses a different result from the Editor column each time. Even if i set it up and then add the editors at different times, it just adds the new editor into the long calculation rather than making a new entry. 

TheTimeSavingCo
Forum|alt.badge.img+31

Ah, my question wasn't clear. So, in your example when the calculation runs you end up with 'Book 1 - Editor A' separately from 'Book 1 - Editor B' - all I've managed to achieve is 'Book 1 - Editor A, Editor B...' - I can't figure out how to write a formula so that it repeats Book 1 but chooses a different result from the Editor column each time. Even if i set it up and then add the editors at different times, it just adds the new editor into the long calculation rather than making a new entry. 


Ah, I see!  Try this formula:

Name & " - " & SUBSTITUTE( Editors, ",", ", " & Name & " - " )


So sorry, I should have mentioned that you can duplicate the base to your own workspace in order to get access to the formula and automation!

Adam, Airtable Consultant - thetimesaving.company
H
Forum|alt.badge.img+9
  • HannahS
  • Author
  • Known Participant
  • 21 replies
  • October 27, 2024

Ah, I see!  Try this formula:

Name & " - " & SUBSTITUTE( Editors, ",", ", " & Name & " - " )


So sorry, I should have mentioned that you can duplicate the base to your own workspace in order to get access to the formula and automation!


Ah, thank you, that's really useful. OK, I've now got the formula working so in the formula column I now get - title A - Editor 1, title A - Editor 4, title A - Editor 9 etc...but even with the duplicated base I can't figure out how to automate each of those entries separated by a comma becoming a record in a new table. Even when I try the repeating option I end up with the record in the new table showing as 'title A - Editor 1, Editor 4, Editor 9...

I'd be really happy to pay for your time to help me figure this out...

TheTimeSavingCo
Forum|alt.badge.img+31
  • TheTimeSavingCo
  • Brainy
  • 6457 replies
  • Answer
  • October 28, 2024

Ah, thank you, that's really useful. OK, I've now got the formula working so in the formula column I now get - title A - Editor 1, title A - Editor 4, title A - Editor 9 etc...but even with the duplicated base I can't figure out how to automate each of those entries separated by a comma becoming a record in a new table. Even when I try the repeating option I end up with the record in the new table showing as 'title A - Editor 1, Editor 4, Editor 9...

I'd be really happy to pay for your time to help me figure this out...


Ah for that you'd just need an automation that pastes the value of that formula field into the linked field like so:

 

If you're still facing issues I'm happy to jump on a free half hour call to help you sort this and you can grab a time here!

Adam, Airtable Consultant - thetimesaving.company
H
Forum|alt.badge.img+9
  • HannahS
  • Author
  • Known Participant
  • 21 replies
  • October 28, 2024

Ah for that you'd just need an automation that pastes the value of that formula field into the linked field like so:

 

If you're still facing issues I'm happy to jump on a free half hour call to help you sort this and you can grab a time here!


This is fantastic, thank you so much. I've got it working now! 


Terms & ConditionsAccessibility statement