We have the following sample table in Navision:
no. - name - master no.
1a - apple - 1
1b - pear - 1
1c - banana - 1
2a - melon - 2
3a - tomato - 3
4a - lettuce - 4
4b - avocado - 4
I would like to create a jetreport filter that would only select the master no's that have more than 1 item.
So in the example above I would like the filter to return: 1,4
Any ideas?
Thanks!
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Thanks a lot!
Changed it slightly, but it did the trick!
Final code:
=NL("Rows";"detail item";"Master No.";"=NL(""CountUnique"";""detail item"";""Navision No."";""item status"";""5|10"";""Master No."";NF(;""Master No.""))";">1") -
Jet Reports Historic Posts Hi,
I believe you could use a calculated filter field for this. The formula would look something like this:
=NL("Rows","Table","Master No.","=NL(""Count"",""Table"",,""Master No."",NF(,""Master No.""))",">1")
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Hi,
Thanks for your reply Hughes.
I believe you could use a calculated filter field for this. The formula would look something like this:
=NL("Rows","Table","Master No.","=NL(""Count"",""Table"",,""Master No."",NF(,""Master No.""))",">1")
Does that work for you?
Regards,
Hughes
What exactly is a calculated filter field? It does sound like something I'd be looking for.
If I try your formula, the "=NL(""Count"",""Table"",,""Master No."",NF(,""Master No.""))",">1")
Would always result in a 'count', a number, not a filter of different master no.'s?
As for the NF part, doesn't an NF always have to refer a previous NL function?
EDIT: just thought I'd give the formula a whirl. It amazingly does not return an error! :)
However, I realise now that I need to filter on another column as well to determine if it is counted. Which is "Navision No."
no. - name - master no. - Navision No.
1a - apple - 1 - 000010
1b - pear - 1 - 000011
1c - banana - 1 - 000011
2a - melon - 2 - 000014
3a - tomato - 3 - 000015
4a - lettuce - 4 - 000016
4b - avocado - 4 - 000016
So in this case it should only show Master no. "1", as that's the only one where the count of the different Navision No's is > 1.
I'm not sure if this is at all possible, making it rather complicated!
I tried =NL("Rows";"detail item";"Master No.";"=NL(""Count"";""detail item"";;""Navision No."";NF(;""Navision No.""))";">1";"item status";"5|10")
But that list includes double Navision no's (like example 4A/B). -
Jet Reports Historic Posts So with a calculated filter field (a filter field that starts with = and contains an Excel formula), the value of the filter field gets passed through Excel's calculation engine. In this case an NF with an empty key value will get the field value from the calling function for each record where the calculated filter field is evaluated. For your modified example, I think you could do something like this:
=NL("Rows";"detail item";"Master No.";"=NL(""CountUnique"";""detail item"";""Navision No."";""Master No."";NF(;""Master No.""))";">1";"item status";"5|10")
Does that work for you?
Regards,
Hughes
Please sign in to leave a comment.