1

I'm building out a very dynamic email that should pull values from an array(aka a related object in Salesforce) and set each value as a variable.

When I do a send preview against a data extension that has some test data in it I get the error in the title. any ideas? Suggestions on an easier way to do this even?

Here is the very long code I have to do this so far:

var @bookingSegment
set @bsObject = RetrieveSalesforceObjects("Booking_Segment__c", "City_Arrival__c, City_Departure__c, Country_Arrival__c, Country_Departure__c, End_Airport_Code__c, EndDateLocal__c, Hotel_Name__c, Start_Airport_Code__c, Start_Date_Local__c, State_Arrival__c, State_Departure__c, Type__c, StartCity__c, UltimateChoiceFlag__c", "Transactional_Email__c", "=", RecordId)
    IF rowCount(@bsObject) > 0 then
        FOR @i = 1 to rowCount(@bsObject) do
           set @City_Arrival =     trim(field(row(@bsObject, @i), "City_Arrival__c"))
              if empty (@City_Arrival) then
               set @City_Arrival = 'null'
              endif
           set @StartCity =     trim(field(row(@bsObject, @i), "StartCity__c"))
              if Length(@StartCity) < 4 then
               set @StartCity = 'null'
              endif
           set @City_Departure =  trim(field(row(@bsObject, @i), "City_Departure__c"))
              if empty (@City_Departure) then
               set @City_Departure = 'null'
              endif
           set @Country_Departure =      trim(field(row(@bsObject, @i), "Country_Departure__c"))
              if empty (@Country_Departure) then
               set @Country_Departure = 'null'
              endif
           set @End_Airport_Code =      trim(field(row(@bsObject, @i), "End_Airport_Code__c"))
              if empty (@End_Airport_Code) then
               set @End_Airport_Code = 'null'
              endif
           set @EndDateLocal =      trim(field(row(@bsObject, @i), "EndDateLocal__c"))
              if empty (@EndDateLocal) then
               set @EndDateLocal = 'null'
              endif
           set @Hotel_Name =      trim(field(row(@bsObject, @i), "Hotel_Name__c"))
              if empty (@Hotel_Name) then
               set @Hotel_Name ='null'
              endif
           set @Start_Airport_Code =      trim(field(row(@bsObject, @i), "Start_Airport_Code__c"))
              if empty (@Start_Airport_Code) then
               set @Start_Airport_Code ='null'
              endif
           set @Start_Date_Local =      trim(field(row(@bsObject, @i), "Start_Date_Local__c"))
              if not empty (@Start_Date_Local) then
               set @Month = FormatDate(@Start_Date_Local,"MMMM")
              endif
              if empty (@Start_Date_Local) then
               set @Start_Date_Local = 'null'
               set @Month = 'null'
              endif

           set @State_Arrival =      trim(field(row(@bsObject, @i), "State_Arrival__c"))
              if empty (@State_Arrival) then
               set @State_Arrival ='null'
              endif
           set @State_Departure =      trim(field(row(@bsObject, @i), "State_Departure__c"))
              if empty (@State_Departure) then
               set @State_Departure = 'null'
              endif
           set @Type =      trim(field(row(@bsObject, @i), "Type__c"))
              if empty (@Type) then
                set @Type = 'null'
              endif
              if @Type == 'Air' then
                set @Type = 'flight'
              endif
              if @Type == 'Hotel' then
                set @Type = 'hotel'
              endif
           set @UltimateChoiceFlag =      trim(field(row(@bsObject, @i), "UltimateChoiceFlag__c"))
              if empty (@UltimateChoiceFlag) then
               set @UltimateChoiceFlag ='null'
              endif

           SET @bookingSegment = Concat(@bookingSegment, "|", @City_Arrival, "|", @StartCity, "|",@City_Departure, "|",@Country_Departure, "|",@End_Airport_Code, "|",@EndDateLocal, "|",@Hotel_Name, "|",@Start_Airport_Code, "|",@Start_Date_Local, "|",@Month, "|",@State_Arrival, "|",@State_Departure, "|",@Type, "|",@UltimateChoiceFlag)

NEXT @i

endif
 Set @bookingSegmentRow = BuildRowsetFromString(@bookingSegment,'|')
 set @NumOfRows = RowCount(@bookingSegmentRow)
 IF @NumOfRows < 16 THEN

    set @Row = row(@bookingSegmentRow, 2)
      set @City_Arrival_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 3)
      set @StartCity_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 4)
      set @City_Departure_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 5)
       @Country_Departure_1  = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 6)
      set @End_Airport_Code_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 7)
      set @EndDateLocal_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 8)
       set @Hotel_Name_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 9)
       set @Start_Airport_Code_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 10)   
       set @Start_Date_Local_1 = field(@Row, 1))
    set @Row = row(@bookingSegmentRow, 11)   
       set @Month_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 12)   
       set @State_Arrival_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 13)    
       set @State_Departure_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 14)   
       set @Type_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 15)   
       set @UltimateChoiceFlag_1 = field(@Row, 1)
ENDIF
IF @NumOfRows < 30 THEN
 set @Row = row(@bookingSegmentRow, 2)
      set @City_Arrival_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 3)
      set @StartCity_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 4)
      set @City_Departure_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 5)
       @Country_Departure_1  = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 6)
      set @End_Airport_Code_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 7)
      set @EndDateLocal_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 8)
       set @Hotel_Name_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 9)
       set @Start_Airport_Code_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 10)   
       set @Start_Date_Local_1 = field(@Row, 1))
    set @Row = row(@bookingSegmentRow, 11)   
       set @Month_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 12)   
       set @State_Arrival_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 13)    
       set @State_Departure_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 14)   
       set @Type_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 15)   
       set @UltimateChoiceFlag_1 = field(@Row, 1)

    set @Row = row(@bookingSegmentRow, 16)
      set @City_Arrival_2 = field(@Row, 1)
          set @Row = row(@bookingSegmentRow, 17)
      set @StartCity_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 18)
      set @City_Departure_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 19)
       @Country_Departure_2  = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 20)
      set @End_Airport_Code_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 21)
      set @EndDateLocal_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 22)
       set @Hotel_Name_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 23)
       set @Start_Airport_Code_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 24)   
       set @Start_Date_Local_2 = field(@Row, 1))
    set @Row = row(@bookingSegmentRow, 25)   
       set @Month_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 26)   
       set @State_Arrival_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 27)    
       set @State_Departure_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 28)   
       set @Type_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 29)   
       set @UltimateChoiceFlag_2 = field(@Row, 1)
ENDIF

1 Answer 1

2

I think the main issue was down at the bottom two If statements. In the first one, you are running if it is less than 16 - which by your code I am assuming your data that you will always have at least 15 rows - this is fine, BUT your next IF statement is less than 30, which will ALSO run if less than 16 - meaning if you only have 15, it will try to run BOTH IF statements.

Easy solution, either make it an IF ELSEIF ENDIF or add in AND @NumofRows > 16 or BOTH:

var @bookingSegment
set @bsObject = RetrieveSalesforceObjects("Booking_Segment__c", "City_Arrival__c, City_Departure__c, Country_Arrival__c, Country_Departure__c, End_Airport_Code__c, EndDateLocal__c, Hotel_Name__c, Start_Airport_Code__c, Start_Date_Local__c, State_Arrival__c, State_Departure__c, Type__c, StartCity__c, UltimateChoiceFlag__c", "Transactional_Email__c", "=", RecordId)
    IF rowCount(@bsObject) > 0 then
        FOR @i = 1 to rowCount(@bsObject) do
           set @City_Arrival =     trim(field(row(@bsObject, @i), "City_Arrival__c"))
              if empty (@City_Arrival) then
               set @City_Arrival = 'null'
              endif
           set @StartCity =     trim(field(row(@bsObject, @i), "StartCity__c"))
              if Length(@StartCity) < 4 then
               set @StartCity = 'null'
              endif
           set @City_Departure =  trim(field(row(@bsObject, @i), "City_Departure__c"))
              if empty (@City_Departure) then
               set @City_Departure = 'null'
              endif
           set @Country_Departure =      trim(field(row(@bsObject, @i), "Country_Departure__c"))
              if empty (@Country_Departure) then
               set @Country_Departure = 'null'
              endif
           set @End_Airport_Code =      trim(field(row(@bsObject, @i), "End_Airport_Code__c"))
              if empty (@End_Airport_Code) then
               set @End_Airport_Code = 'null'
              endif
           set @EndDateLocal =      trim(field(row(@bsObject, @i), "EndDateLocal__c"))
              if empty (@EndDateLocal) then
               set @EndDateLocal = 'null'
              endif
           set @Hotel_Name =      trim(field(row(@bsObject, @i), "Hotel_Name__c"))
              if empty (@Hotel_Name) then
               set @Hotel_Name ='null'
              endif
           set @Start_Airport_Code =      trim(field(row(@bsObject, @i), "Start_Airport_Code__c"))
              if empty (@Start_Airport_Code) then
               set @Start_Airport_Code ='null'
              endif
           set @Start_Date_Local =      trim(field(row(@bsObject, @i), "Start_Date_Local__c"))
              if not empty (@Start_Date_Local) then
               set @Month = FormatDate(@Start_Date_Local,"MMMM")
              endif
              if empty (@Start_Date_Local) then
               set @Start_Date_Local = 'null'
               set @Month = 'null'
              endif

           set @State_Arrival =      trim(field(row(@bsObject, @i), "State_Arrival__c"))
              if empty (@State_Arrival) then
               set @State_Arrival ='null'
              endif
           set @State_Departure =      trim(field(row(@bsObject, @i), "State_Departure__c"))
              if empty (@State_Departure) then
               set @State_Departure = 'null'
              endif
           set @Type =      trim(field(row(@bsObject, @i), "Type__c"))
              if empty (@Type) then
                set @Type = 'null'
              endif
              if @Type == 'Air' then
                set @Type = 'flight'
              endif
              if @Type == 'Hotel' then
                set @Type = 'hotel'
              endif
           set @UltimateChoiceFlag =      trim(field(row(@bsObject, @i), "UltimateChoiceFlag__c"))
              if empty (@UltimateChoiceFlag) then
               set @UltimateChoiceFlag ='null'
              endif

           SET @bookingSegment = Concat(@bookingSegment, "|", @City_Arrival, "|", @StartCity, "|",@City_Departure, "|",@Country_Departure, "|",@End_Airport_Code, "|",@EndDateLocal, "|",@Hotel_Name, "|",@Start_Airport_Code, "|",@Start_Date_Local, "|",@Month, "|",@State_Arrival, "|",@State_Departure, "|",@Type, "|",@UltimateChoiceFlag)

NEXT @i

endif
 Set @bookingSegmentRow = BuildRowsetFromString(@bookingSegment,'|')
 set @NumOfRows = RowCount(@bookingSegmentRow)

IF @NumOfRows < 16 THEN

    set @Row = row(@bookingSegmentRow, 2)
      set @City_Arrival_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 3)
      set @StartCity_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 4)
      set @City_Departure_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 5)
       @Country_Departure_1  = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 6)
      set @End_Airport_Code_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 7)
      set @EndDateLocal_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 8)
       set @Hotel_Name_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 9)
       set @Start_Airport_Code_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 10)   
       set @Start_Date_Local_1 = field(@Row, 1))
    set @Row = row(@bookingSegmentRow, 11)   
       set @Month_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 12)   
       set @State_Arrival_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 13)    
       set @State_Departure_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 14)   
       set @Type_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 15)   
       set @UltimateChoiceFlag_1 = field(@Row, 1)

ELSEIF @NumOfRows < 30 AND @NumOfRows > 16 THEN

 set @Row = row(@bookingSegmentRow, 2)
      set @City_Arrival_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 3)
      set @StartCity_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 4)
      set @City_Departure_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 5)
       @Country_Departure_1  = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 6)
      set @End_Airport_Code_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 7)
      set @EndDateLocal_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 8)
       set @Hotel_Name_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 9)
       set @Start_Airport_Code_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 10)   
       set @Start_Date_Local_1 = field(@Row, 1))
    set @Row = row(@bookingSegmentRow, 11)   
       set @Month_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 12)   
       set @State_Arrival_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 13)    
       set @State_Departure_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 14)   
       set @Type_1 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 15)   
       set @UltimateChoiceFlag_1 = field(@Row, 1)

    set @Row = row(@bookingSegmentRow, 16)
      set @City_Arrival_2 = field(@Row, 1)
          set @Row = row(@bookingSegmentRow, 17)
      set @StartCity_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 18)
      set @City_Departure_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 19)
       @Country_Departure_2  = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 20)
      set @End_Airport_Code_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 21)
      set @EndDateLocal_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 22)
       set @Hotel_Name_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 23)
       set @Start_Airport_Code_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 24)   
       set @Start_Date_Local_2 = field(@Row, 1))
    set @Row = row(@bookingSegmentRow, 25)   
       set @Month_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 26)   
       set @State_Arrival_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 27)    
       set @State_Departure_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 28)   
       set @Type_2 = field(@Row, 1)
    set @Row = row(@bookingSegmentRow, 29)   
       set @UltimateChoiceFlag_2 = field(@Row, 1)
ENDIF
1
  • Oh my, how did I miss that!! of COURSE!! YES!! Thank you SOOO much. I've been trying different random things for hours and totally over looked this every time. I truly appreciate your help!
    – JulieHurtz
    Sep 29, 2017 at 19:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .