Conditionally Replicating Variant and non-variant and total in NL("Rows

Hey, I am very new to this and have just taught myself (thanks a lot to this site) but am stuck with an inventory report.

Background - we have never used variants in Nav before but are now making use of the field. During this transition, which will take many months, we have many SKUs with no variant and with a variant, but many SKUs also have no variant at all.

I have an inventory list with "Rows","Item" and "Sums","Item Ledger Entry" for each location. I can pull in the variant data but the problems I have are:

- Rows=2: Every Sku has a 2nd "Rows" for variant info, yet only 20% have a variant, so too much unnecessary data and larger file. I have put a conditional hide in B6 but my team all use the auto filter regularly, so the hidden rows are no longer hidden after the auto filter is cleared. This gets all the data I want (a row for SKU inventory with no variant filter plus a row for each variant filter including blank), but I don't know how to refine it to only produce variant data for items with variants. Can the '2' in Rows=2 be conditional or be substituted with a formula e.g. Rows=C6? This would let me me conditionally replicate the 2 lines only for items with a variant code, and those without will just have the 1st row replicated.

- NP(Union and Filter doesn't work because I need to have for example, 3 rows minimum for a SKU if it has one variant code (4 rows for 2 variants, etc). One showing total inventory (no variant filter), one for blank-variant filtered totals ("@@"), plus total for the actual variant. 

- having 2 rows pulling the same info but the 2nd row filtering variants give me the data I need but it isn't sorted correctly, it is all the SKUs without a variant filter, and then all the SKUs with a variant filter.


Some examples I have tried:

This pulls a 2nd row for every SKU which floods the results

=@NL("rows=2",NP("Union",NL("Filter","Item","No.","No.","<>*@EACH*","Company=",Options!$C$2,"Link=","Purch. Inv. Line","No.","=No.","Posting Date",Options!$D$10),NL("Filter","Item","No.","Quantity on Hand",">0")))   

with a 2nd "rows" below

B8=IF(OR(X8=0,H8=0),"Hide","")   I8=@NL("Rows","Item Ledger Entry","Variant Code","Item No.",$C8,"Company=",Options!$C$2) 

This one doesn't have a row for an items total, only for variant codes and blank variant (need the total as well)

=@NL("Rows",NP("Union",NL("Filter","Item","No.","No.","<>*@EACH*","Company=",Options!$C$2,"Link=","Purch. Inv. Line","No.","=No.","Posting Date",Options!$D$10),NL("Filter","Item","No.","Quantity on Hand",">0"))) 

and in the same row =@NL("Rows",IF(AND($H7>0,$Y7>0),"Item Ledger Entry",{""}),"Variant Code","Item No.",$B7,"Company=",Options!$C$2)

Any tips greatly appreciated, as this is driving me crazy. Can you use a VBA with a report to action after the report is run? Basically, the easiest solution would be to delete or permanently hide the rows that are conditionally hidden in my first example, but cant figure that out. Or try to union or join so that I pull all the rows of filtered items, plus all the rows with filtered items with a variant, but duplicates are pulled as well

e.g. items and their variant AAA BBB BBB1 CCC CCC1 CCC2 DDD

would result with this list for with no variant filter AAA BBB CCC DDD and this list of items with a variant, combined BBB BBB1 CCC CCC1 CCC2

so you would have










1 comment

Please sign in to leave a comment.