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
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.
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
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.
Glad you’re happy with the solution for Transfer Orders and hope you get a speedy response to the case!