Return the Parent Location as a Search Column of a Location Search in the UI or by Script

The field Sublocation of (internal id: parent) on location records is exposed to SuiteScript when working with the location record, however it is not exposed as a search column or search filter in neither the UI or for SuiteScript. An enhancement requesting the exposure of this field to searches is already field (enhancement #143417).

However, for a location which is set to be another location's sublocation, the name field will hold the names of both the parent and the child location. For example, for two locations A and B where B is the sublocation of A, the value returned by the Name (internal id: name) search column (for the location record of location B) is A : B. In order to return only the sublocation's name without displaying the hierarchy, the field Name (no hierarchy) (internal id: namenohierarchy) can be used.

Using these fields along with the SUBSTR and LENGTH functions, a Formula (Text) column can be built containing the name of the parent location.

SUBSTR is a SQL function, which takes the following arguments:

• char
• position
• substring_length

SUBSTR returns a portion of char beginning at position and substring_length characters long. For example:SUBSTR('abcde', 0, 2)returns the first two characters of the provided input: 'ab'.

More information about SUBSTR and LENGHT can be found in SuiteAnswers : SQL Expressions (Article Id: 10101).

In order to return only the name of the parent location from the name of the child location, the characters of the child location's Name field need to be returned up to up to where the child location's Name (no hierarchy) starts. The formula to obtain this result is the following:

SUBSTR({name}, 0, LENGTH({name})-LENGTH({namenohierarchy})-3)

The three characters substracted at the end are meant to make up for the characters used to display the hierarchy " : ".

This formula can be used in the UI as the formula for a Formula (Text) column field or in a script as the formula for a nlobjSearchColumn. An example of how to set the formula in a script is the following:

var srchCol = new Array();srchCol[0] = new nlobjSearchColumn('formulatext');srchCol[0].setFormula("SUBSTR({name}, 0, LENGTH({name})-LENGTH({namenohierarchy})-3)");var srch = nlapiSearchRecord('location', null, null, srchCol);