I am trying to develop a report that sorts the purch. inv. line table by the Vendor Name from the linked Purch. Inv. Header table. I have done some searching and it looks like you can't sort based on a linked table but there was a formula included on one response to sort based on an NL function (see copied text below). I cannot get the NL function to work appropriately in my spreadsheet. Can anyone help?
Code: Select all=NL("Rows=5","G/L Entry","Global Dimension 2 Code","Posting Date",Options!$C$22,"Global Dimension 2 Code",$F$23,"G/L Account No.",$F$25,"Global Dimension 1 Code","DIVPREP","+=NL(,""G/L Account"",""Name"",""No."",NF(,""G/L Account No.""))","*")
Thanks,
Joy
Date
Votes
4 comments
-
Jet Reports Historic Posts Hi Joy,
I didn't find anything like the formula you posted in the workbook you attached, so I'm not sure which one you actually want to fix. The problem with the formula you posted is that each Global Dimension 2 Code in the G/L Entry is going to potentially have multiple entries with different G/L accounts. So I'm not sure exactly how you would want it to be sorted by the account name when there are multiple different account names for each Global Dimension 2 Code. Does that make sense? Or are you trying to sort one of the formulas in the attached workbook which is not coming from the G/L Entry at all?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
I think I just confused things. I tried to convert the formula in my message to sort the Purch. Inv. Line table by Vendor Name field from the Purch. Inv. Header table. The formula in my spreadsheet was:
=NL("rows","purch. inv. line",,"No.",$F$11,"Document No.",$F$10,"Store Code",$F$8,"Posting Date",$F$7,NL(,"purch. Inv. Header","vendor name","no.",nf(,"vendor no.")),"*","Link=","Purch. Inv. Header","No.","=Document No.","Document Date",$F$9)
Because the formula below does not work:
=NL("rows","purch. inv. line",,"No.",$F$11,"Document No.",$F$10,"Store Code",$F$8,"Posting Date",$F$7,'Link=","Purch. Inv. Header","No.","=Document No.","Document Date",$F$9,"+vendor name","*")
Does that make more sense? -
Jet Reports Historic Posts Joy,
Okay, that does make more sense. Try something like this:=NL("rows","purch. inv. line",,"No.",$F$11,"Document No.",$F$10,"Store Code",$F$8,"Posting Date",$F$7,"+=NL(,""purch. Inv. Header"",""vendor name"",""no."",nf(,""vendor no.""))","*","Link=","Purch. Inv. Header","No.","=Document No.","Document Date",$F$9)
Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
I tried the equation you provided both with the quotation marks and without. The result continues to be #VALUE. Without the quotation marks, I receive an error that says empty filter not allowed and with the quotation marks it says it is an invalid field. From debugging, it looks like the NF(,"vendor no.") is the empty filter. Am I missing something? It seems odd to have an NF function without a key but I would end up with a circular reference because the NF equation is in my key. I have attached the worksheet if you would like to review it.
Thanks,
Joy
Please sign in to leave a comment.