I have a report that has two different tabs pulling information for the same table using the same field. On one table the information pulls the No. field as ="4056" and the other tab, which is also using the field No. is pulling the number without the ="". The formatting for each is general. I am unable to get the two to format the same. This is a problem when trying to summarize the two tabs. What am I missing? The field is for the Sales Header No.
Any help would be appreciated.
Thanks,
Date
Votes
13 comments
-
Jet Reports Historic Posts Official comment Hello -
I assume you mean that the first Line number returned shows the NL(Rows) function:
while all the other Line numbers show with the equal/quote formatting:
As mentioned earlier, this is what should be expected. The results are enclosed in quotes so as to accommodate results that have leading zeroes (otherwise, Excel would strip off those zeroes - which could prevent a report from working as expected). -
Jet Reports Historic Posts The ="xxx" (or "equal/quote") formatting was added to Jet Essentials a few years ago to accommodate reporting of data with leading zeroes (e.g., Department # 00136) {Excel doesn't particularly like leading zeroes}.
I suspect that formatting is showing up as the result of an NL("Rows") function.
e.g.,
It would be possible for other references to now have that formatting. How are the cells that do not have the equal/quote formatting being populated? -
Jet Reports Historic Posts Both tabs are using the same type. Only the filters are different
Tab show only number ie 47090
=NL("Rows","Sales Header",,,,"Document Type","Order","Order Progress","C")
Tab showing number with quotes: ="47090"
=NL("Rows","Sales Header",,,,"+Product group Code","01..02","Order Progress","<>C&<>F","Document Type","Order") -
Jet Reports Historic Posts If you select the one of the cells (on each of your sheets [that's what you mean by "tab", right?) and check the format of the cell…
Are the cells of the same format?
I would expect all the cells to show the equal/quote format. I'm curious why that's not consistent.
Unfortunately, I'm not able to test your functions as I do not have your custom fields ('Product Group Code', 'Order Progress').
Also, what is the point of the blank filter? (causing the extra commas: "Sales Header"[size=150],,,,[/size]"Document Type"…) -
Jet Reports Historic Posts The two tabs have the same information including column headings except for filters.
The tab with the ="XXX" (Tab A) has a filter that includes Order Progress "<>C&<>F"
The other tab (Tab B) has a the same filter but with just the letter "C"
All other filters are the same
I have copied the entire page from Tab A to the Tab B and only changed the filter. Same problem
I also changed the filter on Tab B to mirror Tab A and the results changed from no equal/quote to ="XXX"
Is the ampersand the problem? -
Jet Reports Historic Posts The list is created by a data dump. Therefore, the field is left blank.
-
Jet Reports Historic Posts Thanks.
I'm familiar with a data dump (which would explain one set of commas ,, I was just curious why there were two set ,,,,).
First question: What version of Jet Essentials are you using?
If you are using a data dump, then I assume that the cells where you are seeing the equal/quote formatting contain NF() functions. Right?
Let's test your hypothesis.
I've created a rudimentary report using the same table. Each worksheet contains a single data dump NL(Rows) function, two NF() functions, and one cell reference.
The only difference between the worksheets is that the NL(Rows) function on Sheet1 contains a filter which includes <> and an ampersand. Sheet2's function does not.
Do you experience the same symptoms with this report? -
Jet Reports Historic Posts I get the same results on both tabs. Both contain the =""
-
Jet Reports Historic Posts In that case, you will most like need to run this one by Jet Reports Technical Support team (https://support.jetreports.com/) so that you can send your report to them.
If you are able to duplicate what you are seeing by using standard (vs. custom) NAV fields, that will work out better. -
Jet Reports Historic Posts I am having the same problem, except it occurs in the same NL(Rows) function. I am retrieving the Item No. from a purchase order. If there are more than one item on that purchase order, the second and following Item No.s (i.e. all but the first) are enclosed in a ="".
=NL("Rows","Purchase Line","No.","Document No.",$C3,"Type","Item") -
Jet Reports Historic Posts I should clarify that it's not really an issue, just something I don't understand. In the examples below, look at PO No. 5000392. The first item has no quotes, the second does. The first is not the function, just the next in line for the lookup.
-
Jet Reports Historic Posts Hi -
I see that the first item for PO #500392 does not include the formatting, while the next item does.
If we consider the example I used before, Here is what the design looks like:
What is different this time, however, is that I have added "+Values" to cell A1.
Using +Values will remove all Jet functions from the report when it is in Report mode (versus Design mode).
So now, when I run the report, I'll see that the first item returned from my NL(Rows) function shows without the "equal/quote" formatting:
While subsequent lines do include that formatting:
-
Jet Reports Historic Posts I see! Thanks!
Please sign in to leave a comment.