One of great new functional features of Microsoft Dynamics NAV 2016 is preview posting. It allows you to preview all the entries that would result from posting a document or a general journal.
Preview posting is not a simple thing. If it was, Microsoft would have delivered it years ago. There must be something in particular with NAV 2016 that powers preview posting, so I decided to investigate it and see exactly how it works.
I am not particularly happy with what I found out, but I have also learned some valuable lessons from it. In this post, I’ll share my findings.
Functionally, preview posting looks like this:
- You click “Preview Posting” in a sales or purchase document, or in a general journal.
- The posting process runs.
- You get a page that shows the entries resulting from the posting, navigate-style.
- No trace is left in the database, anywhere, that any posting ever happened.
Technically, on high level, it looks like this:
- Preview posting runs using the same code that processes actual posting.
- After posting completes, all resulting entries are collected from actual tables into temporary ones.
- Temporary tables are shown through a special page.
- Transaction is rolled back.
If you know standard NAV code well, then you must ask an obvious question: how does it do it? Sales and purchase posting routines are full of COMMITs. A quick view at codeunit 80 or 90 shows that COMMITs are not called if PreviewMode is TRUE. Apparently, this resolves the problem of running the whole posting in a single transaction that can then be rolled back in the end.
So – this must be all there is to it?
Unfortunately, it isn’t.
Even though it could have been possible to achieve this with at least two different approaches, preview posting actually uses a new feature, which – after a closer look – seems to have been specifically tailored to power the preview posting. Your guess? Yes – TryFunction.
If you look at codeunit 81 (for example; I’ll use sales posting routine for all examples in this post) you can see that its Code function is turned into a TryFunction. It is called directly from the OnRun trigger, but it’s called with the IF NOT Code THEN construct in the Preview function.
Here’s the Preview function:
It does several things:
- It calls function Start from codeunit 19. More about this later. This is the cornerstone of the concept, and it would be much better if it could have been avoided.
- It sets the PreviewMode to TRUE. This flag is then passed on to codeunit 80, which then makes COMMIT decisions based on it.
- It runs the Code function in the try mode. Apparently, it expects the function call to fail.
- If the codeunit 80 fails with anything but a specific expected error message (let’s call it “preview signal error”) then it throws whichever error came from codeunit 80. This covers for all situations where posting cannot succeed for any valid reason, such as customer being blocked, missing posting setup, posting date out of bounds, you name it.
- If the codeunit 80 fails with the preview signal error, then it means posting actually succeeded, we only got this error because we needed the Code to fail in try mode. Codeunit 80 fails with this error after it posts all the entries, and just before it starts cleaning up data and calling functions irrelevant for the preview functionality. This exits the codeunit, without rolling back anything (because it’s in the try mode):
- It calls the Finish function from codeunit 19. This function collects all entries into temporary tables. Since TryFunctions do not rollback on failure, this means that at this point all generated entries still patiently wait in the database. They are collected, and then shown in the special page.
- Transaction is rolled back through silent abort.
This all looks pretty hunky-dory as long as there are no COMMITs anywhere in the code. A COMMIT during the preview posting would be disastrous. But, how do you make sure there is no COMMIT? Well, codeunit 80 carefully goes around COMMITs if it is in PreviewMode, but can it make sure no COMMIT is called somewhere outside its reach? You could easily sneak a COMMIT or two through customization. Also, thanks to the events feature of NAV 2016 as well as through extensions, objects can pop up out of a blue that subscribe to one of the many events and call COMMIT.
Well, you don’t even have to customize anything – there is (what seems like) a bug in codeunit 80 which results in a COMMIT being called even during preview posting.
Now, this surely is a bug, and I am sure in a cumulative update, this code will read:
Anyway, since this COMMIT is in there, all you need to do is switch Calc. Inv. Discount on, release a sales order, and preview post it, and – voilà:
Now, I haven’t seen this error in my entire NAV career. I knew it existed, and it was there to prevent posting unbalanced transactions into G/L, but seeing it on screen would mean you seriously messed up with your code. Now, in the course of a few days, I see it two times.
But why does this error show? It’s certainly unexpected. Knowing that CONSISTENT is called at a single place in codeunit 12, where it marks transaction as inconsistent as long as it doesn’t balance to 0 (which certainly isn’t the case with PreviewPosting) you may be confused and wonder if there is some other CONSISTENT call. A few seconds of investigation will reveal that there is one – right at the beginning of the Start function in codeunit 19:
Here, it unconditionally marks the transaction as inconsistent, right at the beginning of the whole posting process. Why does it do so?
In C/AL transaction is committed to the database in two situations:
- When code execution completes and control returns to the user.
- When C/AL calls COMMIT.
Unless there was an error, transaction will be committed. However, there is one function in C/AL that changes the committing behavior – it’s the CONSISTENT function. You call CONSISTENT on any record variable, and pass a TRUE or FALSE to it. Just before a transaction is committed to the database, the runtime checks if there are any records which have been marked as inconsistent (having received FALSE from the CONSISTENT call). If there are, transaction is rolled back and execution aborted with the error message you saw above.
You may imagine, codeunit 19 never sets CONSISTENT(TRUE) – it simply leaves the record as inconsistent for the duration of transaction. If there are no COMMITs, the transaction will be aborted either by a legitimate posting error, or by the silent abort at the very end. However, this CONSISTENT(FALSE) at the very beginning makes sure that no commits are accidentally called – if they are, the consistency check will result in failure.
And that’s it. No way you can cause accidental catastrophe by injecting a COMMIT or throwing an error at a wrong place.
If you have read my last post, about the dangers of TryFunction, and if you agree with it (and everyone I spoke to agrees) – then something is wrong. If an error happens inside a TryFunction nothing is rolled back. This behavior is dangerous, and yet – without it – preview posting couldn’t work. If data was rolled back, then nothing would be left in the database for the Finish function in codeunit 19 to collect.
As I said earlier, there are at least two approaches to preview posting, without unnecessary (and utterly confusing for end users) CONSISTENT(FALSE), and without excessive refactoring of posting routines – and I’ll explain each of the two approaches in two separate posts, one on Thursday, and one on Friday this week – so stay tuned.
In the meantime, there is one useful takeaway from this whole analysis: there is a way to make sure that failure in any TryFunction does not create mess in the database. It’s pulling the same CONSISTENT trick that Microsoft pulled in the Preview Posting feature, just on a smaller scale.
Now, what I am going to show is ugly, dirty, and I hate even being forced to blog about that. But with the current state of TryFunction feature, this is the best shot:
The mechanics are simple – at the beginning of the TryFunction, you mark the transaction as inconsistent. In case TryFunction fails, the transaction will remain marked as inconsistent in case you forget to rollback the data yourself. If TryFunction succeeds, then transaction is marked as consistent again, so that transaction can be successfully committed, when time is ripe.
However, as I said – this is ugly. Any COMMIT anywhere in the code will slice this approach to ribbons. And this proves once again that TryFunction behavior – as it currently is – of not rolling back data, is absolutely wrong and that any architecture (including Preview Posting) that depends on it, is inherently bad.
Anyway, I hope you learned a thing or two from this post, as have I while digging through the code.
No animals were harmed during the making of this post, even though a few were quite close to it.
Another great post 🙂 you just keep on going right? 😉
But now I understand your last comment about “This calls for another blog post” :-)”
Excellent, Vjeko, you have proven your point in the best possible way!
This calls for action from MS other than just fixing the unguarded commit. At a minimum, commits should not be allowed while executing a TryFunction (the runtime should raise an error). Also, if a TryFunction updates the database and fails with an error, then the runtime should make the transaction uncommittable to avoid that the caller ignores the error, leading to commit of an inconsistent state.
Just a clarification. The INCONSISTENT function doesn’t mark a record, but the table – globally. That just makes it a bit strange they picked GLEntry table for the Preview function, as this table is also the one used in codeunit 12 to ensure consistency. I would therefore expect the setting from codeunit 12 to overwrite the setting from codeunit 19. I guess the only reason the protection works in your case, is that your COMMIT is before the Cod12.FinishPosting() If you had a COMMIT after FinishPosting, but still within the TryFunction it would be commit’ed. They should have picked a different table for this protection, or designed it differently as you suggest.
PS: I like the INCONSISTENT function. I use it frequently to protect sensitive code. Like web-services you often need to ensure are a single transaction, no matter how somebody else might customize used code afterwards. It was also used like that in codeunit 8703 & 8725 (the old Mobile Document Handlers), to prevent COMMITS during processing of requests from mobile devices.
Peter – when I say “marks record” – I refer to record variable, not to a row in a table. Setting in codeunit 12 indeed overwrites the setting from codeunit 19, and if you could sneak a COMMIT just after the last CONSISTENT call you could wreak havoc, that’s true. By the way, there is no INCONSISTENT function 😉 It’s called CONSISTENT. And my position is that they should have designed both the preview posting differently AND TryFunction behavior (which is broken beyond comprehension).
Thanks Vjeko 🙂
Will just add, that the structure with CONSISTENT(FALSE);CONSISTENT(TRUE); in the TryFunction doesn’t make it secure either. If NAV is buffering inserts for a bulk insert, then it might not encounter the error until some code triggers the actual inserts after the TryFunction.
I also find it odd that they are manually skipping Commits and using the INCONSISTENT trick to ensure it works (one extra tip from this that we have been using, give the table that you set INCONSISTENT on a caption like “COMMIT DISALLOWED” for a slightly nicer error message)…
They have shown with their Test CU’s that they can use Nested SQL Transactions and rollback even after Commits – I thought the natural progression of this would be to make it available to the rest of the code. Maybe a suggestion from an MVP would help 😉
Also, if you are putting suggestions onto MS Connect can you give us the link so we can up vote them?
Nikolai – you are spoiling my refactoring suggestion for Friday 😛 Hush, hush!
I just must disagree with “If data was rolled back, then nothing would be left in the database for the Finish function in codeunit 19 to collect.” Because if you collect the data into temp tables, even after error the temp tables will have the data inside them (temp tables are not part of the transaction). We are using this trick for last 5 years to do same functionality like this preview in our addon and it is working. It means, you can do the preview even without the TryFunction (I am using just extra codeunit for it).
Kamil, I must disagree with you here 🙂 It’s true that rollback doesn’t clean any temp tables, that’s a known fact. But for data to be in temp tables in the first place, it would have to be put into temp tables during posting process, which would require a major refactoring of the posting procedures. It’s the Finish function that collects data AFTER the signal error, and it collects them from actual tables and puts them into temporary. Since everything is collected into temp tables AFTER signal error, it’s the TryFunction “feature” of not rolling back anything that enables retaining data in the database. See my point?
This series is a gem. Thank you!
You’re welcome!
Pingback: Fixing Preview Posting: Part 2 - Microsoft Dynamics NAV Community
Hi all, Posting Preview is Russian functionality implemented in NAV 2009 R2 about 5 or 6 years ago. And it works fine in Russia. I didn`t see NAV 2016 yet, but I know that code and it looks like just migrated to W1. That is why try functions not used, even if they works fine. Code is older than NAV 2013 released)
And about bugs in CU 19. There is no CU 19 in Russia at all. So let`s ready to collect bugs because of some functionality that never used in Russia.
Okay, now I see where it’s coming from 🙂 Well, without looking into Russian localization, I assume they used exactly what I suggested in my Fixing Preview Posting: Part 1 post.
sorry about bugs in CU 19. But there is no invoice discounts postings in Russia too. (Just not used)
Hi Vjeko,
Really Nice Blog !
I just tested tryfunction after gone through ur blog and found one bug in that…as you mention when an error occurred it should exit from tryfunction with return value as false but while inserting any record using insert command in tryfunction (when same record already exists in table) its not considering as an error.. and therby it continues further….returns True if found no other explicit error msg or any testfield.
Can u correct me whether my understanding is correct?
I suspect it’s a bug.
Thanks in advance
Krrish
Could be a bug. But you shouldn’t be doing any database writes from a try function anyway.
Microsoft Outstanding Bug Report – not yet fixed in NAV2016 CU3 – ID 377725 – Using the “Preview Posting” feature from a General Journal with insufficient settings or missing information (e.g. External Document No.) causing Dynamics NAV to create G/L Ledger Entries, VAT Entries, etc. but no G/L Register created and not deleting the posted Gen. Journal Lines.
Using the “Preview Posting” feature from a Purchase Invoice with insufficient settings or missing information (e.g. External Document No.) causing Dynamics NAV to create G/L Ledger Entries, VAT Entries, etc. but no G/L Register.
Recommendation:
Do not use Posting Preview until this issue is fixed.
https://mbs.microsoft.com/partnersource/uk/support/hot-topics/msdnavreportedissuesnyr
Thanks for your post Vjeko – TryFunction – AS IT STANDS – has a high risk of misuse – and thus risk of causing future problems. (and it looks like Microsoft tripped over their own wires here too NAV bug ID 377725 issue with Preview Posting not fixed yet in CU3 … which I hope emphasises the importance of amending this…)
Existing NAV C/SIDE code is written with an underlying assumption that it will roll back on error!!! The TryFunction is dangerous because it allows existing NAV code to be called and to inadvertently commit unwanted partial results – contrary to this previously safe working assumption.
However the TryFunction is already in service. Microsoft have already used the functionality as a part of new features – and have tried themselves to follow the safe usage pattern – with explicit ERROR… (But it’s dangerous)
IF (NOT TryFunctionCode()) THEN BEGIN
SomeOtherStuff();
ERROR(”);
END;
As well observed by vjeko. This pattern works OK as long as SomeOtherStuff() does not do any explicit COMMIT’s.
To preserve this pattern intact and prevent unwanted issues presents a challenge. Fortunately a possible solution exists for the Microsoft NAV team – which I will suggest here for consideration to adopt as part of an update NAV2016 CU4 – without breaking anything already in place.
As a part of return from a TryFunction with result FALSE, NAV must check for an active write transaction and if active, mark the current database transaction as UNSAFE TO COMMIT (SQL Server can do this – if Microsoft fix CU4 – see a possible tech suggestion below)
Any attempt to commit work after that point – either explicit or implied – would fail. This approach allows the uncommitted results to still be read and inspected (and copied to temp tables if needed) – but would prevent any unwanted partial commit – at the database level.
One way to achieve this in TSQL is to force the XACT_STATE to be -1. When XACT_STATE is -1 any attempt to COMMIT the active transaction fails. This could be achieved indirectly by the following simple SQL issued within the active transaction by NAV. This forces the XACT_STATE to -1 by causing a trapped conversion error. Any subsequent COMMIT attempt – however issued – is then doomed… (This would need to be issued by NAV under the hood… from exiting a TryFunction call with trapped result FALSE)
IF @@TRANCOUNT > 0 begin
begin try
select convert (integer, ‘x’); –always fails with XACT_STATE = -1
end try
begin catch
end catch
end
This approach would allow existing NAV code to work unmodified – (using the safe usage pattern with ERROR), but would prevent backdoor problems by partial explicit or implied COMMITS.