Adding New Record Based on Previous Related Record

Joe_Ambrose · ‎Jan 12, 2019

Good Day,

I’m trying to manage a number of marketing campaigns at the contact level within AirTable. I’m unclear if automation can be applied to make the process efficient.

My key use case in the [Activity] table is to manage a multi-step campaign over a series of days, where the [Due Date] for each step is calculated off the date of the [Completed Date] of previous steps for that [Contact].

My ask in the post is to determine if automation in AirTable can create a new record in the [Activity] table for a [Contact], on a [Campaign] for the next [Step] and calculate the [Due Date] based on the previous steps [Completed Date] plus the [Skip Days].

It seems like I would use an ifLook-up of a record for a campaign, active status, step, contact and competed date and then create a new record for contact, campaign, step, and calculate the due date based on the previous record. Or can automation be applied to the duplication of a completed record that could make updates?

I would appreciate any feedback on the feasibility of doing this within AirTable or other recommendations.

Thank you.

AlliAlosa · ‎Jan 13, 2019

Hi there! I’m pretty sure what you’re asking for can be done - but could you provide a bit more info about which fields are on which tables?

For example, is the [Completed Date] a field on the [Activity] table or the [Campaign] table?

Joe_Ambrose · ‎Jan 14, 2019

Thank you. I’m interested to hear your thoughts.

I have outline the fields mentioned by indicating the table they come from and the type of field.
[Activity] table fields include
{Due Date} is currently a date field
{Completed Date} date field
{Action Taken} single select field
{Stop Sequence} is check box
{Contact}, linked from [Contact] table
{Campaign} linked from [Campaign] table
{Step} linked from [Campaign] table
{Skip Days} linked from [Campaign] table

[Campaign] table fields include
{Campaign} is a concatenate formula field
{Step} numeric field
{Skip Days} numeric field

[Contact] table fields include
{Contact} is a concatenate formula field

What else would be helpful?

AlliAlosa · ‎Jan 17, 2019

Hi there Joe - I apologize as I think I misunderstood what you’re looking for. However I do think what you want to do can be accomplished using Zapier. What should trigger the creation of the new record? Should it happen upon a date being entered into the field {Completion Date}?

Joe_Ambrose · ‎Jan 17, 2019

The uses case is upon a record being updated, {complete}, then that record would be duplicated and updated for the next step in that campaign by looking up the step in the [campaign] table.

AlliAlosa · ‎Jan 17, 2019

I would start by creating a view that is filtered to only show the records that have been marked as {complete}. You’re then going to want to create a Zap that is triggered based on a new record being added to that view.

If you’ve never used Zapier, it’s super user friendly and free up to 5 “Zaps”. The article below will be extremely helpful in walking you through how to do this. I am more than willing to help if you’re comfortable sharing your Base or a “dumbed down” version of it. It’s difficult to try and wrap my head around it without seeing it.