I am trying to pull data from the G/L entry table, but need it grouped by the Gen.Prod.Posting Group that is in the Value Entry table.
The syntax looks correct, but the results are not correct. It is not grouping and summing by the selected Gen.Prod. Posting group.
=NL("Sum","G/L Entry","Amount","G/L Account No.","99999","Posting Date","010111..013111","Global Dimension 1 Code","99","Link=","Value Entry","Document No.","=Document No.","Gen. Prod. Posting Group",$D11,"Posting Date","010111..013111","Global Dimension 1 Code","99")
Can someone tell me what is incorrect? Any help would be appreicated.
Date
Votes
7 comments
-
Jet Reports Historic Posts Wouldnt the Gen. Prod posting group be the same in both the GL Entry and Value Entry table?
The field is already in the GL Entry table, do you really still need the Link=? -
Jet Reports Historic Posts Yes I do, because the field is blank in the g/l entry table
-
Jet Reports Historic Posts The formula looks correct, I tested it in Cronus and even added a Document No. filter and the results give the sum for that document no.
$-4,680.00
=NL("Sum","G/L Entry","Amount","G/L Account No.","6130","Posting Date","010111..310111","Global Dimension 1 Code","SALES","Document No.","103006","Link=","Value Entry","Document No.","=Document No.","Gen. Prod. Posting Group","RETAIL","Posting Date","010111..310111","Global Dimension 1 Code","SALES","Document No.","103006")
If you drill down from the value it returns can you see the filters or entry numbers that it is filtering by. Check the filters in the Value entry table as well, i.e go and manually apply them to the table and make sure that it is returning the same entries.
Cant see why it shouldn't work -
Jet Reports Historic Posts When I list the detail, it shows the sum for two Gen.Prod Posting Groups. The document number is the same, but I could have multiple posting groups within that document. However, I thought that if I selected the desired posting group, then I should only get the sum for that group.
-
Jet Reports Historic Posts Hi!
So first of all, meemz, the problem with your formula is that you are trying to filter for the Document No. in the Value Entry twice in your formula. You are trying to use Document No. first as a linking field and then you are trying to filter for a Document No. of 103006. This will not work. Document No. cannot be a linking field and have another filter value. If you only want to see Document No. of 103006, then you should just filter for that in the G/L Entry table and forget linking to the Value Entry.
Secondly, pierce, what your formula is actually doing is getting the sum of values from the amount field of the G/L Entry for every record matching your filters where there exists a record on the Value entry with the same document number and which matches the filters on the value entry table. So it should only be summing g/l entries where there is a value entry with a matching document number and the general product posting group in cell D11.
Another thing to note is that Link= will only be evaluated when you actually run the report (click the Report button on the Jet menu/ribbon). If you are evaluating the NL(Sum) in design mode, the Link= will be ignored because formulas are optimized for faster execution in design mode.
To take a look at what is going on you could try the following formulas:
C4: =NL("Rows","G/L Entry",,"G/L Account No.","99999","Posting Date","010111..013111","Global Dimension 1 Code","99","Link=","Value Entry","Document No.","=Document No.","Gen. Prod. Posting Group",$D$11,"Posting Date","010111..013111","Global Dimension 1 Code","99")
D4: =NF(C4,"Entry No.")
E4: =NF(C4,"Amount")
F4: =NF(C4,"Document No.")
G4: =NL(,"Value Entry",,"Document No.",F4,"Gen. Prod. Posting Group","$D$11","Posting Date","010111..310111","Global Dimension 1 Code","99")
H4: =NF(G4,"Document No.")
I4: =NF(G4,"Gen. Prod. Posting Group")
With these formulas, you can see the results being brought back by the original formula and then for each record brought back you can see what the entry number, amount and document number and what record is being found in the Value Entry for that Document number and product posting group. Does this help you figure out what is going on?
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
This is what I see, I have Gen.Prod Posting Grp AA & BB using the same document number in the Value Entry.
Using your setup, I see that the Jet report is using Entry number 1111 twice, once for Grp AA and once for Grp BB.
The correct GRP should be BB and not AA.
I don't know why this is happening. Do you have any other items that I should check? -
Jet Reports Historic Posts So let me see if I understand you correctly. You're saying that there is a record in the Value Entry table with Document No. of 1111 and Gen. Prod Posting Group of AA as well as an entry for Document No. of 1111 and Gen. Prod. Posting Group of BB, is that correct? You're trying to filter for Gen. Prod. Posting Group BB but since a Document No. of 1111 exists for both BB and AA, you are getting some G/L Entries being included that you don't want, right?
You are linking the G/L Entry table and the Value Entry tables solely by Document No. Do you also need to link them by Document Type? Or is there some other linking field that you're missing that would eliminate the issue? It sounds like you're not linking by all the fields you need to ensure that you get the unique G/L Entries that you want.
Regards,
Hughes
Please sign in to leave a comment.