Programming NAV to update SalesHeader when SalesLine is modified

Not sure if this is the right subforum for this… I just joined this forum but I understand you guys are the original and best, so I’m hoping someone here knows how to help me!

We are using NAV 2009 R2 as our back-office software, and are doing a data integration with Dynamics CRM for our salespeople to use. I am currently setting up the integration processes and have gotten a pretty good data flow between the two.

However, one specific problem I ran into… the Scribe software is set to only monitor inserts/updates of the SalesHeader entity. This is fine 99% of the time, as if anybody needs to manually create an order in the back-office, it will register as being created/modified and flow over to CRM.

But one thing I noticed… if we edit a sales line (rather than inserting or deleting one)… a typical scenario, let’s say we typed the price wrong and need to correct it before we sent an order confirmation… it will not notice anything in Scribe. My best guess is that modifying a SalesLine does not cause the header to be “modified”, so the program has no way of knowing we did anything to it.

It would be much more complicated to monitor SalesLines, so I would like to keep our integration monitors the way they are. However, I know there’s a field for “last modified” in NAV entries… what I’m looking to do is somehow script our NAV server so that if a sales line is modified, it also sets the header as modified.

I have a feeling this is easy to do, but I know nothing about NAV programming so I would like some step-by-step instructions.

Thanks in advance!

Take a look at the OnModify trigger in the Sales Line table (37). This is where you’d probably want to add your new code. You would want to GET the Sales Header using values of its primary key based on values in the Sales Line. The PK for the Sales Header table is “document type”, “no.”; the values of this key come from Sales Line fields “document type” and “Document No.”. The easiest way to do this is using a call to the function GetSalesHeader, which is defined in Sales Line table.

From there, just enter a command to set the value of your Last Modified field to today, then call MODIFY(TRUE) on the SalesHeader. I’d check the definition of the Sales Header table first though, just to be sure you have a Last Modified field there. I’m not sure that NAV puts a field like that in this particular table by default.

Hmm, you’re right… we don’t seem to have a Last Modified field. I swear, Scribe has a way of knowing if a sales header was modified.

Can that MODIFY(TRUE) thing be used on its own? That might be enough. Thing is, I’m really confused as to how I’d even do this… if I go into Object Designer and click Design for either table, I just see every single field… now what? I’m a complete beginner at NAV customization.

You’ll need access to the C/AL code that sits behind all of the fields and the table definition. If you press F9 while you have the table open in design mode, you should expect to see the code triggers I’m talking about. This of course assumes that you have the appropriate licenses. If nothing happens when you press F9, then you either don’t have the license needed to view that section of the application, or your login credentials have not been given sufficient privelege. The second issue may be addressed by granting SUPER credentials to your login. The first issue would require an investment in additional licenses.

Alternatively, and again based on your license, you may be able to affect the same change by making modifications in the appropriate Sales subform. You may even need to resort to some contrivances like copying the subform into a new form object in the 50K range, which should afford access to C/AL code. You’d then want to modify the parent Order/Invoice form to link to the new subform.

I would probably start by determining the precise conditions that cause Scribe to process changes to the Sales Header. Once we know that with certainty, you’ll be in a better position to craft the solution.

First off, yes we have a developer’s license. I can access the coding just fine.

I’m just afraid of breaking anything. I did modify a table before, the ScribeProducts custom table (it was imported using a .fob file, but of course their table didn’t include all the fields we needed)

Suppose I make a custom field called SalesLineModified and make it a DATETIME value… would I then be able to have something in the coding put the current time (I know in SQL and Scribe it’s called NOW()) in that field?

As far as Scribe, I’m pretty sure anything that is modified will do it. It uses the NAS instance to monitor changes, and as far as I can tell modifying any of the fields will trigger it.

For anyone who touches a keyboard for a living, I’ve always advocated this one simple admonition … It’s time for a coffee break if the last thought you had before beginning to type was anything like “I wonder what happens when I …”.

All rules have exceptions though, and this may be one. To address your concern over making mistakes, you would want to create a rollback copy of the Sales Line and Sales Header tables before you start to make changes. Then create your new field in the Header record. I’d probably opt for a Date data type over DateTime since the early incarnations of the DateTime data type were a bit unstable. Then you’ll want to add code in the OnModify trigger of the Sales Line table that would look something like this …

GetSalesHeader;
SalesHeader.[your new field name] := today;
SalesHeader.Modify;

Once you’ve made these changes, test your work by making changes to any of the Sales Line records using any of the Sales document forms (Sales Quote, Sales Order or Sales Invoice) and watch for your Scribe application to update the change log.

If all of that works the way you expect, you may want to add similar code to the OnInsert trigger of the Sales Line, depending on your business requirements.

That should do it.

Thank you, that works! At least the coding does. Scribe isn’t picking up the new field yet, because we need to get everyone off our server and reboot the NAS server for Scribe to notice the changes.

One concern I have, since this is just a date, what would happen if you modified/added sales lines more than once within a day? That wouldn’t really change the data, would it? Or does the Date field actually store a time as well? (I know some of them, like Requested Delivery Date, actually store a time - if you put in a 0, it turns into midnight in 1753)

There’s surely no harm in adding a Time field to the process. That would resolve any questions or issues related to multiple changes in one day. You’ll still need to determine what triggers the Scribe updates though.

Cheers,

George

Well, isn’t a Time field the same as DateTime? Or does that ONLY store a time and not a date?

It depends on whether you’re in native NAV database or SQL Server. From your observation about how the 0 date value defaults to 1753 as stored value, I’m assuming you’re in SQL Server.

I’m not so much concerned about what data gets stored as I am about being able to reliably retrieve it; that’s where my avoidance of the DateTime data type comes from. If you don’t share that aversion then by all means feel free to create a single field using that data type.

Yep, I was checking the entries in SQL Server.

So to confirm, “Time” is the same as “DateTime”?

You’ll notice that I carefully (but not carefully enough it seems) tried to bypass the fact that I don’t know the answer to that specific question. It’s a great academic exercise though and probably a good time to find the answer. I will let you know what I’m able to find. I wouldn’t let the absence of information on that question deter you from the project at hand however (again, my opinion. Your mileage may vary)

Actually, a lot of those fields are set to “Date”, but yet in SQL show a time of 12:00:00, so perhaps it can store time as well?

And knowing that, can you think of a way to test your theory? I have a method in mind that involves create new date values every 5 minutes or so to see whether the time value in SQL changes off of 12:00:00. If it doesn’t change, maybe the premise is incorrect?

Yeah, I could certainly just check the SQL server after updating it and see what it shows.

Alright, so looking in the SQL database, when I modify the sales line, it assigns a value of today’s date with 00:00:00.0000 as the time.

So I guess technically a date field can store a time, but since it’s only a date type, it doesn’t.

I mayaswell convert it to type Time, but would using “today” still work? Or is the equivalent called “now”? I don’t want to break the server with invalid syntaxes [A]

So, as you’ve seen from your experiments, SQL stores Date values in a Date field, but also displays a blank frame for Time when you view the data from the SQL side. This suggests that there is actually no Time value being stored when you save a Date data type from within the NAV code. I believe you will find similar results with the Time data type; you’ll see a blank Date frame but no actual Date values will be stored in the Time data type. That was the basic motivation behind the development of the DateTime data type.

So, from within NAV, assigning values to these two data types is very straight-forward. Assign date values (TODAY, WORKDATE, date literals like 01022013D) to Date data types, and assign time values to Time data types. If you stick with that convention, you should be fine.

Cheers!

Well, I suppose it doesn’t need a date field, as what are the odds of editing it exactly 24 hours from the previous time? The sole purpose of this is just to have some data modified, so I think I will just change it to a Time type.

What is the convention for “right now”? Obviously I can’t use “today” as that’s a date, not a time. Is “now” a valid term?

TIME

I will try it, thanks.

–EDIT–

Phew, got it. TIME didn’t work, and I almost broke the server for everyone else because of that code I inserted.

Thankfully I figured it out. Setting it to DATETIME, then using CURRENTDATETIME did the trick!