Hi,
I’m trying to use the NF function in column A2 to get the description name (I already used the NL in column A1 for the lookup value), but when I type NF ($A1, “Description”) I get a #Value error. Can anyone let me know what I’m doing wrong.
Date
Votes
5 comments
-
Jet Reports Historic Posts First, you can't use Jet formulas in row 1 or column A. However, Jet will probably fix this for you the first time you run the report (everything will get moved down and over). What is your NL function exactly?
Regards,
Hughes -
Jet Reports Historic Posts This is what I’m using (I moved the formulas to Column C9):
• NL (“Rows”,”Item”,”No”)
• NF ($C9,”Description”) <— Generates a Value# error
Note: I’m using a Nav 09 Classic Database -
Jet Reports Historic Posts So the issue is that you can't use an NF function when you are specifying a field in the NL function. So change your functions to this:
=NL("Rows","Item") =NF($C9,"No.") =NF($C9,"Description")
Since, the record key displayed in column C isn't very useful, most people hide the column by putting the keyword "Hide" in C1. Then that column will be hidden when you run the report. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Perfect! This worked thank you so much for all your help. I was wondering if you’re able to help me with one more thing. I’m trying to link up fields/data from several tables, but when I run the report it takes forever to load and it even freezes which makes me believe I’m not doing something right. Below is the syntax/formulas I’m using, and it would really help me if you can review and let me know what I’m doing wrong or if there’s a better way to pull this data.
Tables Used:
Item
Purchase Price
Sales Price
Formulas Used:
=NL(“Rows”,”Item”)
=NF($C9,”No.”)
=NF($C9,”Description”)
=NL(“Rows”,”Purchase Price”)
=NF($C10,”Unit of measure code”)
=NF($C10,”Minimum quantity”)
=NF($C10, “Direct Unit Cost”)
=NL(“Rows”,”Sales Price”)
=NF($C11,”Minimum Quantity”)
=NF($C11,”Unit Price”)
=NF($C11,”Location Code”) -
Jet Reports Historic Posts So currently this is probably producing a very large amount of data. What you are doing is for each item you are retrieving EVERY record from Purchase Price, not just those connected to that item. Then for each of those rows, you are retrieving EVERY record from the Sales Price table, regardless of whether it is connected to that item. The result is a combinatorial explosion which produces many rows that don't make any sense at all.
The solution is that you need to filter each nested NL(Rows) function by some data retrieved from the parent function(s). So I can see that Purchase Price and Sales Price both have an Item No. field, so you will need to add a filter to those NL(Rows) functions to filter on the item number you are retrieving with the NF from the Item replicator.
What I'm not sure about how the Sales Price and Purchase Price are related to one another. I'm pretty sure you don't want both of those NL(Rows) on the same row together without relating them. You may just want a list of sales prices and then a list of purchase prices in which case you could change your first function to =NL("Rows=2","Item") and move the Sales Price replicator down to the next row below the Purchase Price replicator. Does this help?
Regards,
Hughes
Please sign in to leave a comment.