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

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

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

Hi Mike,

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

Here is the setup that I used:

I hope that gives you what you need.

Thanks,

Chris

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, 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. Essentially, what I’m doing is tricking the system, by writing a formula that will only the desired location (To or From) otherwise null. Once you’ve done that the maximum summary type does work. Below is the unsummarized results.

Summarized results get you:

I did this by using these formulas and applying these summary types:

I’ve used this method to do a lot of things that didn’t seem possible at first, and which NS will openly tell you isn’t possible.

The caveat is that you can’t filter on the to and from locations. That won’t be possible until NS enables that field, so you should still vote for the enhancement (I did quite some time ago). With this search set up you can 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.

Because we’re tricking the system you can’t set up a rule to compare the locations. But you can work around it by using a summary formula type based on the same principle as we used to get the search results in the first place.

Basic principle was:

Costed = 1

Uncosted = 2

Therefore only show me results where the sum = 3 

Not pretty… but it does work.

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.

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

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

Hi Mike,

Try sorting by Line Sequence Number.

Thanks,

Chris

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.

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.