Splitting records with many selected multiple-select options into individual records in a new ta

[sg]

Hello,

I am endeavoring to create a social-media post manager/tracker to coordinate reposting of the same centrally-approved content on the same dates by many different agencies for various campaigns.  The first table contains a record for each content item with the posting date(s) assigned, along with the agencies who will need to post; since there may be multiple posting dates, instead of a Date field, I have added a table just for dates to make this a Linked Record selection.  

My second table, which has a row for each selectable date, pulls in the content to be posted on each selected date with a lookup for the agencies tied to each. This is intended to be the table that contains views specific to each agency for their own status updates, filtered to show each agency's approved content assignments (via the "Msg Approved by City" checkbox). This is how I would like it to work when the base is put into use.  Note that I tried to use Autonumber for the primary field here, but was unable to get Airtable Automations to create new records with that info.  Instead, my first table's primary field is a formula to display the Record ID, which is then passed to the second table and tied to each posting date to create a Post ID.

Screenshots below:

I am hoping to find a way to break these rows out further so that each agency has its own row for each content item + post date (Post ID) -- the Post ID would be repeated x the number of agencies posting the content each time.  This would allow me to create personal views for each agency representative so that they see only their assigned posts and can indicate the posting status for each dated post via a generic "posting status" field instead of me having to make individual status fields for each agency.  

I have read up on junction tables in the forum and tried a few published scripts, but I am not very familiar with JavaScript and have not been able to get this to click.  I have also tried using Automations' repeat-for-list function, which did create separate records for each posting date, but did not break the agencies out -- the entire group of agencies is copied across all new records, as expected.

I am hoping for some guidance on how best to manage this via a junction table, or perhaps I am overthinking and there is a non-automated, non-scripted way to do this in Airtable?  I am also mindful that my database schema is starting to look like a hairball with all of my attempts at tying things together between my content-manager and post-tracker tables.  I think that I could consolidate this into one table except that I am unable to use a Date field to select multiple posting dates, which is why I created the second table to begin with.

The end result would hopefully be that agency representatives can log in to use their assigned personal views to update the status of their assigned posts, and once all agencies select Complete on all individual dated posts for a content item, I would have a formula field in play in the Content Manager to indicate that the Content Item is wholly complete.  The status fields for the posts + the date of each post would also feed into a formula field in the Content Manager to indicate when an agency last engaged to post an item so that the City can track engagement in a low-key way; we are in the building stage and are not sure how many regional nonprofits will be interested in cross-posting or reposting content, and it would be great to include a dashboard to track this for City stakeholders.

Thank you in advance for any help, I really, really appreciate it!!

Sydney

(edited to add labels to my post)

[TheTimeSavingCo]

You should be able to do this with a repeating group automation like you mentioned actually.  If you could invite me to your base I can set it up for you real quick

[sg]

I have just sent an invite -- Thank you so much!! 🎉

Sydney

[TheTimeSavingCo · Accepted solution]

No worries!

I've just added a new automation to the base that looks like this:

And the tricky bit is here where we set the "Depts / Org" value:

This automation will now trigger whenever a new record gets created in the "Social Media Posting Tracker" table, and its action will be to create one record per "Depts / Organizations" record that's linked to the triggering record:

[sg]

This works perfectly, thank you so much!!  🎉

Sydney

[TheTimeSavingCo]

Glad I could help!

[tdstarkey]

Okay, so I'm doing this in my own table, and I can only get one new record out of it. In the example above, I would only have 6/25/2023 and City Dept 1; the rest don't show up. Any thoughts?

[jillmonterey]

Just clicking in a year later to say I'm so glad this question was asked and answered! I couldn't figure out how to keep my loop from simply creating a row for each multi-select option with all four options in each cell instead of breaking them out, one per record. It's the Value>ID that did it!

[jillmonterey]

Uh-oh, further along it's misbehaving. Users can enter change requests, then pick up to 4 Regions where the changes should apply. I've got a single row for each region, as I wanted, but I also have another row with all four regions. How to get rid of that extra row?