3 Replies Latest reply on Jun 27, 2011 8:43 AM by philmodjunk

    Update Record

    bablahemen

      Title

      Update Record

      Post

      Hello,

      I have 3 Database.

      1) Purchase Order 2) Material Received 3) Inventory.

      kindly need ur suggestion on how i update the the inventory file automatically while creating the Material Received Voucher.

      The follow goes :

      PO made by Salesmen

      When Material received, MRV made.

      MRV Details should auto update the related item in the Inventory. i.e If Item1 in PO is 10. So when i make MRV its should be

      Inventory Qty = inventory qty + MRV Qty(imported MRV qty from MRV DB).

      But i am not able to update. but i am able to get to data to inventory file but cant update.

      Please if anyone have the solution. please let me know.

        • 1. Re: Update Record
          philmodjunk

          Two options, a script uses set field to update a single record in inventory by adding the new amount received to it. (A similar script would deduct items shipped, consumed, lost etc.)

          Set Field [Inventory::QtyOnHand ; Inventory::QtyOnHand + MaterialRecieved::Qty]

          Could be performed from each new record in materialRecived if you link the two tables by an InventoryID number.

          Another option is to set up a ledger type table where each record represent an inventory change for one inventory item. One field is used to log items added to inventory and another is used to log items removed from inventory. A combination of calculation and summary fields are used to compute not only the total on hand for each inventory item to to provide a running total so that you can also see how your inventory levels change over time (which can be helpful in determining appropriate re-order levels.)

          • 2. Re: Update Record
            bablahemen

            Hi Phil,

            Thanks for the solution. It was really helpful to solve my problem. but i have one dought, the field wht i have used are repetative fields.

            How would i be able to check with individual records in inventory file? i have attached an sample, which is checking 2 fields witht he inventory.

            can u help me solving this.


            If [ Get ( AccountName ) = "vipin" ]
            Set Field [ HAA SRV Inst::Supplier_ref ]
            If [ HAA SRV Inst::Supplier_ref = Inventory::Supplier Code ]
            Set Field [ Inventory::Qty_Location_Ghusais; Inventory::Qty_Location_Ghusais + HAA SRV Inst::qty ]
            End If
            Set Field [ HAA SRV Inst::Supplier_ref[2] ]
            If [ HAA SRV Inst::Supplier_ref[2] = Inventory::Supplier Code ]
            Set Field [ Inventory::Qty_Location_Ghusais; Inventory::Qty_Location_Ghusais + HAA SRV Inst::qty[2] ]
            End If
            Else If [ Get ( AccountName ) = "surajit" ]
            Set Field [ HAA SRV Inst::Supplier_ref ]
            If [ HAA SRV Inst::Supplier_ref = Inventory::Supplier Code ]
            Set Field [ Inventory::Qty_Location_JAFZA; Inventory::Qty_Location_JAFZA + HAA SRV Inst::qty ]
            End If
            Set Field [ HAA SRV Inst::Supplier_ref[2] ]
            If [ HAA SRV Inst::Supplier_ref[2] = Inventory::Supplier Code ]
            Set Field [ Inventory::Qty_Location_JAFZA; Inventory::Qty_Location_JAFZA + HAA SRV Inst::qty[2] ]
            End If
            End If

            • 3. Re: Update Record
              philmodjunk

              I would not use repeating fields for this. You need one record for each item being added/removed to/from inventory.