Hi,
I'm trying to create a report on expected delivery dates. It's really simple, just pulling out purchase lines, not a filter to another table in site. The complication is that they want filters on a date range. If the confirmed date has been filled in then they want to filter on that. If it hasn't, then they want to filter on the expected date.
I can't use normal filters because they will just give me confirmed date and expected date. I tried using two filters to give me a list of document numbers and union/join but that gave me so big a filter that it errored.
Does anyone have any ideas? It's not a complicated concept but I can't quite see how to implement it in Jet.
Date
Votes
3 comments
-
Jet Reports Historic Posts why not use two ROWs commands - one to pull the line numbers where the confirmation code fits your date range
then the second one to pull the line numbers where the confirmation date is blank, but the expected date fits the date range.
something like:
B4 = filter date
C4 = NL("ROWS","PURCHASE LINE","LINE NO.","CONFIRMATION DATE",$B$4,….)
D4 = NL("ROWS","PURCHASE LINE","LINE NO.","CONFIRMATION DATE","''","EXPECTED DATE",$B$4….)
note that in D4, the confirmation date filter is double quote("), single quote('), single quote('), double quote(") - this is how I wo8uld have to do it in my database, assuming the field is truly blank. -
Jet Reports Historic Posts I don't think I can set an Options filter on the date in the formula, but if you create a table you can add a formula that will show one date or the other. I built this off the Sales Line table. I set either the Requested Deliver Date or the Shipment Date to show depending on if the requested delivery date is blank or not.
=NP("Formula","IF([@[Requested Delivery Date]]="""",[@[Shipment Date]],[@[Requested Delivery Date]])")
This says, If the Requested Delivery Date is blank then show Shipment Date, otherwise show the Requested Delivery Date. We could also do this with a rows replicator and hide the other columns, but then the table filtering is lost.
Even though it wont let you set the filter at the beginning of the report, if you run the table you can click in the top header row and filter out the dates you want which will include both sets. This will allow you to have one list off both dates that will allow filtering.
Hope this helps.
Jason -
Jet Reports Historic Posts It can be done using calculated fields. The final formula that worked was:
=NL("Rows","Purchase Line",,"+=IF(NF(,""Confirmed Date"")="""",NF(,""Expected Receipt Date""),NF(,""Confirmed Date""))","Date&"&NP("DateFilter",Options!$F$5,Options!$F$6),"Location Code","STORES","Outstanding Quantity",">0","Job/Contract No.",Options!$F$7,"Phase No.",Options!$F$8)
NOTE: The Date& before the date filter.
Please sign in to leave a comment.