0

Sort by calculated sum from seperate tables - fields with special characters not being sorted

I have created a report that combines contract costs based on customer rather than contract so I've had to combine the 'customer' table to get the list of customers, the 'contract' table that lists who the customer is on each contract and the 'contract ledger entry' table which contains all the cost transactions relating to all the contracts.

Cell C13 contains the following

=NL("Rows","Customer","No.",$F$4,"*","Company=",$D$9)

Cell F4 contains the following

="+=-NL(""Sum"",""Contract Ledger Entry"",""Actual Line Amount"",""Contract No."",NL(""Filter"",""Contract"",""Contract No."",""Bill-to Customer"",NF(,""No."")),""Contract Costhead"",""<>SAL001"",""Posting Date"",$S$4)"

then the rest of the report is relatively simple.

E13 contains

=NF($C13,"No.")

F13 contains

=NL("First","Customer","Name","No.","@@"&$E13,"Company=",$D$9)

G13 contains

=-NL("Sum","Contract Ledger Entry","Actual Line Amount","Contract No.",NL("Filter","Contract","Contract No.","Bill-to Customer","@@"&$E13,"Company=",$D$9),"Contract Costhead",G$2,"Posting Date",$S$4,"Company=",$D$9)

 

Most of the report works okay in that it sorts it by total costs (which I'm hoping to change to total profit % in future). However, when a customer no. contains an ampersand (ie C&S001) or where the total costs are zero, these are not sorted in the report.

I tried playing around with using "@@"& before the NF(,""No."")) in cell F4 but I either got an error or the sort stopped working completely!

Hope I have explained the above clearly enough. Can anyone help me with a solution to include customer no.'s with special characters in my sort?

0 comments

Please sign in to leave a comment.