2

I would like to generate the unique customer number. I did the following but I wonder, how can i make it in a loop, so the loop checks until it find zero generated customer number in the DE:

%%[

Var @NcustNO, @checkcustid, @ucustid
SET @NcustNO = RequestParameter("custNO")
SET @low = 1000000 
SET @high = 9000000 
SET @number =1010001

/* generate the customer number and check against DE but want to have it in loop condition */

IF EMPTY(@NcustNO) THEN

    SET @FcustNO = Random(@low,@high)
    SET @checkcustid = Lookup("DE","custnumber","custnumber",@FcustNO)

    IF NOT EMPTY (@checkcustid) THEN

        SET @uniquecustid = Add(@FcustNO,@number)
        SET @FcustNO = @uniquecustid

    ENDIF

ENDIF

]%%

Modified one after Kelly's suggestion :

/* for create */

%%[ 
Var @customernumber, @ucustid
SET @customernumber = RequestParameter("custNO")

IF EMPTY(@customernumber) THEN

    /*genrate unique 7 digit customer number and look it up against a DE for uniqueness :: a loop and a check - with SSJS*/

]%%

    <script runat="server" language="javascript">

    Platform.Load("Core","1");

    var number = 1010001
    var low = 1000000
    var high = 9000000
    var filter 
    var layouts  

    //While Loop to check for customer

    while(layouts !=0) {

     var randnumber = Math.random() * (low - high) + low;
     var uniquecustid = randnumber + number ;
     filter = {Property:"Custid",SimpleOperator:"equals",Value:uniquecustid};
     layouts = DataExtension.Init("DE").Rows.Retrieve(filter);

    }

    Variable.SetValue("@uniquecustid",Stringify(uniquecustid));

    </script>

%%[ 

SET @ucustid = @uniquecustid 

ENDIF
]%%

Status of above code : ERROR

Jint.JintException:  Exception of type 'Jint.Native.JsException' was thrown.
Line: 955 Char: 20
throw "There was an error retrieving the rows."
Source:  Jint

DE screen shot : enter image description here

  • Any reason why you would not use GUID? – Kelly J Andrews May 26 '14 at 13:05
  • the requirement is to have 7 digit customer number...i wonder will it be done with GUID? How? – Jack85 May 26 '14 at 18:07
  • I did not know about the requirement. Essentially, you are looking for a while loop, correct? – Kelly J Andrews May 27 '14 at 11:13
  • yes..any loop...that can happily take care of my need in the code.. – Jack85 May 27 '14 at 11:20
  • Another question - is this being done on a landing page, or an email - or something else? What is the context of this being used? – Kelly J Andrews May 27 '14 at 11:23
1

Since you are on a landing page, you would probably be ok using Server Side Javascript in this case.

<script runat=server>
    Platform.Load("Core","1");
    var custnumber = DataExtension.Init("custnumber");
    //Insert While Loop to check for customer
</script>

I created a very contrived example of how this might work for your use case. I created a data extension with just the primary key, and a landing page that would perform a lookup if there isn't a query string parameter of custId available. I tightened your range to guarantee it would require multiple iterations before finding a new ID available.

<h2>Results from Data Extension</h2>
<script runat=server language=javascript>
    Platform.Load("Core","1");

    var custId = "";

    if (Request.GetQueryStringParameter("custId")){
        custId = Request.GetQueryStringParameter("custId");
        Write("Customer ID requested: " + custId + "<br>");
    } else {
        Write("No Customer ID requested - creating new customer. <br>");
            var low = 1,
                number =1010001,
                high = 22,
                randnumber,
                newId;
        while(layouts.length != 0){

            randnumber = Math.floor(Math.random() * (high - low) + low);
            newId = randnumber + number;
            Write("Looking up Customer Id: " + newId + "<br>");

            layouts = DataExtension.Init("ssjs_test").Rows.Retrieve({Property:"custId",SimpleOperator:"equals",Value:newId});
        }

        Write(newId);
    }

</script>

Here is the working sample

After the while loop, you would then need to create the new customer in the data extension. This should get you moving in the right direction, and simplifies the code you have above.

Here is some additional info on data extension SSJS functions.

You can pass data between AMPscript and SSJS using methods found here.

| improve this answer | |
  • 1
    They do pass back and forth yes. There is a for loop in AMPscript, but it's not the best method to get you where you want to be. You might be able to do it with AMPscript only, but it probably wouldn't be efficient. Updated the answer with additional link. – Kelly J Andrews May 27 '14 at 12:31
  • 1
    This should work, but I've not been able to test it out personally. I would say give it a try and see how it goes. If it's not working, we can address that. – Kelly J Andrews May 27 '14 at 16:54
  • 1
    Yes inplace of DE i used the Name/External Key(both are same) of the data extension in my code. – Jack85 May 28 '14 at 11:58
  • 1
    @Jack85 - I assume there is data available? I'm certain this method works, but something else is causing your problem. Hard to figure out what it is specifically. – Kelly J Andrews May 28 '14 at 19:22
  • 1
    Finally!! All went fine ... Special Thx to Kelly!! – Jack85 May 29 '14 at 20:23

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.