0

I am building a SSJS data extension row retrieve filter to pull out the values a person scored on a survey for certain areas. This is all taking place on a landing page in the SFMC microsites feature (not cloud pages).

For example, I want to find out how many 1,2,3,4 or 5's a person scored for 'Resourcefullness'. The rep value is correctly defined and the Values variables are passed correctly.

There must be something with my filter or the Rows.Retrieve call as it always outputs 0.

Please see below sample of my script:

<script runat="server">
Platform.Load("core","1");

var CollectDE = DataExtension.Init("BiAnnualSurvey_Collection");

var filterpartname = {
                LeftOperand:{
                    Property:"Service1",
                    SimpleOperator:"equals",
                    Value: Variable.GetValue("@Rep")
                },
                LogicalOperator:"OR",
                RightOperand:{ 
                    LeftOperand:{
                    Property:"Service2",
                    SimpleOperator:"equals",
                    Value: Variable.GetValue("@Rep")
                    },
                    LogicalOperator:"OR",
                    RightOperand:{
                    Property:"Service3",
                    SimpleOperator:"equals",
                    Value: Variable.GetValue("@Rep")
                    }
                }
};

var filterpartvalue = {
                   LeftOperand:{
                    Property:"Service1" + property,
                    SimpleOperator:"equals",
                    Value:value
                },
                LogicalOperator:"OR",
                RightOperand:{ 
                    LeftOperand:{
                    Property:"Service2" + property,
                    SimpleOperator:"equals",
                    Value:value
                    },
                    LogicalOperator:"OR",
                    RightOperand:{
                    Property:"Service3" + property,
                    SimpleOperator:"equals",
                    Value:value
                    }
                }
                 }
var filter = {
                LeftOperand: filterpartname,
                LogicalOperator:"AND",
                RightOperand: filterpartvalue
};
//Resourcefulness Filter
var property = "Resourcefulness"

var value = 1
var r1 = CollectDE.Rows.Retrieve(filter);
var val1 = value;
var resourcefulness1 = r1.length;

var value = 2
var r2 = CollectDE.Rows.Retrieve(filter);
var val2 = value;
var resourcefulness2 = r2.length;

var value = 3
var r3 = CollectDE.Rows.Retrieve(filter);
var val3 = value;
var resourcefulness3 = r3.length;

var value = 4
var r4 = CollectDE.Rows.Retrieve(filter);
var val4 = value;
var resourcefulness4 = r4.length;

var value = 5
var r5 = CollectDE.Rows.Retrieve(filter);
var val5 = value;
var resourcefulness5 = r5.length;


</script>          
          Rep: <ctrl:var name=@Rep /><br>
          Resourceful<br>
          <ctrl:var name=val1 /> - <ctrl:var name=resourcefulness1 /><br>
          <ctrl:var name=val2 /> - <ctrl:var name=resourcefulness2 /><br>
          <ctrl:var name=val3 /> - <ctrl:var name=resourcefulness3 /><br>
          <ctrl:var name=val4 /> - <ctrl:var name=resourcefulness4 /><br>
          <ctrl:var name=val5 /> - <ctrl:var name=resourcefulness5 /><br>
          <br><br>
1

Your filter isn't getting re-evaluated when you set the values before each retrieve.

I'd make function to return the filter based on the Rep and Value. Something like this:

<script runat="server">
Platform.Load("core","1");

var CollectDE = DataExtension.Init("BiAnnualSurvey_Collection");
var Rep = Variable.GetValue("@Rep");

function setFilter(rep, value) {

    var filterpartname = {
        LeftOperand:{
            Property:"Service1",
            SimpleOperator:"equals",
            Value: rep
        },
        LogicalOperator:"OR",
        RightOperand:{ 
            LeftOperand:{
            Property:"Service2",
            SimpleOperator:"equals",
            Value: rep
            },
            LogicalOperator:"OR",
            RightOperand:{
            Property:"Service3",
            SimpleOperator:"equals",
            Value: rep
            }
        }
    };

    var filterpartvalue = {
           LeftOperand:{
            Property:"Service1" + property,
            SimpleOperator:"equals",
            Value: value
        },
        LogicalOperator:"OR",
        RightOperand:{ 
            LeftOperand:{
            Property:"Service2" + property,
            SimpleOperator:"equals",
            Value: value
            },
            LogicalOperator:"OR",
            RightOperand:{
            Property:"Service3" + property,
            SimpleOperator:"equals",
            Value: value
            }
        }
         }
    var filter = {
        LeftOperand: filterpartname,
        LogicalOperator:"AND",
        RightOperand: filterpartvalue
    };

return filter;

}

//Resourcefulness Filter
var property = "Resourcefulness"
var Value = 0

Value = 1
var r1 = CollectDE.Rows.Retrieve(setFilter(Rep,Value));
var val1 = Value;
var resourcefulness1 = r1.length;

Value = 2
var r2 = CollectDE.Rows.Retrieve(setFilter(Rep,Value));
var val2 = Value;
var resourcefulness2 = r2.length;

Value = 3
var r3 = CollectDE.Rows.Retrieve(setFilter(Rep,Value));
var val3 = Value;
var resourcefulness3 = r3.length;

Value = 4
var r4 = CollectDE.Rows.Retrieve(setFilter(Rep,Value));
var val4 = Value;
var resourcefulness4 = r4.length;

Value = 5
var r5 = CollectDE.Rows.Retrieve(setFilter(Rep,Value));
var val5 = Value;
var resourcefulness5 = r5.length;


</script>

Rep: <ctrl:var name=@Rep /><br>
Resourceful<br>
<ctrl:var name=val1 /> - <ctrl:var name=resourcefulness1 /><br>
<ctrl:var name=val2 /> - <ctrl:var name=resourcefulness2 /><br>
<ctrl:var name=val3 /> - <ctrl:var name=resourcefulness3 /><br>
<ctrl:var name=val4 /> - <ctrl:var name=resourcefulness4 /><br>
<ctrl:var name=val5 /> - <ctrl:var name=resourcefulness5 /><br>
<br><br>
  • worked like a charm! Can't believe I missed that...THANKS!! – Gortonington Jul 21 '16 at 18:11

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.