Show From and To Locations on One Row in a NetSuite Inventory Transfer Search

The quick answer

For an Inventory Transfer transaction search, first verify that the source Location appears on negative Quantity rows and the destination appears on positive rows. Group by Internal ID and Document Number. Add two Formula (Text) columns, each with Summary Type Maximum: return Location when Quantity is below zero for From, and above zero for To. Each transaction group must contain only one distinct source and destination.

Before you start

Use a working Transaction search with known Inventory Transfer records and access to edit its Results columns. This example changes the search definition; it does not create or edit inventory movements.

An Inventory Transfer moves stock in one step. A Transfer Order has a different shipping and receiving workflow. A field or join that works for one transaction type may not work for the other.

Here, IT8201 moves 6.5 M of Flexible Hose and 18 Ea of Support Rail from North Hub to South Depot. IT8202 moves 7 M of Control Cable in the reverse direction. Keep each item's units consistent when checking its signed pair.

1. Check the transaction type and line criteria

Open the saved Transaction search named Inventory Transfer Locations. Under Criteria > Standard, verify Type is Inventory Transfer and Main Line is false. The example also sets Tax Line, Shipping Line and COGS Line to false to exclude unrelated line categories. Confirm the filters and actual line behavior in your account.

Criteria field names: Type, Main Line, Tax Line, Shipping Line, COGS Line. — Criteria row order: Type, Main Line, Tax Line, Shipping Line and COGS Line. Enlarge for the full definition.

Criteria descriptions: Inventory Transfer, followed by four false values. — In the same row order: Type is Inventory Transfer; each of the four line criteria is false.

Keep both directions in the input. Filtering standard Location to only the source or only the destination can remove the rows needed for the other location.

2. Verify the signed detail rows

The starting Results columns are Internal ID, Document Number, Item, Quantity, Location and Units, without Summary Types. Click Preview. The example returns Total: 6.

Document Number repeats IT8201 four times and IT8202 twice. — The first four rows belong to IT8201; the last two belong to IT8202. Enlarge to see the items, signed quantities and locations together.

IT8201 occupies the first four rows: Hose -6.50 at North Hub and 6.50 at South Depot, then Rail -18 and 18. The last two rows belong to IT8202: Cable -7 at South Depot and 7 at North Hub.

Quantity, Location and Units for the same six detail rows: negative source quantities and positive destination quantities. — Same row order: IT8201 occupies the first four rows; IT8202 occupies the last two. In this example, negative is From and positive is To.

Verify the signs and locations against known transfers before grouping. The 6.50 quantity shows why an equality test such as {quantity} = 1 excludes that row; use the sign test. The reverse transfer shows why alphabetical order cannot establish direction.

3. Group the transfer identifiers

Click Return To Criteria, then Results. Click Remove all to replace the diagnostic columns with the final four-column summary.

Add Internal ID with Summary Type Group, then Document Number with Group. Commit each row with Add. The internal ID keeps distinct transfers separate; the document number gives the result a readable identifier.

Results columns Internal ID and Document Number both have Summary Type Group. — Group by the transfer internal ID and its readable document number.

Do not group Location: that would split the From and To rows again.

4. Add the From Location formula

Add Formula (Text), choose Summary Type Maximum, and enter:

CASE WHEN {quantity} < 0 THEN {location} ELSE NULL END

Formula returns Location when Quantity is below zero and NULL otherwise. — The From formula uses negative quantity rows; set its Summary Type to Maximum.

Set Custom Label to From Location and commit the row with Add. Negative quantity rows supply the source location; positive rows return NULL.

5. Add the To Location formula

Add a second Formula (Text) column with Summary Type Maximum:

CASE WHEN {quantity} > 0 THEN {location} ELSE NULL END

Formula returns Location when Quantity is above zero and NULL otherwise. — The To formula uses positive quantity rows, including fractional quantities; set Maximum.

Set Custom Label to To Location and commit the row. The final Results definition contains only the two grouped identifiers and these two Maximum formula columns.

Maximum returns the greatest non-NULL text value using the database's text ordering. With one distinct qualifying location, that returns the intended location; it does not select the largest quantity. This works only when the group has one distinct source and one distinct destination; extra joins or differing locations can make Maximum hide variation.

6. Save and check both directions

Click Save & Run. The example returns Total: 2. The displayed example lists IT8201 first and IT8202 second.

Summary Document Numbers: IT8201 followed by IT8202. — Displayed example: IT8201 first, IT8202 second. Enlarge for the complete result.

The paired location columns retain that row order: IT8201 shows North Hub → South Depot, and IT8202 shows South Depot → North Hub. Enlarge either image to see the complete result, including its identifiers and both locations.

From Location values: North Hub then South Depot. — From Location in the displayed row order: IT8201 starts at North Hub; IT8202 starts at South Depot.

To Location values: South Depot then North Hub. — To Location in that same row order: IT8201 ends at South Depot; IT8202 ends at North Hub. Enlarge for both locations and identifiers together.

This is one row per transfer, not an item-quantity report. Do not add a Sum of Quantity to measure transferred stock: the balanced source and destination pairs in this example net to zero.

Common questions

Why can To Location be blank even though the transfer form has a destination?

Check the transaction type and search context first. A form field does not guarantee that the same value is exposed by a particular search field or join. Inspect current records and available fields in your account. This guide derives the two locations from checked detail rows; it makes no claim about the availability of a direct destination field.

Why does this search use Main Line false?

It needs the detail rows carrying both signed quantities and their locations. A header-only result can omit the destination-side rows. Verify the detail output on known Inventory Transfers before relying on the formulas.

Why use Maximum for a location name?

Each formula returns the relevant location text on one side and NULL on the other. With one distinct qualifying location in the group, Maximum returns that text. If several different locations qualify, Maximum returns the greatest text value under the database's text ordering and can conceal the difference; inspect the detail rather than treating that output as a validation.

Why did one of the two location columns become blank?

Check for a wrong transaction type, a Location filter that removed one direction, zero quantities, or detail rows that do not follow the expected signs. Also check whether grouping or joins changed the input grain. Return to the unsummarized rows and compare them with the transfer.

Can I report a row per item and include transferred quantity?

Use a separate variant grouped by the transfer internal ID and Item, with Units or another line discriminator where needed. Validate a direction-specific numeric quantity formula against the detail rows, such as summing only the positive side. Keep units consistent and investigate repeated item lines or duplicate joins before summing. For balanced pairs in this example, a plain Sum of both signed directions is zero and does not measure the moved quantity.