How to look up a NetSuite record by field with a URL modified by an expression?

[jaredfraley]

Hi Celigo Community,

I am trying to look up a record by field in NetSuite using a URL modified by an expression to remove the "www.".

The reasoning is that some URLs had "www." and some did not, even for the same website's data, so I removed "www." from all URLs before importing the data for the records.

With that said, I am running into an error with parsing the lookup expression.

Example:

"selector": {
        "domain": "https://www.example.com",
        "icon_url": "https://www.example.com/img/favicon.png"
      }

Expression:

{{replace selector.domain "www." ""}}

Lookup:

["custrecord_comp_rec_url","is","{{replace selector.domain \"www.\" \"\"}}"]

Error:

  Message: 
    Unable to process lookup search key for field "custrecord_compsku_competitor": Failed to generate lookup expression from template: ["custrecord_comp_rec_url","is","{{replace selector.domain \"www.\" \"\"}}"]. Details: Parse error on line 1:
  ...ace selector.domain \"www.\" \"\"}}"]
  -----------------------^
  Expecting 'CLOSE_RAW_BLOCK', 'CLOSE', 'CLOSE_UNESCAPED', 'OPEN_SEXPR', 'CLOSE_SEXPR', 'ID', 'OPEN_BLOCK_PARAMS', 'STRING', 'NUMBER', 'BOOLEAN', 'UNDEFINED', 'NULL', 'DATA', 'SEP', got ...

  Code: handlebars_template_parse_error

[natebriant]

Hi Jared,

I was able to replicate that same error when trying to use a handlebar expression in the "How can we find existing records?" input of a lookup or input step of a flow.

There are two options to resolve this.

Option 1. This looks like a bug with SuiteScript 2.X in which case you can rebuild your import step using the NetSuite SuiteApp SuiteScript 1. When you create a new import step under Advanced options (see below screenshot) you would select SuiteApp Suite Script 1.0 and then you could use the same handlebar expression. This would require you to remap the step as well as the API version can not be changed after the step has been modified.

Option 2. You can transform the data using a JavaScript hook before the data gets to the import or lookup and continue to use SuiteApp SuiteScript 2.X. By performing the data transformation (removing the "www") before you get to the step in which you are doing the lookup action. I made an example of doing this via the transformation feature with a simple script on the flow step before I needed to do the lookup. I have also included steps and screenshots below.

Step 1. Click on the plus icon on the endpoint bubble step before the step on which the lookup needs to occur.

Step 2. Click on the data transformation step to define a new transformation in the flow. This will open up the transformation page.

Step 3. Switch the view to JavaScript to allow you to select you script that you want to run.

Step 4. Create a new script by hitting the plus button. This will open create a script menu.

Step 5. Give your script a name and insert the function stub for transformation which gives details about what the script will do. If you want to know more about the different stubs and their use cases this help center article will have further links to the different types of stubs.

Step 6. Input your script with a function that will transform the needed data. I created the following script based on the example data you provided but it may require modification on your side. It just removes the www from the domain record.

function transform (options) {

//modifys the record for the domain and removes the www value

options.record.selector.domain = options.record.selector.domain.replace("www","")

//return the record

return options.record
}

Step 7. Save and close the script editor

Step 8. Back in the define transformation page, search for your newly created script and make sure to call the function which in the case of the example I made it is just called transform. You can preview how the script handles the data and from the screenshot below with step 8 you can see that the script has removed "www" from the record domain. If you want to transform the icon_url you would need to modify the script.

Step 9: Now with a script modifying the data, there is no need to do a handlebar expression on the lookup criteria step. I can just search by the field mapping. I am looking up against a custom field on a customer record in this example.

Screenshots for the JavaScript steps

[jaredfraley]

@natebriant,

I could not get the script to work as the data format is actually:

{
   "record": {
     "array": [
     {
       "selector": {
         "domain": "https://www.example.com"
         "icon": "https://www.example.com/icon.png"
       },
      },
    ],
  }
}

That being said, I got it working by changing to SuiteScript 1.0, thanks!

I am curious as to how it would look with the script though. What would be the correct way to achieve the same results with the format provided using the script?

[natebriant]

Jared,

Glad you were able to resolve the issue by switching to SuiteScript 1.0!

The example script I wrote is very static and dependent on the structure of your data. If the field you are looking to modify is in a different location in the data structure then you would need to modify the field reference in the body of the script.

In the original script I referenced the domain field as: options.record.selector.domain. This returns the domain field because in the original example its location in the data structure is the options argument > record object > selector object > domain field.

In the new data format you provided, the script would need to go down another level into the array. So the script reference would look be: options.record.array[0].selector.domain. This script would then go down the data structure like this: options argument > record object > the first record in the array (arrays start at zero) > selector object > domain field.

So the new script would look like this:

/*
* transformFunction stub:
*
* The name of the function can be changed to anything you like.
*
* The function will be passed one 'options' argument that has the following fields:
*   'record' - object {} or array [] depending on the data source.
*   'settings' - all custom settings in scope for the transform currently running.
* The function needs to return the transformed record.
* Throwing an exception will return an error for the record.
*/
function transform (options) {
//new reference to data structure with an array
 options.record.array[0].selector.domain = options.record.array[0].selector.domain.replace("www","")
  return options.record
  
}

This new script is still pretty static and dependent on the selector object always being in the first position of the array. But the point is really just to demonstrate how to reference different objects and fields in the data structure and then transform them.

Hope this makes more sense!