Hello all, I am currently setting up a new Smartsheet process that involves form entries to populate 2 sheets. The first form populates sheet 1 and acts as a campaign taxonomy for placements. Populates about 5 Columns. For the second sheet, this is more for creative entries, so on this sheet the 1 placement made on Sheet 1 can be referenced as many as ten times based on how many markets the campaign is running in. So 1 to many relationship for the campaigns. The form in sheet 2 is to build off the selections made from the first form. So take those 5 columns filled previously and add on with creative information. I almost making a parent/child relationship between Sheet 1 (Placements for Campaigns) and Sheet 2 Creatives under those placements.
What I need: User of this Smartsheet will fill out form 1 as such. Once submitted, a status of "Needs Market Assigned" will prepopulate that would prompt the form 2 from Sheet 2 to appear in the highlighted column in Sheet 1. When the user clicks on this link, form 2 will open but will have 12 additional fields to fill out and will have Omni Planner - Campaign UID filled out from the first form entry
Form 1 & Sheet 1
Form 2 link would populate in Creative Form column in sheet 1 and would be to fill out these additional fields
And have Campaign Details pre filled out from the selections from form 1 and sheet 1 at the bottom of form 2
I have an example from someone who used to work at my company, but the formula is quite long, has more components then my situation, and I have not too skilled with the substitution function. But it works essentially the same way. User fills out form 1 in Campaign Taxonomy and then when status is a certain value, a creative form is prompted to take the campaign details from form 1 and insert them in form 2 and add on creative information on 2nd creative sheet
When the user clicks on that link all of the campaign details in these columns are prepopulating in their respective fields so they do not need filled out again
Here is the formula adding these in - I only need the columns of Omni Planner, Account, Dealer Name, C/P/E, Media Type and Campaign UID to prepopulate, not as many fields that are being referenced in the example below
=IF(OR(Status@row = "Account - Needs Creative", Status@row = "Account - Revise"), "https://app.smartsheet.com/b/form/f4472790ae074dc5bba64d36a513f463?" + "&Fiscal%20Year=" + [Fiscal Year]@row + "&Client=" + SUBSTITUTE(SUBSTITUTE(Client@row, " ", "%20"), "&", "%26") + "&Campaign%20Start=" + [Campaign Start]@row + "&Industry=" + SUBSTITUTE(SUBSTITUTE(Industry@row, " ", "%20"), "&", "%26") + "&Agency=" + Agency@row + "&Brand=" + SUBSTITUTE(Brand@row, " ", "%20") + "&Source=" + SUBSTITUTE([Platform / Vendor]@row, " ", "%20") + "&Medium=" + SUBSTITUTE([Medium_Conv]@row, " ", "%20") + "&Campaign%20End=" + [Campaign End]@row + "&Channel=" + SUBSTITUTE(Channel@row, " ", "%20") + "&Campaign%20UID=" + [Campaign UID]@row + "&Target%20Market=" + SUBSTITUTE(SUBSTITUTE([Target_Market_Conv]@row, " ", "%20"), "|", "%7C") + "&Data%20Source=" + SUBSTITUTE(SUBSTITUTE([Data_Source_Conv]@row, " ", "%20"), "|", "%7C") + "&Targeting%20Type=" + SUBSTITUTE(SUBSTITUTE([Targeting_Type_Conv]@row, " ", "%20"), "|", "%7C") + "&Campaign%20Name=" + SUBSTITUTE(SUBSTITUTE([Campaign Name]@row, " ", "%20"), "_", "%5F") + "&Media%20KPI=" + SUBSTITUTE(SUBSTITUTE([Media KPI]@row, " ", "%20"), "-", "%2D") + "&Objective=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Business Objective]@row, "(", "%28"), ")", "%29"), " ", "%20") + "&Retailer=" + SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Retailer@row, " ", "%20"), "&", "%26"), ",", "%2C"), ".", "%2E"), "-", "%2D") + "&Platform%20/%20Vendor=" + SUBSTITUTE(SUBSTITUTE([Platform / Vendor]@row, " ", "%20"), "#", "%23") + "&UID=" + UID@row + "&Account%20Person=" + SUBSTITUTE(SUBSTITUTE([Account Person]@row, "@", "%40"), " ", "%20") + "&Omni%20Planner=" + SUBSTITUTE(SUBSTITUTE([Omni Planner]@row, "@", "%40"), " ", "%20") + "&Creative%20Start=" + SUBSTITUTE(SUBSTITUTE([Campaign Start]@row, "@", "%40"), " ", "%20"))
