Monday, July 1, 2019

Return the Value of the Field Sublocation Of in a Search of Type Location by SuiteScript or in the UI

The requirement is to perform a search of type Location and return the value of the field Sublocation Of as a search column by SuiteScript or in the UI.

In the UI the location records can be found by navigating to Setup > Company > Locations. When viewing or editing a location record the field Sublocation Of can be seen on the location record. When consulting the location record in the SuiteScript Records Browser the Sublocation Of field can be seen in the Fields section, however it is not displayed in the Search Columns section.

This means that the field is not yet exposed as a search column. An enhancement which requests the exposure of this field as a search filter and search column is already filed (enhancement #143417), however the following alternate solution can be used to get the value of this field as a search column.

It is possible to obtain the name of the parent location from the location's name field. For example, for a location A, which is the sublocation of location B, the name field will contain B : A. The field namenohierarchy will contain only the locations name. In this example A. Therefore, by using these two fields and the functions SUBSTR and LENGTH the name of the parent location can be obtained (value of Sublocation Of).

A possible approach is the following formula:

SUBSTR({name}, 0, LENGTH({name})-LENGTH({namenohierarchy})-3)
The 3 positions substracted at the end are meant to make up for the positions showing the hierarchy " : ".

No comments:

Post a Comment