I have a very simple report. (details below)
rows=3
NL Purchase Header
"No."
"Buy-from vendor name"
"Your reference" (this is a custom field in navision)
"Expected Receipt Date" (not sure if this is custom or not)
"Created By" (this is the user who created the P0)
(Placed on row 3 as stated in Rows=3)
NL Purchase Line (Document No. is the connector)
"Item No."
"Quantity"
"Quantity Received"
"Quantity To Receive" (this is a formula =sum(Quantity 'minus' Quantity Received)
I would like to filter out the entire line if the "Quantity To Receive" cell = zero
Basically, if the item has been fully received, I don't want to see it. :)
Thanks :) :)
Date
Votes
5 comments
-
Jet Reports Historic Posts This is going to be a tough one to get done with a filter since there isn't really a field to filter. If you wanted to filter a field that existed with a calculated value, you might stand a chance, but this isn't the case here. One thing that you could do though is to use a conditional hide. So, just make your Quantity to Receive field and then in R1CB add Hide+? to signal a conditional hide. In col B of the row that you are replicating add something like this =IF(D3<>0,"Show","Hide"). Assuming that your quantity to receive is in cell D3, this hide all rows with quantity to receive = 0.
-
Jet Reports Historic Posts EDIT EDIT EDIT…..
If anyone read my last post i made a mistake when i typed the Conditional calculation in. in R1CB i typed in Hide+ (but didn't put the ?) I didn't realize that was part of the calculation. Sorry for the confustion.
JDamian has it right on the money, Now i just need to find a way to hide the rest of the fields that belong to those particular P0's. If anyone has any suggestions, i'd be happy to hear them, but i'm not counting on this being able to be done.
Thanks again Damian for the time you took to explain how to make this work. Much appreciated. -
Jet Reports Historic Posts Why don't you try using this in your NL("Rows=3","Purchase Header") formula:
=NL("Rows=3","Purchase Header",,"No.",NL("Filter","Purchase Line","Document No.","Document No.","=IF(NF(,""Quantity"")-NF(,""Quantity Received"")<>0,NF(,""Document No.""))"))
I got this from another posting about comparing 2 fields. http://community.jetreports.com/viewtopic.php?f=7&t=99
I guess you wouldn't want to do this if you had a lot of records in the "Purchase Line" table, but I wouldn't expect you would have a lot of them. -
Jet Reports Historic Posts Just my 2 cents to see if you could build the same report with a different filter…
Have you tried to filter the purchase order lines on the Outstanding Qty (Base) field? This is the "quantity to receive" field that NAV uses in the calcformula of the Qty on Purch. order field in the Item table.
So it would be something like NL("Rows","Purchase line",,"Document type","Order","Document no.",CellRefToDocNo,"Type","Item","Outstanding Qty (Base)","<>0")
Does this work for you? -
Jet Reports Historic Posts I will have to read this again in the morning and make sure i understand what you wrote. I'm trying to keep it simple and quick to run. I was going to attempt to add on to "damians" help and try the following:
Each item on the P0 is on a seperate row
The qty and qty received are in their own cells and columns
example "item Number", "qty", "qty received", "qty to receive" (there are other items but they are not relevant for this)
Add the qty and qty received columns of each po line. If the two sums = the entire P0 is hiden from the report
only problem is its unknown how many line items are on each P0 so i'm having a bit of trouble getting the columns to add up.
Please sign in to leave a comment.