Automations to create new records in a new table

[HannahS]

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

[Matt_Jastremski]

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).

[HannahS]

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. 

[Matt_Jastremski]

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

[HannahS]

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

[TheTimeSavingCo]

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

[HannahS]

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

[HannahS]

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]

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

[HannahS]

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.