I need to do a comparison ( looking for mismatches) between the Source No. in GL Entry and the actual Vendor in Vendor Ledger ( Part of trying to figure out just how users have managed this and how often )
D3 ||"NL","AllUnique","Vendor Ledger Entry","=NF(""Vendor No."")&NF(""Transaction No."")","Document Type","Invoice","","","","","","","","","","","","","","","","","",""
D4 ||"NL","AllUnique","G/L Entry","=NF(""Source No."")&NF(""Transaction No."")","Source Type","Vendor","Document Type","Invoice","","","","","","","","","","","","","","","",""
D7 =NP("Join",NP("Difference",$D$4,$D$3),"|") Generates Value
debug comes up with record key is corrupt or invalid ( I'm guessing its my attempt to bring back two fields as one)
Likely something simple I've overlooked but …..
Oh Jet 2011 Essentials Navision 4 SP3 Excel 2007
Date
Votes
3 comments
-
Jet Reports Historic Posts Hi Van,
"Record key is corrupt" is a strange error in this case. The strange thing here to me is that you are returning the combination of Vendor No. and Transaction No. So if you have Vendor A with transaction 123, then the number you will get is "A123" which is not going to be a valid filter for anything I don't think. Of course this is not a record key or field cache, so if you try to use an NF against it, Jet will tell you that the record key is invalid, which may be what you are trying to do. Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Van,
"Record key is corrupt" is a strange error in this case. The strange thing here to me is that you are returning the combination of Vendor No. and Transaction No. So if you have Vendor A with transaction 123, then the number you will get is "A123" which is not going to be a valid filter for anything I don't think. Of course this is not a record key or field cache, so if you try to use an NF against it, Jet will tell you that the record key is invalid, which may be what you are trying to do. Does this help?
Regards,
Hughes
Okay here is reasoning
The transaction no in GL entry should be the exactly the same Transaction Number that is in the Vendor Ledger Entry file Since the Source No in GL should be the Vendor No based on fact that I'm filtering on Purchase I shoudl get same data
So the difference I'm looking for is where the combination Vendor+Transaction <> SourceNo ( ie Vendor No.) + Transaction
Once I get data I can parse the results for Transaction Number and figure out how many Bad GL records there are and exactly which ones they are -
Jet Reports Historic Posts Having read my response lets make this a bit more generic
I have two fields in two different tables that should be exact matches
I want to create a sort on each table where the two fields are concatenated into one (This is part I'm not sure is working)
Then I want to do a comparison so that if there are any cases where the concatenated results don't match I can examine those records
In the specific I'm working om The transaction number in Vendor Ledger Entry should equal the transaction Number in GL Entry and the Source No from GL Entry should equal the Vendor No from Vendor Ledger. I.E> Unless I misunderstand one link/relationship in Navision is that the path back to the originating Source Entry from GL Entry is the transaction Number plus the source type ( Purchase Cash receipts etc)
Please sign in to leave a comment.