Saved search: transfer order – no “To Location” data

The {location} field on an Inventory Transfer saved search seems to contain both the ‘from location’ and the ‘to location’ resulting in one transfer order search generating multiple lines

  • the first line {location} results with the ‘from location’ data and
  • the second line for the same transfer ID {location} results in the ‘to location’

If filtered with ‘main line’ true then you get one line per transfer but only ever the ‘from location’, never the ‘to location’.

Need to get a saved search to produce, in one line, both the ‘to’ and the ‘from’ locations.  Looking at the transfer in GUI the ‘transfer from location’ is: [Field ID: location] and the ‘transfer to’ field is: [Field ID: transferlocation].

The issue is that putting {transferlocation} on a saved search always returns -none-.

 

Closest Answer Id: 32418  sort of addresses that this is an issue but the suggested use of:

Formula (Text) | Formula = case when nvl({quantity},0) < ‘0’ then {location} else null end | Custom Label = From Location

Formula (Text) | Formula = case when nvl({quantity},0) = ‘1’ then {location} else null end | Custom Label = To Location

–>  Does not get both data points on one line.

 

Looking for Transaction saved search

Critera =

  • Type is Inventory Transfer

Results=

  • Date, Group
  • Document Number, Group
  • Amount (Credit) , Sum

Need (on the same line, could then be concatenated)

  • Transfer from location
  • Transfer to location
Rookie Asked on October 6, 2020 in Saved Searches.
Add Comment
11 Answer(s)

Hi,

It feels like this should be easier than it seems to be!

If you are happy with them both in a single field then a Formula (Text) like this will work:
NS_CONCAT({location})
N.B. With NS_CONCAT, you need to make sure the Summary Type is either Minimum or Maximum.

To get them in separate fields will require  some further manipulation as part of that formula.
Here’s an example that gets the to and from in separately:

REGEXP_SUBSTR(NS_CONCAT({location}),'^[^,]+')
REGEXP_SUBSTR(NS_CONCAT({location}),'[^,]+$')

Thanks,

Chris

Intermediate Answered on October 7, 2020.
Add Comment

UPDATE – Changed to format as the formulae as code, based on Mike’s later message, otherwise the quotes get changed to an unsupported character.

Hi Mike,

Ah, my TO only had one line. I’ll take another look with multiple lines when I get a moment but the error you’re receiving with the REGEXT is because of the quotes that this forum uses.

If you change them to regular, single-quotes, it should work.

REGEXP_SUBSTR(NS_CONCAT({location}),'^[^,]+')
REGEXP_SUBSTR(NS_CONCAT({location}),'[^,]+$')

Thanks,

Chris

Intermediate Answered on October 7, 2020.
Add Comment

Hi Mike,

I think I found us an altogether simpler solution that yields results in this format:

RE: Saved search: transfer order – no “To Location” data

Here is the setup that I used:

RE: Saved search: transfer order – no “To Location” data

RE: Saved search: transfer order – no “To Location” data

I hope that gives you what you need.

Thanks,

Chris

Intermediate Answered on October 7, 2020.
Add Comment

So there is a way around this. But it’s not pretty. My search was designed to identify inventory transfers between costed and uncosted locations, so just ignore those lines.

Basically I the key here is to use the quantities as a comparison basis. One line will show a negative quantity, that’s your “From Location” and the other will be positive, that your “To Location”. So I set it up so that I have two columns that show location. If you can get the below, then you can run a summary search to roll it all up. See below

RE: Saved search: transfer order – no “To Location” data

I did this by using these formulas and setting up these summarys:

RE: Saved search: transfer order – no “To Location” data

The caveat is that you can’t filter on the to and from locations.  Either download to CSV and use in excel. Or if you’re looking for a specific attribute, which I was, then build a field into the location record. We had “Costing Type” it could either be “Costed” or “Un-costed” Then I used the below formula in the summary Criteria

RE: Saved search: transfer order – no “To Location” data

Basic principle was:

Costed = 1

Uncosted = 2

Therefore only show me results where the sum = 3

Not pretty… but it does work.

Rookie Answered 3 days ago.

Realizing that the formulas are not fully readable.

 

Qty = Formula Numeric

CASE WHEN {quantity} < -0.01 THEN -{quantity} ELSE {quantity} END

Summary Type = Group

 

From location = Formula Text

CASE WHEN {quantity} < -0.1 THEN {location} WHEN {quantity} IS NULL THEN NULL ELSE NULL END

Summary Type = Maximum

 

To Location = Formula Text

CASE WHEN {quantity} > 0.01 THEN {location} WHEN {quantity} IS NULL THEN NULL ELSE NULL END

Summary Type = Maximum

3 days ago.
Add Comment

If you want the information on the same row, then you should use the Summary Type column. Set a column that is unique like Internal ID as the Group column to group rows with the same internal id togerther as one row. Then you can use Maximum or Minimum on your formula columns from the  SuiteAnswer article to get the biggest or smallest value of the grouped rows. The formula is setup to basically have only one value, so the minimum and maximum are the same value.

Intermediate Answered on October 7, 2020.
Add Comment

Thank you Chris and battk for the fast answers!

Yes, if NetSuite would expose/use the Field ID: transferlocation in addition to Field ID: location this would be easy.

 

Getting closer but not preferable.  I am receiving:

With Internal ID as Group and NS_CONCAT({location}) as Max (or min, no difference)

  • one line per internal ID <– good
  • the column for NS_CONCAT({location}) as Max (or min, same results) has many ‘to locations’ (duplicates, looks like always 4 for the from location and 4 for the to location… happens to = the number of lines when the Internal ID is not set as Group… like as if the Max grouping function is not properly applying)  <– lots of not needed repetition making it hard to read
  • the REGEXP_SUBSTR(NS_CONCAT({location}),’^[^,]+’) as formula text with Min or Max returns “ERROR: Invalid Expression

Thanks,

Mike

Rookie Answered on October 7, 2020.
Add Comment

Indeed the single-quote copied from the forum was causing the error. Thanks!

Not getting the error now but am getting (which may help/confuse the other issue) the same location returned for ‘^[^,]+’ as ‘[^,]+$’.

Now it seems to not be an issue with those formulas (I think they are working as they are supposed to) but the data in NetSuite and thus what is shown in the results of NS_CONCAT({location}).

Those results look like this (Store A is the from and Store B is the to location) this is all returned in one field:

Store A,
Store B,
Store B,
Store B,
Store B,
Store A,
Store A,
Store A,
Store A,
Store A,
Store A,
Store A

This happens on all transfers even just one item transferred once from Store A to Store B.

‘^[^,]+’ is correctly pulling the first location in the list Store A

‘[^,]+$’ is correctly pulling the last location in the list Store A

The issue seems to be in figuring out why NS has the ‘from location’ listed 8 times and the ‘to location’ listed 4 times… and why in the jogged order it is in?

 

Again thank you for your assistance on this!

-Mike

Rookie Answered on October 7, 2020.
Add Comment

Hi Mike,

Try sorting by Line Sequence Number.

Thanks,

Chris

Intermediate Answered on October 7, 2020.
Add Comment

Small change when sorting by Line Sequence Number, results are:

Store A, – line sequence number = 0
Store A, – line sequence number = 0
Store A, – line sequence number = 0
Store A, – line sequence number = 0
Store B, – line sequence number = 1
Store B, – line sequence number = 1
Store B, – line sequence number = 1
Store B, – line sequence number = 1
Store A, – line sequence number = 2
Store A, – line sequence number = 2
Store A, – line sequence number = 2
Store A – line sequence number = 2

Still end up with the From Location at the top and bottom of the list.

Rookie Answered on October 7, 2020.
Add Comment

Ok, I think we are down to:

inventory transfer  -vs-  transfer order

 

Chris your solution is elegant and works for ‘transfer orders’, Thank you!

My company uses both ‘inventory transfer’ and ‘transfer order’ for slightly different purposes.

On ‘inventory transfer’ type, NetSuite is unable to connect/join the data in ‘to location’ and it always produces -none- result even though the form for ‘inventory transfer’ uses that field. I have a support case open and will update this thread on what NetSuite says.

 

 

Rookie Answered on October 8, 2020.

Glad you’re happy with the solution for Transfer Orders and hope you get a speedy response to the case!

on October 9, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor