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
mlajewsk Rookie Asked on October 6, 2020 in Saved Searches.
Add Comment
11 Answers

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.

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

Summarized results get you:

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

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

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

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.

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

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.

Rookie Answered on October 23, 2020.

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

on October 23, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.