Form Help

ConnorForm

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

ConnorForm

Update The formula I have so far:

=IF([Market Status]@row = "Needs Market Assigned", "https://app.smartsheet.com/b/form/ee02bcc6adf74b819839187a4e17d2b8?" + "&Account%20=" + SUBSTITUTE(SUBSTITUTE(Account@row, "@", "%40"), " ", "%20") + "&Omni%20Planner=" + SUBSTITUTE(SUBSTITUTE([Omni Planner]@row, "@", "%40"), " ", "%20") + "&Dealer%20Name=" + SUBSTITUTE(SUBSTITUTE([Dealer Name]@row, " ", "%20"), "_", "%5F") + "&Media%20Type=" + SUBSTITUTE(SUBSTITUTE([Media Type]@row, " ", "%20"), "-", "%2D"))

Is pulling values selected in Dealer Name and Media Type from sheet 1 to sheet 2 but I still need Omni, Account, C/P/E and Campaign UID

Reminder Form 1 fills out BT- Campaign Taxonomy. Sheet 2 is the form snippet below that takes values filled out in form 1 and places them under campaign details in the form 2 below

ConnorForm

Another update!!!!! lol I got more fields to populate in form 2 based off form 1 selections. Just missing Omni Planner and Account pulling through

Not sure how I can tweak this formula to get both of those to pull through

=IF(Status@row = "Needs Market Assigned", "https://app.smartsheet.com/b/form/ee02bcc6adf74b819839187a4e17d2b8?" + "&Account%20=" + SUBSTITUTE(SUBSTITUTE(Account@row, " ", "%20"), " ", "%20") + "&Omni%20Planner=" + SUBSTITUTE(SUBSTITUTE([Omni Planner]@row, " ", "%20"), "_", "%5F") + "&Dealer%20Name=" + SUBSTITUTE(SUBSTITUTE([Dealer Name]@row, " ", "%20"), "_", "%5F") + "&Media%20Type=" + SUBSTITUTE(SUBSTITUTE([Media Type]@row, " ", "%20"), "-", "%2D") + "&Campaign%20UID=" + SUBSTITUTE(SUBSTITUTE([Campaign UID]@row, " ", "%20"), "_", "%5F") + "&C/P/E=" + SUBSTITUTE(SUBSTITUTE([C/P/E]@row, " ", "%20"), "_", "%5F"))

Brian_Richardson

Drop the & from in front of Account and it should be ok.

The format for the URL is:

https://app.smartsheet.com/b/form/id?field=value&field=value&field=value

Also you don't need to double up on SUBSTITUTE for %20, you're doing it twice for Account.

Finally, I prefer + to %20 myself for the blanks, it works as well and is cleaner to read.

=IF(
 Status@row = "Needs Market Assigned", 
"https://app.smartsheet.com/b/form/ee02bcc6adf74b819839187a4e17d2b8?" 
+ "Account=" + SUBSTITUTE(Account@row, " ", "%20") 
+ "&Omni+Planner=" + SUBSTITUTE(SUBSTITUTE([Omni Planner]@row, " ", "%20"), "_", "%5F") 
+ "&Dealer+Name=" + SUBSTITUTE(SUBSTITUTE([Dealer Name]@row, " ", "%20"), "_", "%5F") 
+ "&Media+Type=" + SUBSTITUTE(SUBSTITUTE([Media Type]@row, " ", "%20"), "-", "%2D") 
+ "&Campaign+UID=" + SUBSTITUTE(SUBSTITUTE([Campaign UID]@row, " ", "%20"), "_", "%5F") 
+ "&C/P/E=" + SUBSTITUTE(SUBSTITUTE([C/P/E]@row, " ", "%20"), "_", "%5F")
)

ConnorForm

Thanks @Brian_Richardson That is working except for the Omni Planner and Account Columns, they are still coming in as blank. In the Smartsheet, these columns are Contact List Columns. Could that be why they are not coming through? Dealer Name and C/P/E are Text/Number Columns / Media Type is a dropdown column and Campaign UID is a Text/Number column as a formula.

Brian_Richardson

Are Omni Planner and Account set to Restricted?