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
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
| improve this answer | |
  • 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 '17 at 19:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.