Serial No. - Assíst Button In Sales Order takes 5 Minutes

Steffen_BrodowskiSteffen_Brodowski Member Posts: 20
Hello Everybody!

I have a Customer, who works with four Companies in Navision. At the range of 12 month there are 1.800.000 Item Ledger Entries per company.

Working with serial numbers on items is critical, because 10.000 serial numbers per item is "nothing".

I try to select a serial number on sales order - Line/Trackinglines/Assistbutton on Field "Serial No.", but it takes 5 minutes until the form pops up, so I can select a number.

Servertime to get 10.000 Serialnumbers is 6,5 seconds, so the rest of the 5 minutes is used by sending all records to the client and generate temporary entries, shown on the form.

It's crazy to programm such thing by navision, but we have to stand at front and have to tell a solution to the customer.

But which solution?

Steffen Brodowski


  • Options
    MissesMagicMissesMagic Member Posts: 39
    How about keys? - my first idea
  • Options
    krikikriki Member, Moderator Posts: 9,090
    How about SQL or Navison DB? My first idea. :wink:
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Options
    nunomaianunomaia Member Posts: 1,153
    The way Navision is designed to handle serial numbers isn’t the best way. Navision assumes that are only a few hundred serial numbers per item code. You have to make a customization in that Form to try avoid that issue. For example, instead of displaying all the available serial numbers, only display the first.
    Try to create new keys and other code optimizations.
    One of the new features that Navision 5 is promising it’s to solve that issue, I can’t confirm that.
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    What nunomaia is the correct direction. This is not about keys and server issues, the problem is that the serial numbers are linked into the reservations, and when you open the Serial number form, it runs though all the reservation entries and then populates a temporary table to display them.

    This problem started big time when 3.00 (Solutions) was pseudo released. Then through each incarnation, 3.01, 3.10 3.60, 3.70, 4.00 they took a new perspective, and came up with a new way of how to handle serial numbers.

    Basically they were looking at the problem form completely the wrong angle, since the problem was never with Serial number, the problem was with how costing is calculated.

    Your problem is that in reality you don't need or want a temporary table with serial numbers in it, you really want a real table. Now I do not know your business model, and I don't have enough information to even start to suggest what you should do, but because I am me I am going to anyway \:D/

    I can see a number of solutions, some actually fixing the issue (that would mean DO NOT use Dynamics NAV serial numbers, and write your own serial number handling) and some quick fixes.
    Th quick fix (which is probably what you have to do since you probably have a lot of history, AND I guess the client needs this to work now).

    Create new table for serial numbers. Have this table generated when a reservation entry is created that contains serial number information. (Yes you are basically duplicating existing data, not a good database practice but...). This table will contain a link back to the Item ledger entry and the reservation entry, so you can get all the information needed.

    Now create a new serial number look up that uses this table. When you select a serial number, use code to generate the appropriate Temporary table and pass this back to the standard function, so that the code thinks it was generated from the existing form.

    With out really knowing your customers business model, and based on the limited information here, that's what I would do, but there are many many more solutions.
    David Singleton
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    Oh btw, you could add fields, keys etc to the reservation entry table, and thus save a lot of analysis and development time (in the short term), but if you do that you would substantially increase the amount of table locking, and that would not be good in a database this size.
    David Singleton
  • Options
    Steffen_BrodowskiSteffen_Brodowski Member Posts: 20

    thanks for your answers.

    My solution was also to show only 100 serial numbers by pressing the assist button.

    Native or SQL is not the question, this quantity of data is only to handle if using SQL. An performance tuning on SQL is done - they call me "Performance Man" since three years. :-)

    As I wrote there is no performance tuning impossible, because 6 seconds by the server to get the data is more rapid then anyone can give.

    I guess that navision, sorry, Microsoft, is a little stupid to think that an item only has some hundred serial numbers on livetime of an item. And posting this by 3000 orders per day will take time.

    But it is as it is and so the customer can't use this to get an view of serial numbers or selecting over all numbers - thats live.

    Syncronisation of an own table for the numbers is an heavy work, because the programmer of the serial number modul does not use the insert or other table triggers. He also uses 4 temporary tables (one for insert, one for modify, one for delete and one for the new ones) So I have to change all this "rubbish" sourcecode - no thanks.

    I thought, there is an better solution to solve this, but - as "sometimes" in navision, there are no good solutions without paying money for it, from witch you can by a house for. :-)

    But if I don't fix the problem, some people will get smokers - or what is a non smoking person doing, if it wants to open this form 10 times on half a day? Trinking coffee is also not good for this person - some liters per day, I think.

    Thanks for your answers.

    Greetings from Germany

    Steffen Brodowski
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    I don't think its all that bad. You can modify the codeunit 99000830 on the CreateEntry section to start.

    Of course it would be nicer if they called the OnInsert, OnModiify and OnDelete triggers in the reservation table ](*,)

    Still I think its your best solution.
    David Singleton
  • Options
    AGHAGH Member Posts: 6
    Consider to use barcode reader device so that your customer doesn't have to pick a serial no one by one.

    You can make a little modification for a validation on the 'serial no' field in when the customer enters a new serial no.
  • Options
    MOYMOY Member Posts: 3

    I had the same pb and i resolve it.
    The assist edit calls the function "Assistedit" in the code unit 6500 "Item traking management" and uses SETFILTER with wrong keys. Yous should do next steps :

    1. Add a new key in the table 32 "Item ledger entry" with these fields : "Item No., Open, Variant code, Positive, Location code, Serial No." and the SIF "Quantity, Remaining Quantity"

    2. Add a new key in the table 337 "Reservation entry" with these fields : "Item No., Variant code, Location code, Reservation status, Serial No." and the SIF "Quantity, Quantity (base), Reserved Pick & Ship Qty"

    3. In the Code unit 6500 serch
    Reservation.SETCURRENTKEY(...) and ItemLeadgerEntry.SETCURREENTKEY(...) lines
    and at the end add the field "Serial No.".

    4. find the line with :
    If TempReservEntry.FIND'-' then

    and add a new line after the line "Repeat"
    TempEntrySummary.SETCURRENTKEY("Serial No.");

    It will take just 2 seconds to show all lines . =D>
Sign In or Register to comment.