Bin Quantity - SQL

RabbitPost
RabbitPost Member Posts: 17
edited 2022-06-29 in NAV/Navision Classic Client
Dear All

I am trying to pull in to Excel the current quantity in each Bin Code using SQL and the Nav db as an external data source. Can anyone advise which Table holds the current bin quantity for items.

We are using the Warehouse module

Best Answer

  • ShaiHulud
    ShaiHulud Member Posts: 228
    edited 2022-06-30 Answer ✓
    Bin Quantity is calculated from table "Warehouse Entry"

Answers

  • ShaiHulud
    ShaiHulud Member Posts: 228
    edited 2022-06-30 Answer ✓
    Bin Quantity is calculated from table "Warehouse Entry"
  • RabbitPost
    RabbitPost Member Posts: 17
    Hi Shai

    DO you know what the calculation is
  • ShaiHulud
    ShaiHulud Member Posts: 228
    Bin Quantity is calculated in "Bin Content" table's field "Quantity". It's a flowfield with the following calculation formula: 
    CalcFormula = Sum("Warehouse Entry".Quantity WHERE("Location Code" = FIELD("Location Code"),
                                                                "Bin Code" = FIELD("Bin Code"),
                                                                "Item No." = FIELD("Item No."),
                                                                "Variant Code" = FIELD("Variant Code"),
                                                                "Unit of Measure Code" = FIELD("Unit of Measure Code"),
                                                                "Lot No." = FIELD("Lot No. Filter"),
                                                                "Serial No." = FIELD("Serial No. Filter"),
                                                                "Package No." = FIELD("Package No. Filter")));
    "Lot No. Filter", "Serial No. Filter" and "Package No. Filter" and FlowFilter fields, so they will not exist in the database, but the others you should be able to find.
    If you're not using alternative Variants or Unit of Measures, then you don't need to include those either. In that case, you only need to filter by Location Code, Bin Code and Item No.

Categories