How to export Inventory by Location with Dataport

selece28selece28 Member Posts: 316
Hi Nav Masters,
I have a question
My client want to export Inventory balance per Location.
I'm able to do it with a Dataport with DataItem "Item"
And onBeforeExportRecord i just CALCFIELD the Inventory.
My question is, if i have more than 1 location, i just filter per location, but the problem is one item code only write once, how to make dataport write 1 line per location. I want to loop for all location for every item code.

I know we can do it another way with Report, by writing manually to a file.
But can a dataport do it?

Thanks in advance
______________

Regards,
Steven

Comments

  • kinekine Member Posts: 12,562
    Dataport can do it, but you will need to make it manually over Integer table. It means that e.g. you will populate some Temp table with all combination of item and location and you will export that table in one loop etc.

    But for me it is faster to create report and write the data through FILE variable. It will be easier...
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • jonsan21jonsan21 Member Posts: 118
    The FASTEST and EASIEST way would be to open up Items by Location Form (F491), highlight everything, Click CTRL+C, open up Excel and click CTRL+V, yes?

    Rgds,

    Jon.
    Rgds,

    Jon.
  • leugimleugim Member Posts: 93
    hi

    in NAV 4 SP3 you can indent tables in a dataport as when designing reports (i believe this is a new feature of version 4).

    so, you can insert Location table before Item table, and then indent Item table to Location. the bad news are that you must set filters manually, but this way seems to be better than designing a report or programming a loop... or even "copy-pasteing" ( :D ) in excel

    make sure the fields you are calculating are filtered by location

    regards
    _______________
    so far, so good
  • SavatageSavatage Member Posts: 7,142
    leugim wrote:
    in NAV 4 SP3 you can indent tables in a dataport as when designing reports (i believe this is a new feature of version 4).

    Really??
  • ara3nara3n Member Posts: 9,255
    It's buggy and it crashes Nav client.

    I wouldn't use it.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Alex_ChowAlex_Chow Member Posts: 5,063
    Don't use the item table. Use the Stockkeeping Unit table. That's what the table is used for, it gives you item by location information.
  • dmitripdmitrip Member Posts: 44
    Personally I use a custom report.

    The other option is to run a Physical Inventory Journal -> Calculate Inventory. It doesn't calculate inventory by Lot/Serial No. though.
  • ara3nara3n Member Posts: 9,255
    Here is an example howto.
    OBJECT Dataport 50000 Example Export Item by Loc
    {
      OBJECT-PROPERTIES
      {
        Date=02/14/08;
        Time=[ 8:39:33 AM];
        Modified=Yes;
        Version List=MOD01;
      }
      PROPERTIES
      {
        Import=No;
        FieldStartDelimiter=<None>;
        FieldEndDelimiter=<None>;
        FieldSeparator=<TAB>;
      }
      DATAITEMS
      {
        { PROPERTIES
          {
            DataItemTable=Table2000000026;
            AutoSave=No;
            AutoUpdate=No;
            AutoReplace=No;
            DataItemTableView=SORTING(Number);
            OnPreDataItem=BEGIN
    
                            ItemLedger.SETRANGE(Open,TRUE);
                            IF ItemLedger.FINDSET THEN REPEAT
                              TempItemLedger.SETCURRENTKEY("Item No.");
                              TempItemLedger.SETRANGE("Item No.",ItemLedger."Item No.");
                              TempItemLedger.SETRANGE("Location Code",ItemLedger."Location Code");
                              IF TempItemLedger.FINDFIRST THEN BEGIN
                                TempItemLedger."Remaining Quantity" += ItemLedger."Remaining Quantity";
                                TempItemLedger.MODIFY;;
                              END ELSE BEGIN
                                TempItemLedger."Item No." := ItemLedger."Item No.";
                                TempItemLedger."Location Code" := ItemLedger."Location Code";
                                TempItemLedger."Remaining Quantity" := ItemLedger."Remaining Quantity";
                                TempItemLedger."Entry No." := ItemLedger."Entry No.";
                                TempItemLedger.INSERT;
                              END;
                            UNTIL ItemLedger.NEXT = 0;
    
                            TempItemLedger.RESET;
                            SETRANGE(Number,1,TempItemLedger.COUNT);
                          END;
    
            OnBeforeExportRecord=BEGIN
                                   CLEAR(Column);
    
                                   IF Number = 1 THEN
                                     TempItemLedger.FINDSET
                                   ELSE
                                     TempItemLedger.NEXT;
    
                                   Column[1] := TempItemLedger."Item No.";
                                   Column[2] := TempItemLedger."Location Code";
                                   Column[3] := FORMAT(TempItemLedger."Remaining Quantity");
                                 END;
    
          }
          FIELDS
          {
            {      ;     ;Column[1]            }
            {      ;     ;Column[2]            }
            {      ;     ;Column[3]            }
          }
           }
      }
      REQUESTFORM
      {
        PROPERTIES
        {
          Width=9020;
          Height=3410;
        }
        CONTROLS
        {
        }
      }
      CODE
      {
        VAR
          Column@1000000000 : ARRAY [50] OF Text[100];
          I@1000000001 : Integer;
          ItemLedger@1000000002 : Record 32;
          TempItemLedger@1000000003 : TEMPORARY Record 32;
    
        BEGIN
        END.
      }
    }
    
    
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Alex_ChowAlex_Chow Member Posts: 5,063
    Why do customization when there's a standard NAV functionality that gives you the information already? :-k
  • ara3nara3n Member Posts: 9,255
    Alex Chow wrote:
    Why do customization when there's a standard NAV functionality that gives you the information already? :-k

    Because It's not good enough?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Alex_ChowAlex_Chow Member Posts: 5,063
    ara3n wrote:
    Alex Chow wrote:
    Why do customization when there's a standard NAV functionality that gives you the information already? :-k

    Because It's not good enough?

    Why not? The requester asked to export items by location. Stockkeeping Unit does this without complex programming.
  • ara3nara3n Member Posts: 9,255
    what if they don't have SKU in their license?
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Alex_ChowAlex_Chow Member Posts: 5,063
    SKU comes standard in BRL licensing. However, the poster didn't state what version they're using and what licensing model they're on.

    What if the client uses version 3.7? The code you posted will not compile, so I guess we can keep asking "what if" until our fingers get tired.

    I guess it's different approach to addressing the problem. I prefer programming as the last possible alternative.
  • ara3nara3n Member Posts: 9,255
    I agree that programming should be the last resort.
    I provided the example for people who have been asking on how to indent and instead they can use temporary record to calculate their data and then export using integer dataitem.


    Also the thread was going to long and I just wanted to finish it.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • selece28selece28 Member Posts: 316
    Sorry i forgot to mention, yes we don't use SKU. And cannot copy from Form 491 because i just want to export the item with qty > 0.
    I'm thinking of using a dataport because i thought theres an easy way to do it. But after i read this thread i think the easiest way is to create a report to write to FILE.
    Thanks ara3n for the code example.
    And Thanks to All for the ideas, I really appreciate it.
    ______________

    Regards,
    Steven
  • cunnycunny Member Posts: 129
    Just want to mention that there is another approach.

    What if you have no chance to run the code logic within Navision (Outside of Navision)? I ever faced the problme when I was trying to build a mobile client which need to extract Inventory by Location from C/SIDE SQL db directly. I solve it by simply querying the SIFT table through something like ADO. Because whatever you do in Navision, finally it still needs to go to the right SIFT table. I mean if you want to use CALCFILEDS function in stead of crazy SUM, Off course , it's just for SQL db. Maybe C/FRONT or C/ODBC can be used for Native DB too. Anyway, it's just another idea for some special cases when you need to retrieve that information outside of Navision.

    Regards
    cunny Lee
    MCP - MBS Navision
    [email protected]
  • selece28selece28 Member Posts: 316
    Ow ya, now that you mention it. How to use the SIFT table?
    I usually create a query with SUM and grouping. Is there any other easier way?

    Thanks
    ______________

    Regards,
    Steven
  • cunnycunny Member Posts: 129
    Hi there,

    Within Navision, your way is the easiest way, and also ara3n and Alex Chow have provided very nice ideas. They all works

    As I said my idea is for the cases when you can't do it within C/SIDE. Especially when you want to integrate few systems then you have to do it outside and there is a possibility to use ADO query the SQL SIFT table and probably this is the fastest way in that case. Otherwise my idea will make things more complicated if you can do it in Navision. For how to use ADO you need to spend a little bit time to tabke look at it, the fellows here have post very uesful articles including sample codes on this forum. For SIFT you need to find the proper information from Navision Application Design Guide or Navision Certification courseware. Then you will know how it works

    Good Luck :)
    cunny Lee
    MCP - MBS Navision
    [email protected]
Sign In or Register to comment.