Hi there,
i want to get "rows" from the "sales line"-table, but only, if there is no entry in the "Warehouse Shipment Line"-table for the sales-line.
the filter for the "Warehouse Shipment Line"-entry is "source type"="37";"Source Document"="order";"Source No."=is the "sales line"-"document-no.";"Source Line No."=is the "sales-line"-"line-no.".
What i did, is geting rows from every "sales-line", and after that, get a Count into the "warehouseshipment-line" with the filter from above.
this works for me, but there might be another way (but after that football-game last night i'm not on the right way right now ;) )
My question:
How can i get only the sales-lines, which have no entry in the "warehouse-shipment line "-table , but using the "NL-"Rows"-function ?
maybe using NP("difference")..? but how?
regards
jetsetter
Date
Votes
11 comments
-
Jet Reports Historic Posts It should be possible to use the Count-function as a calculated parameter within the NL(Rows).
But that means juggling with quotes :ugeek: , so is it possible to post the NL(Rows) and Count function you are using?
Maybe there is a way to combine the two.
rmw -
Jet Reports Historic Posts hi,
here is an short example für the NL(row) and NL(Count) for a better understanding… -
Jet Reports Historic Posts This will do the trick (I hope):
=NL("Rows"; "Sales Line"; ; "Document Type";"Order"; "Type";"Item"; "Outstanding Quantity";">0"; "=NL(""Count"";""Warehouse Shipment Line"";;""Source Type"";""37"";""Source Document"";""Sales Order"";""Source No."";""=NF(;""""Document No."""")"";""Source Line No."";""=NF(;""""Line No.""""))""";"0" )
I am using the NL(Count) as a filter field and zero as the filter value
But within the NL(Count) you can use values from the rows you are replicating, by using a NF without a key.
Trouble is that Jet and Excel need their parameters as text, so it is a bit tricky to get the quotes right.
A double quote within a text string equals a quote without ending the string.
So everything within the NL(Count) needs to be double quoted and because the internal use of NF needs quotes too, you need double double quotes there :?
HTH
rmw -
Jet Reports Historic Posts :lol: Quote double double Quote :lol:
sounds like a Morse Code ;)
Thank you very much,
i will give it a try, but not today ….
regards
jetsetter -
Jet Reports Historic Posts unfortunately, it doesn't work…..but i don't get an error, just no data….
i tried to check the quotes and double quotes, for me they a OK…
can anyone have a look and give me an advice?
thx
jetsetter -
Jet Reports Historic Posts What have you tried replacing the Count function with a Filter function? That seems more logical to me
-
Jet Reports Historic Posts Hi Heather,
thanks for your replay.
Just changing "count" to "filter" doesn't make a difference….(and no sense to me)
Which "Filter"-Function would you suggest?
regards
jetsetter -
Jet Reports Historic Posts okay - perhaps I'm not thinking clearly (still waiting for the caffeine to kick in this morning) :)
and I certainly didn't fully explain myself last week…
I was suggesting using NL(FILTER) in place of NL(COUNT) for embedding into your ROWS formula.
Here is the logic as I'm seeing it with the COUNT embedded in the ROWS formula:
Document Type = Order
Type = Item
Outstanding Quantity > 0
but the NL Count isn't returning a value found in the table to filter against (it's not a field code), it's returning a count of lines that match the other parameters, so I can't see how Jet can properly interpret that as a field code in the Rows command. (Or am I missing something here?)
an NL Filter, returning the order numbers that have values into the warehouse shipment line table, and used as an excluding (rather than including) filter might do the trick. But honestly, I'm not sure that wouldn't be such a complicated formula and not be worth the time.
Unless there is some other table you can find that could give you the rows you need - to at least provide the Order numbers?
I've struggled with ways to include large sets of data that are not included in other tables, but I've found that for me, using the array formulas (difference, union, intersect) slows down performance so much that it isn't worth doing (internet pipeline issues). Generally I end up using a helper sheet, pull a quick rows report that has all of the values I don't want included, and then use a JOIN command to build myself an excluding filter - but that has it's drawbacks also, because I can end up with a filter that's just too huge (and a lines table is likely to give you exactly that problem).
Otherwise, I think you are right, and NP(Difference) is your best solution:
and I think this is the formula you would want for that.
=NL("Rows";NP("Difference";NL("Filter";"Sales Line";"Document Type";"Order";"Type";"Item";"Outstanding Quantity";"=>0");NL("Filter";"Warehouse Shipment Line";"Source No.";""Source Type";"37";"Source Document";"Sales Order"))) -
Jet Reports Historic Posts Hi,
I think using the count as a CalcFilter field would work, but you got the quotes a little wrong around the nested NF functions. Try something like this:=NL("Rows";"Sales Line";;"Document Type";"Order";"Type";"Item";"Outstanding Quantity";">0";"=NL(""Count"";""Warehouse Shipment Line"";;""Source Type"";""37"";""Source Document"";""Sales Order"";""Source No."";NF(;""Document No."");""Source Line No."";NF(;""Line No.""))";"0")
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts But that means juggling with quotes
Seems like Hughes is a better jugler than me ;)
rmw -
Jet Reports Historic Posts Bingo ! :D
Thank you, Hughes!
yes, that works for me….
Thank you, rmw, for giving the right direction…. ;)
regards
jetsetter
Please sign in to leave a comment.