How to export Inventory by Location with Dataport

selece28

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

kine

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...

jonsan21

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.

leugim

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" ( ) in excel

make sure the fields you are calculating are filtered by location

regards

Savatage

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??

ara3n

It's buggy and it crashes Nav client.

I wouldn't use it.

Alex_Chow

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.

dmitrip

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.

ara3n

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.
  }
}

Alex_Chow

Why do customization when there's a standard NAV functionality that gives you the information already? :-k

ara3n

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?

Alex_Chow

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.

ara3n

what if they don't have SKU in their license?

Alex_Chow

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.

ara3n

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.

selece28

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.

cunny

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

selece28

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

cunny

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