RE: 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
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
