Highlight Sales Order Lines by Colour for qty < Inventory

shogan@mila.ie

Hi everyone,

I am attempting to highlight Sales Lines on a Sales Order where if the 'Quantity' of the item on the order is less than the 'Inventory' ('Quantity on Hand' for you US folks), that the line is highlighted in red.

What I have found out so far is that I need code for each of the field's OnFormat trigger, as it is not possible to highlight records specifically, per se, which is OK, (albeit a little clunky).

I am attempting to make the changes to Form 46 Sales Order Subform. I know this is just a filter on the Sales Lines table for Document Type = 'Order', but for now I am content with just highlighting lines on the Sales Order for now.


So, starting with the Sales Line 'Quantity' field, here is my code:

(Note: 'recItem' is a Global Record Variable, of type Item)

Quantity - OnFormat(VAR Text : Text[1024];)


recItem.RESET;
recItem.SETCURRENTKEY("No."); // Not sure if I need this, as RESET should do this, right?


IF Quantity > recItem.Inventory 0 THEN  // Should also test for [Type:'Item' (not sure how - help!)] AND ['Qty. to Ship' > 0] AND [Item exists in the Item table, VALIDATE? GET?]
BEGIN

    recItem.GET("No."); // Can this be used to verify an Item on the Sales Line is in the Item table? Just error checking...

    IF Quantity > recItem.Inventory THEN
      BEGIN

        CurrForm.Quantity.UPDATEFORECOLOR(255); // Red
        CurrForm.Quantity.UPDATEFONTBOLD(TRUE); // Bold
    
      END 

    ELSE 

      BEGIN

        CurrForm.Quantity.UPDATEFORECOLOR(0); // Black
        CurrForm.Quantity.UPDATEFONTBOLD(FALSE); // Normal

      END;

END;

What is happening is that all quantity figures are being highlighted where > 0. It is almost like recItem.Inventory is evaluating to 0, thus the true part of the IF statement is ALWAYS being executed for quantities greater than zero.

Also, does anyone know if the OnFormat trigger is fired when the Quantity (or 'Qty. to Ship') field is manually updated, or can it be fired from the OnUpdate trigger? Because when entering a new Sales Line on the order, the line won't be highlighted until the 'Quantity' field has been entered. The same holds true when changing the 'Quantity' field to a higher quantity that is greater than 'Inventory' for that item.

I have looked at code in our system and in "Programming Microsoft Dynamics NAV" (Studebaker), but I cannot find where you can have more than one condition in an IF statement... would be useful for the tests I'd like to add to the IF statement (in the comments of the code above).

Trying really hard to get into coding NAV without too much diversion away from standard functionality. But in this case if it means that we gain productivity, then I think a modification like this is light and beneficial.

Regards,
Stephen

MBerger

I am guessing you are just beginning in coding in NAV, because i see you are missing the basics: you need to GET the item, the use CALCFIELDS to have the system calculate the inventory ( it's a flowfield, and don't forget to set any flowfilters, like "location filter" ).

Also: dont put this code in every onformat trigger, you only need to calculate it once for each record. Put the code in the OnAfterGetRecor trigger of the form, and have it set a global boolean which you can then use in the OnFormat triggers

OnAfterGetRecord :

MarkRecord := false ;
if (type = type::item) and ("Qty. to ship" > 0) then
  begin
    Item.get("No.") ;
    //Set any desiered flowfilters here
    Item.calcfields(Inventory) ;
    if quantity > Item.Inventory then
      MarkRecord := true ;
  end ;

OnFormat :

if markrecord then
  currform."quantity".UpdateForecolor(255) ;

shogan@mila.ie

I had a feeling that FlowFields / FlowFilters would come up - I know Inventory is a FlowField, but thanks for pointing that out to me.

I am not worried about FlowFilters for Inventory, as I want the lines to highlight based on the real-time current value of this field. What other FlowFilters would you suggest? Taking your example, how could I set Location := '' (we have a "null" location for standard stock)?

Expanding the code to take other fields into consideration and at the user's request, here is my code now:

bMarkRecord := FALSE;
CLEAR(decEWSThreshold);
CLEAR(decSalesInventoryAvailable);

IF (Type = Type::Item) AND (Quantity > 0) AND ("Qty. to Ship" > 0) THEN BEGIN

    recItem.GET("No.");
    
    // Set flowfilters here if necessary.

    recItem.CALCFIELDS(Inventory);

    decEWSThreshold := 1.5 * recItem."EWS (Estimated Weekly Sales)"; // 150% of Item's EWS
    decSalesInventoryAvailable := recItem.Inventory - recItem."Qty. on Sales Order" - "Qty. to Ship";
    
    IF (decEWSThreshold > decSalesInventoryAvailable) AND
      ((recItem.Category = 'A') OR (recItem.Category = 'B') OR (recItem.Category = 'N') OR (recItem.Category = 'N')) THEN
         bMarkRecord := TRUE;

END; //IF

Works perfectly. Problem solved and thank you, MBErger.

shogan@mila.ie

I think I have it...

recItem.SETFILTER("Location Filter", '');

Please tell me I am right

MBerger

shogan@mila.ie wrote:

I think I have it...

recItem.SETFILTER("Location Filter", '');

Please tell me I am right

That is indeed how you set a flowfilter. Regarding which ( if any ) flowfilters to set : i don't know your setup or requirements, so it is impossible for me to tell you what to do there. i DO encourage you to set them though, even if they are not used AT THE MOMENT ! ( One of the biggest mistakes when coding in NAV is "we don't use that"...... there is one very crucial word missing at the end of that : "now" ! I always try to prepare my code for possible future changes as much as possible.)

And a tip :

  .... RecItem.Category in [ 'A','B','N' ].....

is much easier to read and maintain. I would also check that condition BEFORE the Calcfields, for performance reasons.

If the field "Category" in the item table ( it's a custom field, isn't it ? ) has a lookup, i would also suggest to add a boolean to that table, so you can remove the hard coded values completely. If you do that, you can add or remove categories that need to be colored without having to change any code.

Categories.get(Item.Category) ;
if not Categories."Mark Records" then
  exit ;

shogan@mila.ie

I see where you are coming from on the performance end...

bMarkRecord := FALSE;
CLEAR(recItem);
CLEAR(decEWSThreshold);
CLEAR(decSalesInventoryAvailable);


IF (Type = Type::Item) AND (Quantity > 0) AND ("Qty. to Ship" > 0) THEN BEGIN

    recItem.GET("No.");

    IF recItem.Category IN ['A','B','N','S'] THEN BEGIN
        
      // Set flowfilters.
      recItem.SETFILTER("Location Filter", '');
      recItem.CALCFIELDS(Inventory);

      decEWSThreshold := 1.5 * recItem."EWS (Estimated Weekly Sales)"; // 150% of Item's EWS
      decSalesInventoryAvailable := recItem.Inventory - recItem."Qty. on Sales Order" - "Qty. to Ship";
    
      IF decEWSThreshold > decSalesInventoryAvailable THEN BEGIN

        bMarkRecord := TRUE;

      END;

    END;

END;

After getting the Item record, if the Item's Category is not in A / B / N / S, then it ignores the rest of the calculations. Now why didn't I think of that?

I agree with the boolean-ing of the Category, (yes, the field is custom) but I am happier modifying code rather than table structures, for now.

Question: wouldn't looking up a table be slower than reading string variables?

Question: which is preferred in terms of future-proofing a system - modifying tables or modifying code?


Regards,
Stephen

MBerger

Question: wouldn't looking up a table be slower than reading string variables?

Technically, yes....but a GET statement uses the primary key and is usually quite quick.

Question: which is preferred in terms of future-proofing a system - modifying tables or modifying code?

I myself try ( it's not always possible ) to make things so the end user can change the behaviour himself, without having to result into having a coder change the object. It'll cost a little bit more time to make the more flexible version, but if extra ( or less ) categories are needed in the future, you only need to set the value in thecategory table, and you're set.

David_Singleton

Hi Stephen,

I gather you are just starting with NAV development. As a good starting point, you might want to read this blog : http://dynamicsuser.net/blogs/mark_brum ... ation.aspx : hopefully it will help you get on the Navision track.

David_Singleton

And getting on topic.

The solution you are working on is never really going to work. Its probably a great exercise in learning the difference between defining a business need, and writing code, so its probably worth doing for learning.

The issue is that the basic logic wont cover all cases, and down the line even if you get the customer to accept it, they are going to come across many exceptions as to why it doesn't work.

As a starting point though, instead of writing code, do this instead with a flow field. Look at the Inventory field in the Item table, and "copy" it into the Sales Line table. This will be much better for the learning experience and the drill down will help you figure out all the conditions of why it works or not.

David_Singleton

MBerger wrote:

shogan@mila.ie wrote:

Question: which is preferred in terms of future-proofing a system - modifying tables or modifying code?

I myself try ( it's not always possible ) to make things so the end user can change the behaviour himself, without having to result into having a coder change the object. It'll cost a little bit more time to make the more flexible version, but if extra ( or less ) categories are needed in the future, you only need to set the value in thecategory table, and you're set.

Definitely as MBerger says,

In this case adding a new field to a table that probably only has a handful of records definitely add the boolean field for the user to select. Hard coding is the worst thing you can do. You can add this boolean also as a flow field in the Sales Line table.

shogan@mila.ie

David Singleton wrote:

Hi Stephen,

I gather you are just starting with NAV development. As a good starting point, you might want to read this blog : http://dynamicsuser.net/blogs/mark_brum ... ation.aspx : hopefully it will help you get on the Navision track.

Thank you! I do know a few languages but NAV - even after all these years - sill eludes me a little

Never heard of Hungarian Notation before but I have seen it being used and that's why I changed MBerger's original code into the code I use. I hate using single letters as type identifiers, as they can easily be missed. hence why I use 2 or 3 chars at a time. Also helps defining a variable where two words are concatenated together to both begin with capital letters. Drummed into me in college to start vars with small letters. So this way I get the best of both worlds!

shogan@mila.ie

David Singleton wrote:

And getting on topic.

The solution you are working on is never really going to work...

You mean what I am trying to achieve with this code?

Well it is just to highlight when stock is going to run out based on weekly sales of commonly sold items. It's only a age (1.5 was just plucked from the air).

I don't expect it to work based on defining a business need, but the more we use navision, the better we are getting with our guesstimates. So much so that they are more reliable than facts...

So may cases I could talk about right now...

David_Singleton

shogan@mila.ie wrote:

Thank you! I do know a few languages but NAV - even after all these years - sill eludes me a little

You're welcome. Try out doing this with Flow fields and drop the Hungarian Notation and you will be off to a good start.

Also try to get a copy of the Navision style guide read it cover to cover and follow it. That will make your code much more readable and supportable, in the end it will reduce the TCO of Navision for your clients.

shogan@mila.ie

David Singleton wrote:

MBerger wrote:

Question: which is preferred in terms of future-proofing a system - modifying tables or modifying code?

I myself try ( it's not always possible ) to make things so the end user can change the behaviour himself, without having to result into having a coder change the object. It'll cost a little bit more time to make the more flexible version, but if extra ( or less ) categories are needed in the future, you only need to set the value in thecategory table, and you're set.

Definitely as MBerger says,

In this case adding a new field to a table that probably only has a handful of records definitely add the boolean field for the user to select. Hard coding is the worst thing you can do. You can add this boolean also as a flow field in the Sales Line table.

As these categories are defined on a business need, I would not want any user to select or change the Categories in question. I'd have to lock down this new boolean field to permissions on the Sales Line and Category tables - just a little too much work for now.

That, and those Categories have not changed in 15 years...

But I do agree. I am a sysadmin and if I am asked to do something for someone I try to develop a solution where the user can do it themselves, with the proer safety constraints in place AND TESTED. The more I put out onto the main office, the more users feel in control.

David_Singleton

shogan@mila.ie wrote:

I don't expect it to work based on defining a business need, but the more we use navision, the better we are getting with our guesstimates. So much so that they are more reliable than facts...

The problem is that in a few months, users forget that this is just a rough idea, and start to rely on it. Then they see a "non highlighted" line but find that they are actually out of stock and then blame Navision for getting it wrong. A better solution is to show the numbers and let people learn to think for them selves.

Navision has two extremes for Inventory out management. The warning it gives when you enter quantity, which is pretty useless for most companies, and reservations (or bins or serial numbers) which are generally too difficult to use for most companies. It does not have a proper Inventory allocation system that fits somewhere in the middle. So many developers try to figure something that works for the customer, and slowly realize that its not as simple a task as it first seems. BUT it is a great learning exercise and will teach you a lot about Navision, so keep at it.

David_Singleton

shogan@mila.ie wrote:

That, and those Categories have not changed in 15 years...

That is not an excuse for Hard coding. An its not just the business need changing. There are many reasons that you should never hard record identifiers into code. Not the least of which is that you remove the business need form the data model, which means you will have to hard code everywhere. Security is the great developers fall back (and the laziest one) don't use that.

Add the boolean to the table, then make the field not editable. Security solved.

If you want to learn Navision then a/ you need to do it the Navision way, b/ you need to listen to advise that is given to you.

David_Singleton

shogan@mila.ie wrote:

I am a sysadmin and if I am asked to do something for someone I try to develop a solution where the user can do it themselves, with the proer safety constraints in place AND TESTED. The more I put out onto the main office, the more users feel in control.

So I gather you are an end user, and you purchased the Navision development tools. You should just think "We paid for these tools so we should be able to use them how we want". The Application Builder license is very powerful in what damage you can do with it, and ideally you should have done Navision C/AL development training before making changes. Maybe talk with your Partner so see if they offer some basic training. You have already spent a lot of money, so why not spend a little more and get access to the full power of what you have.

shogan@mila.ie

David Singleton wrote:

shogan@mila.ie wrote:

I don't expect it to work based on defining a business need, but the more we use navision, the better we are getting with our guesstimates. So much so that they are more reliable than facts...

The problem is that in a few months, users forget that this is just a rough idea, and start to rely on it. Then they see a "non highlighted" line but find that they are actually out of stock and then blame Navision for getting it wrong. A better solution is to show the numbers and let people learn to think for them selves...

I should have been a bit clearer... on the Purchasing side, we have automated the generation of PO's, based on EWS (13 weeks sales history for an item - covers 4 months, almost) or Item Sales Forecasts (over the next 13 weeks), and developed NAV based on MAD ("Mean Average Deviation". Basically a mathematical way to achieve stock on hand to satisfy a 98% service level on A / B / N / S Category Items (to achieve OTIF's - "On Time In Full") sales lines, and more importantly, sales orders, thus reducing backorders and out of stocks, by analysing ROPs and ROQs. It's a complicated affair and took nearly 2 years to develop properly. But has proved very successful since mid-2008.


EWS is more of a purchase-used calculation than sales - up until now, that is.

shogan@mila.ie

David Singleton wrote:

shogan@mila.ie wrote:

I am a sysadmin and if I am asked to do something for someone I try to develop a solution where the user can do it themselves, with the proer safety constraints in place AND TESTED. The more I put out onto the main office, the more users feel in control.

So I gather you are an end user, and you purchased the Navision development tools. You should just think "We paid for these tools so we should be able to use them how we want". The Application Builder license is very powerful in what damage you can do with it, and ideally you should have done Navision C/AL development training before making changes. Maybe talk with your Partner so see if they offer some basic training. You have already spent a lot of money, so why not spend a little more and get access to the full power of what you have.

Actually no, I have been a system admin since mid-2002 for this company. Normally I spec out business requirements and consult with our service provider. Logic is very similar to what I grew up with in previous companies. I've already gotten the NAV training books from Austria.

I comment EVERY SINGLE CHANGE in ANY object.

Testing testing testing. I am not happy unless all changes have been tested. And then tested to see if the change adversely affects other code/modules.

I have a duplicate server on a PC running the MS Dynamics NAV Server service and NAS service, completely separate from the production system, and have a separate icon on everyone's computer, pointing at a separate .zup file. No mistakes then.

All changes after testing go to our provider (as we need to keep changes synced). And I get them to look at the changes to see if they are OK with them.

They trust me to make changes and it saves our company money to be spent on the bigger projects.

Anyway - now you see why I am sooooo reserved on changing tables? I leave that to the providers. I deal with non-processing reports, formatting fields on forms, and we use Jet Reports almost exclusively now.

Backups are to the cloud and local disk storage (got rid of tapes last year), and restoring the db takes approx 2.5 hours for a db 18GB in size (at approx 70% usage).

I am pretty safe having all these protocols in place. Damage is kept to a minimum and if I don't feel safe doing something, then I don't do it but I pass it on to get done.

I am employed as a sysadmin and not as a DB dev or DBA even. but NAV is soooo critical to the workings of the company that I deliberately limit what I do to ensure the safety of the data. Remember this is my network, so I keep it safe and away from harm as much as I can.

shogan@mila.ie

David Singleton wrote:

shogan@mila.ie wrote:

That, and those Categories have not changed in 15 years...

That is not an excuse for Hard coding. An its not just the business need changing. There are many reasons that you should never hard record identifiers into code. Not the least of which is that you remove the business need form the data model, which means you will have to hard code everywhere. Security is the great developers fall back (and the laziest one) don't use that.

Add the boolean to the table, then make the field not editable. Security solved.

If you want to learn Navision then a/ you need to do it the Navision way, b/ you need to listen to advise that is given to you.

I totally am in agreement with you on the data vs model principle. Functionality should be data-driven and not hard-coded. In time, if those Categories are used in one or two more areas, then I'll get my hands dirty and modify the table.

If I had my way I would do a course in C/AL but here in Ireland, NAV training is expensive and there aren't many training houses out there doing it. Help file is excellent for the C/SIDE reference, and heck I might even look at some dev tools/IDEs on mibuso's downloads section. I just need to become more familiar with then environment as it is presented to me now.

Security may be the developers 'laziest' fallback, but sadly as I also wear a sysadmin hat, it's my job to ensure that it is in place and followed and probably one of the hardest aspects of my job. I don't do lazy. It's not my thing.

As for a.) and b.) I feel that it should be that Navision follows the needs of the business when there is a business case to be followed; I'd do take advice seriously hence our close working relationship with our provider: if they spot something we/I are/am not doing right, or if I need assistance, they are very helpful. I don't try to be out-of-the way difficult, and use comments both in the Documentation section and within the triggers themselves.

Also our provider's Case system logs all changes made as well.

I tend to let things build up as one of the most common things in this company is that so few people see where developments go. I build a lot of future-proofing into any spec for development that I write and pass to the provider.

But as I am making a sales team's job a little easier (and the purchasing team's a little harder!) I believe hard-coding temporarily is warranted. Think of it as a live "trial-run" as future requests may be built on top of this. At the end of the year I'll be looking at all developments to date and checking to see if there is any overlap with previous work/future plans. I sometimes do this exercise to speed up performance and ensure security.

Believe me when I say that NAV is a constant "work in progress".

Thanks David for your frank responses. I have developed a thick-skin when it comes to NAV development, so I take everything seriously. Sometimes I just wish I could just...well.... develop.