I have a quick question I’m hoping someone can help me with. The attached spreadsheet is the one I was working on in class last week and it seems to work fairly well except that I need to find a way a have the error messages be blanked out. Basically, the part has a replenishment type and if it is a purchase order the production order side should be blank (instead of having all the value or name errors) and vice versa if it is a production order. I tried using an IF (ISERROR in the formula, but this does not work very well. Any suggestions?
Date
Votes
3 comments
-
Jet Reports Historic Posts Good morning Jmask,
Whenever you get an error in a cell with a Jet Reports function, try the Jet Reports Debug button. This may help you to find out what causes the error. My guess in this case is that you will get an error message that an empty filter is not allowed.
The report you uploaded has a value error in Column W - the Purchaser code. This field is taken from the NL function in U5.
The NL function in U5 is filtered by a value in Cell E4. If this Cell is empty you will get an error in U5, leading to an error in W5.
To solve the problem of filtering with an empty value you will have to use the "@@" symbols in your Filter. In Cell U5 your function would then look like this:=NL("Rows","Purchase Line",,"Outstanding Quantity",">0","No.","@@"&$E4)
Then you can change the contents of V5 and the other colums that retrieve fields from U5 back to the original formula: E.g.=NF($U5,"Document No.")Jet Reports now knows that U5 contains no link to the database and therefore shows an empty cell.
Another thing to be aware of in this report is that you are generating Rows for Purchase order lines and Rows for Production order lines with NL functions on the same Row. Hmmmm… that may be a bit cryptic… You have an NL generating Rows in Cell U5 and an NL generating Rows in Cell AD5. So, 2 NLs on Row 5. Both NLs are referring to Cell E4 for the Item no.
Sometimes Excel has problems with diagonal references. And your functions could cause such a problem as both NL functions are generating Rows… and what you don't know is how the reference to Cell $E4 will be copied across the Rows.
My suggestion would be to copy the value of E4 to E5 and let your NL functions refer to E5 instead.Cell E5: =$E4 Cell U5: =NL("Rows","Purchase Line",,"Outstanding Quantity",">0","No.","@@"&$E5) Cell AD5: =NL("Rows","Prod. Order Line",,"Item No.","@@"&$E5,"Remaining Quantity",">0")And to hide the value in Cell E5 from the report, change the Font color to white :)
Hope this helps you another step forward in working with Jet. Please let us know if it works. -
Jet Reports Historic Posts Excellent Hans!
It worked like a charm. The only other question I would have about my spreadsheet is that I am trying to design it such that a person with a viewers license can run it just by entering a new part number on the "Options" spreadsheet. Right now I have to change to design mode and type in the part number and then run it. Is there a way to enter a different part number in view mode and have it run a new report?
latest spreadsheet attached….. -
Jet Reports Historic Posts Hi Jmask,
Glad to hear it works :)
Modified the report you uploaded so that it shows a request form when the report is run. The yellow colored cells on the Options sheet are modified to define the sheet and a lookup form to help the users select the appropriate item. Please read the Jet Reports Help or PDF manual or the documentation you got in your class for more info.
Please sign in to leave a comment.