Querying NetSuite Records in Custom Form Builder Settings
In the Shopify - NetSuite IA we have a location mapping settings where
1. In NetSuite Location textbox input we get dropdown of all the NetSuite Locations we have
2. Same is with Shopify
I am using custom flows for Inventory flow and would like to have a custom setting which can mimic this. I know how to create the Key Value Pair forms via form builder, but I do not know how to get the dropdown which has all the records of certain record type from the NetSuite. In short, how can I create a custom form builder where the NetSuite side of text input will have a dropdown of all NetSuite Locations.
Thank you!
1
Comments
Hi Sujit Desai, yes this is possible and here is an example I put together for you. Note that you will need to change the connection id for each virtual export to your own Shopify and NetSuite connection ids. Also, for the NetSuite section, you will need to change the search id to your own NetSuite search id. As for the NetSuite saved search itself, my result included Internal ID that had a custom label of value and then Name with a custom label of label. Hopefully this helps!
{"fieldMap": {
"refreshable": {
"id": "refreshable",
"name": "refreshableName",
"type": "staticMap",
"label": "label this",
"keyName": "extract",
"keyLabel": "extractLabel",
"valueName": "generate",
"valueLabel": "generateLabel",
"keyResource": {
"virtual": {
"name": "Lookup",
"_connectionId": "626168f90b6ef816b50c82d7",
"apiIdentifier": "ea7da9f678",
"asynchronous": true,
"oneToMany": false,
"sandbox": false,
"netsuite": {
"type": "restlet",
"skipGrouping": true,
"statsOnly": false,
"restlet": {
"recordType": "location",
"searchId": "2597",
"useSS2Restlets": false
},
"distributed": {
"useSS2Framework": false
}
},
"distributed": {
"disabled": false
},
"adaptorType": "NetSuiteExport"
}
},
"valueResource": {
"virtual": {
"name": "Get Locations",
"_connectionId": "62d95e8be26de11b5ee0dba6",
"apiIdentifier": "ede0fb0635",
"asynchronous": true,
"assistant": "shopify",
"oneToMany": false,
"sandbox": false,
"assistantMetadata": {
"resource": "locations",
"version": "v2",
"operation": "retrieves_alistoflocations"
},
"http": {
"relativeURI": "/locations.json",
"method": "GET",
"successMediaType": "json",
"errorMediaType": "json",
"formType": "assistant",
"paging": {
"method": "linkheader",
"lastPageStatusCode": 404,
"linkHeaderRelation": "next"
},
"response": {
"resourcePath": "locations"
}
},
"rest": {
"relativeURI": "/locations.json",
"method": "GET",
"resourcePath": "locations",
"pagingMethod": "linkheader",
"allowUndefinedResource": false,
"linkHeaderRelation": "next"
},
"transform": {
"type": "expression",
"expression": {
"rules": [
[
{
"extract": "id",
"generate": "value"
},
{
"extract": "name",
"generate": "label"
}
]
],
"version": "1"
},
"version": "1",
"rules": [
[
{
"extract": "id",
"generate": "value"
},
{
"extract": "name",
"generate": "label"
}
]
]
},
"adaptorType": "RESTExport"
}
}
}
}
}
Tyler Lamparter this is very helpful. Additionally, is there any documentation that guides how to use develop these kind of settings. This article does not comment about form fields like key resource, value resource etc. Would be great to have a generalized article that dictates how to query records from any applications in custom settings. Thanks again!
Sujit Desai it does look like the docs are missing examples and information about how to get refreshable data within them. Let me get with the doc team and see what we can do about adding in some of those advanced examples.
As for how to manipulate/use what I sent here, I typically will create an export within Celigo UI, then use our apis to make a GET request to that newly created export (you can find the export id in the url when you have that export open in the UI). Once you make the get request, copy the entire object, replace the virtual object with it, delete the id, and then you should be good to go. Note you will need a transform on it to convert the id of the response to "value" and name to "label".
Let me know if you have other questions or have another example you'd like to see in the mean time!
Sujit Desai, on another note, this refreshable setup is limited to 100 options in the drop down list. If you need something with more than 100 options, you can flip to form script and it will allow a lot more than 100 (not sure the exact limit). Here is a sample script that does the same thing as this form JSON, but allows more than 100 options per drop down. You will have to update the connection ids to your own. Again, we're working on updating documentation, but hopefully this is helpful for you and others.
import {
connections,
integrations,
exports,
imports,
flows,
request
} from 'integrator-api'
class Form {
// { fieldMap: {}, layout: { fields: [] } }
constructor() {
this.fieldMap = {};
this.layout = {};
this.layout.containers = [];
}
fields(...fields) {
this.layout.fields = fields;
}
container(type, label, ...fields) {
this.layout.containers.push({
type: type,
containers: [{
label: label,
fields: fields
}]
});
}
column(label, ...fields) {
this.layout.type = 'column';
this.layout.containers.push({
label: label,
fields: fields
});
}
}
class Field {
constructor(id, name, label, type) {
this.id = id;
this.name = name;
this.label = label;
this.type = type;
}
isRequired() {
this.required = true;
}
canBeDeleted() {
this.showDelete = true;
}
isMultiline() {
this.multiline = true;
}
description(description) {
this.description = description;
}
help(helpText) {
this.helpText = helpText;
}
typeOfInput(type) {
this.inputType = type;
}
default(defaultValue) {
this.defaultValue = defaultValue;
}
delimiter(delimiter) {
this.delimiter = delimiter;
}
keyNamePlaceholder(keyName) {
this.keyName = keyName;
}
valueNamePlaceholder(valueName) {
this.valueName = valueName;
}
mode(mode) {
this.mode = mode;
}
addOption(arg1, arg2) {
if (!this.hasOwnProperty('options')) {
this.options = [{
items: []
}];
}
if (arg2) {
this.options[0].items.push({
label: arg1,
value: arg2
});
} else {
this.options[0].items.push(arg1);
}
}
addStaticMapOptions(keyName, keyLabel, keyOptions, valueName, valueLabel, valueOptions) {
this.keyName = keyName;
this.keyLabel = keyLabel;
this.keyOptions = keyOptions;
this.valueName = valueName;
this.valueLabel = valueLabel;
this.valueOptions = valueOptions;
}
}
function formInit(options) {
let form = new Form();
// reference: https://docs.celigo.com/hc/en-us/articles/360059205112-Common-form-fields
// deconstruct form to make access to fieldMap for prop assignment simpler
let { fieldMap } = form;
//create field for staticMap
fieldMap.refreshable = new Field('refreshable','refreshableName','label this', 'staticMap');
let keyOptions = getKeyOptions();
let valueOptions = getValueOptions();
fieldMap.refreshable.addStaticMapOptions('extract', 'extractLabel', keyOptions, 'generate', 'generateLabel', valueOptions)
fieldMap.refreshable.description('Map your NetSuite locations to Shopify locations.');
fieldMap.refreshable.isRequired();
//set the layout for the form
form.fields(...['refreshable']);
// wrap up by assigning form to options.resource.settingsForm prop; return the form as well
options.resource.settingsForm.form = form;
return form;
}
function getKeyOptions() {
return exports.runVirtual({
"export": {
"name": "Lookup",
"_connectionId": "626168f90b6ef816b50c82d7",
"apiIdentifier": "ea7da9f678",
"asynchronous": true,
"oneToMany": false,
"sandbox": false,
"netsuite": {
"type": "restlet",
"skipGrouping": true,
"statsOnly": false,
"restlet": {
"recordType": "location",
"searchId": "2597",
"useSS2Restlets": false
},
"distributed": {
"useSS2Framework": false
}
},
"distributed": {
"disabled": false
},
"adaptorType": "NetSuiteExport"
}
}).data;
}
function getValueOptions() {
return exports.runVirtual({
"export": {
"name": "Get Locations",
"_connectionId": "62d95e8be26de11b5ee0dba6",
"apiIdentifier": "ede0fb0635",
"asynchronous": true,
"assistant": "shopify",
"oneToMany": false,
"sandbox": false,
"assistantMetadata": {
"resource": "locations",
"version": "v2",
"operation": "retrieves_alistoflocations"
},
"http": {
"relativeURI": "/locations.json",
"method": "GET",
"successMediaType": "json",
"errorMediaType": "json",
"formType": "assistant",
"paging": {
"method": "linkheader",
"lastPageStatusCode": 404,
"linkHeaderRelation": "next"
},
"response": {
"resourcePath": "locations"
}
},
"rest": {
"relativeURI": "/locations.json",
"method": "GET",
"resourcePath": "locations",
"pagingMethod": "linkheader",
"allowUndefinedResource": false,
"linkHeaderRelation": "next"
},
"transform": {
"type": "expression",
"expression": {
"rules": [
[
{
"extract": "id",
"generate": "value"
},
{
"extract": "name",
"generate": "label"
}
]
],
"version": "1"
},
"version": "1",
"rules": [
[
{
"extract": "id",
"generate": "value"
},
{
"extract": "name",
"generate": "label"
}
]
]
},
"adaptorType": "RESTExport"
}
}).data;
}
On my last comment, ultimately, the script is building and returning the JSON structure below. The script just also has virtual imports and exports to get the options, then uses the returned array data to populate the keyOptions and valueOptions array fields.
{"fieldMap": {
"refreshable": {
"id": "refreshable",
"name": "refreshableName",
"type": "staticMap",
"label": "label this",
"keyName": "extract",
"keyLabel": "extractLabel",
"valueName": "generate",
"valueLabel": "generateLabel",
"keyOptions": [
{
"value": "10",
"label": "Austin"
},
{
"value": "9",
"label": "Baltimore"
},
{
"value": "1",
"label": "California"
},
{
"value": "5",
"label": "Dallas"
},
{
"value": "4",
"label": "FBA"
},
{
"value": "2",
"label": "Florida"
},
{
"value": "6",
"label": "Houston"
},
{
"value": "11",
"label": "Indianapolis"
},
{
"value": "3",
"label": "Maryland"
},
{
"value": "7",
"label": "Memphis"
},
{
"value": "8",
"label": "San Mateo"
}
],
"valueOptions": [
{
"value": "67738009765",
"label": "BOISBRIAND"
},
{
"value": "67738042533",
"label": "BOUCHERVILLE"
},
{
"value": "49044553893",
"label": "California"
},
{
"value": "49920475301",
"label": "Florida"
}
]
}
}
}
Hey Tyler Lamparter this example is super helpful! I have a question though, the virtual export needs a _connectionId, I recently found out that I need to update the _connectionId when I migrate the integration from Sandbox to Production. Based on this article, https://docs.celigo.com/hc/en-us/articles/360046853572-connections-find-query- It looks like there is a way to assign an externalId on the connection, how do I do that in the UI? and is there a sample code on how to use the connections.find api? Thank you in advance!
Antonio Jr are you using JSON to build the form or using Javascript? If you're using Javascript, you could create an alias (see docs here), then reference the alias within your script to get the id of the particular environment you are in.
I'm using both, the Javascript (formInit) part is to populate the refreshable (it has more than 100 items). Can I plug that alias directly in the _connectionId part of the virtual export?
Antonio Jr it would look like this. You would just need to get the id from the options argument you are given and then pass the variable you made into the virtual export.
Here's what I did too, under the Aliases tab of the Custom Integration tile, I created an alias for the connection.
Then I updated the formInit script to use the alias
Worked beautifully! Thank you Tyler Lamparter for the direction!
Tyler Lamparter
it would be awesome if you can publish a full list of settings that can be tuned to display the records in the settings.
I am trying to select a non inventory item from NetSuite, but the dropdown only shows the first 50 records that you would see when you searched these records in netsuite.
Is there a way to see all the records in a dropdown?
Sujit Desai are you using a script? If so, what's it look like. Not really sure what you mean by “publish a full list of settings”.
Please sign in to leave a comment.