Bin Quantity - SQL

RabbitPostRabbitPost Member Posts: 17
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

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

Answers

  • ShaiHuludShaiHulud Member Posts: 189
    edited 2022-06-30 Answer ✓
    Bin Quantity is calculated from table "Warehouse Entry"
  • RabbitPostRabbitPost Member Posts: 17
    Hi Shai

    DO you know what the calculation is
  • ShaiHuludShaiHulud Member Posts: 189
    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.
Sign In or Register to comment.