0

Multiple Filters

Benjamin

July 12, 2021 21:07

None

Follow

I am attempting to extract inventory case quantities on hand from a non-Nav database using a universal connector using the following formula:

=NL("Sum","Inventory Location File","ON_HAND_IN_UM","ITEM_NUMBER",C5,"LOCATION",D5)

The item number (sku) is one filter; the inventory location code is the second filter. I am successful in getting the correct data when using just one filter, either the item number or the location code by themselves, but am getting 0 when using both filter values when I am 100% certain data exists matching both filter criteria.

Thoughts?

• Facebook
• Twitter
• LinkedIn

1 comment

• 0
  
  BryanAtTigunia December 16, 2021 18:37

  Are you thinking that it should return data where either of those values is true or when both are true?  Your function will only sum up values where both Item number and Location are true.

  If you need when either is true, you will need to use Array Calculations to determine the values that meet either of the criteria.

  If you believe data should be returned when the item number is 1234 and the location is NY (example) but nothing is being returned when both are applied, you will have to do some digging.  I would recommend using a Rows replicator to bring back the primary key for when the item number is 1234 and on a separate sheet, a Rows replicator to return the primary key value for when the Location is NY.  Then use Excel to compare values.  In theory, based on your results, there should be no matching values because neither is true at the same time.

  It is also possible your Sum is 0 so check the drilldown after running to see if there are records or not.