Skip to main content

Querying NetSuite Records in Custom Form Builder Settings

Comments

5 comments

  • Official comment
    Tyler Lamparter Senior Solutions Consultant
    Answer Pro
    Celigo University Level 4: Legendary

    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"
            }
          }
        }
      }
    }
  • Sujit Desai Referral Partner
    Engaged
    Celigo University Level 4: Legendary

    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!

    0
  • Tyler Lamparter Senior Solutions Consultant
    Answer Pro
    Celigo University Level 4: Legendary

    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!

    0
  • Tyler Lamparter Senior Solutions Consultant
    Answer Pro
    Celigo University Level 4: Legendary

    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;
    }
    0
  • Tyler Lamparter Senior Solutions Consultant
    Answer Pro
    Celigo University Level 4: Legendary

    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"
            }
          ]
        }
      }
    }
    0

Please sign in to leave a comment.