Hello. Has anyone come across an issue I am facing (and solved it!). I have a Jet report that is pulling data out sorted by the item code field. A formula in the sheet accesses that list as a vlookup and therefore it needs to be in Excel's sort order ascending to work properly. But the way Jet returns things in ascending order appears to be different to how Excel does the same so the sheet is unable to accurately calculate as intended as the vlookup picks up the wrong data.
By way of illustration - two products with item codes CB-1B and CB-TB is sorted in ascending order (with a + sign in from of the Item No. field in the NL function) by Jet and returned to Excel as
CB-TB
CB-1B
Whereas if you sort in Excel by the same column in ascending order,. It sorts it to
CB-1B
CB-TB
So a formula looking up as vlookup to the first data set for a non-matching element will pick up the wrong data….
Problem for what I need to do….. has anyone seen this- is it something nice and simple like a configuration to set Jet to sort differntly or is there a workaround or do I need to re-engineer how the Excel sheet works (which I think may be highly tricky given what I am trying to do)
Many thanks
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Hmmm, it's not configurable I'm afraid. Jet sorts that way for consistency. If Navision has a sort key which matches what you are sorting/filtering by then Jet will use that sort key to get the results in sorted order. This is much faster than Jet sorting the data internally, so Jet uses this methods when possible. However, in some cases Jet can't use a Navision key to do the sorting and has to sort the results manually. In this case, Jet's own sorted results need to match the sorted results which would come out of Navision, so code fields are sorted as Navision would sort them. Code fields are a special case in Navision and they have a very different sort order than just text data.
I have been thinking about your problem and there might be a way to fool Jet into treating the field as a text field instead of a code field. I believe if Jet were treating the data as text, then it would be sorted the way Excel sorts data. If you wrap the data as what we call a calculated filter field I believe it will do that. So say your current function looks like this:=NL("Rows","Sales Line",,"+No.","*")
I believe if you change it to look like this, the sort will change to be textual:=NL("Rows","Sales Line",,"+=NF(,""No."")","*")
This will probably also make the function significantly slower to evaluate but I believe it will work. Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Chris,
Jet sorts code fields the same way that the NAV native server sorts them. In code values with mixed letters and numbers, the letters come first, as you have noted. What does your VLOOKUP formula look like? Possibly you could rewrite it as another Jet formula?
Regards,
Hughes -
Jet Reports Historic Posts Thanks for responding - The vlookup is simple but relies on other information pulled out. To try and give an idea, I have a bunch of purchase orders pulled out with the stock coming in on each so I string together the item number and the cumulative quantity coming in on each PO. Then using a vlookup, I look at the cumlative sales lines to identify against each sales order and its position in the cumulaitve sales line "queue" which Purchase order it relies upon to satisfy that row. The vlookup relies on the data being out and I am probably not sufficiently Jet capable to write something which would do the whole thing as part of a Jet operation. Talking internally, there may be a workaround but I was surprised that the two systems sort in apparently different ways which from what you are saying is driven by not so much a Jet way but a Navision way….. is that configurable at all do you know?
-
Jet Reports Historic Posts Thats worked. Great result as thought this could be a show stopper. Thankyou for the help!!
Please sign in to leave a comment.