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

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.

Advanced 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

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

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

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

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

From NetSuite Support:

As per checking of your case message, I understand that your concern is about populating the ‘To Location’ and ‘From Location’ of your Inventory Transfer saved search and have them in one line only.

Per my initial investigation using the preliminary details you provided, I was able to confirm that functionality that you are looking for is an existing system limitation. Inventory Transfer saved searches are currently only allowing the ‘Location’ field to show both the ‘TO’ and ‘FROM’ locations in different line.

We understand that the aforementioned feature will be beneficial for you. Fortunately, there is already an Enhancement request logged for the same concern, details of the Enhancement are as follows:

178607 List > Search > Saved Searches > New > Transaction. Please include ‘To & From Location’ field that sources from Inventory Transfer.
I can attach this case to add a vote to the above enhancement. Our system engineers periodically reviews these enhancements and hopefully be implemented in our future releases. Please note that there is not specific time-frame for its release though.

Please upvote this Enhancement request (178607).
Thanks!

Rookie Answered on October 21, 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   Become a Sponsor