3 Replies Latest reply on Jan 13, 2013 1:06 PM by MarkNZ

    Update Product Counter from Portal 2


      Hi, all

      I have added a new tab in a PRODUCTS Table..Data Entry Form View with a portal pointing to a newly created StockIN Table.


      The user can create new STOCKIN records within this portal. please note that the STOCKIN Table is using the PRODUCTS ID to correctly relate, Identify the new records created.

      In the PRODUCTS Table there is currently a Stock Counter which decreases in number when a line Item in INVOICES is committed.

      Q. What script could be used to trigger the products counter button to update & or decrease in quantity as new entries are made in this portal which is in the Form view Layout in the PRODUCTS table .

      see attachment..

      Regards Mark

        • 1. Re: Update Product Counter from Portal 2

          In the Products table...

          you can have a totalIN of the stockIN by creating a calc which says sum(stockIN::Qty)


          you can have a totalOUT of the lineItems by creating a calc which says sum(lineitems::Qty)


          Because the relationships are by product ID...


          You can then have a third calculation field that says totalIN-TotalOUT which should give you the current stock level. If you return faulty stock you would make negative qty's in the stockIN table.




          - Lyndsay

          1 of 1 people found this helpful
          • 2. Re: Update Product Counter from Portal 2

            Not sure if I would want to go with calcs here... those will become slower and slower as the database grows.  Also searches on prodcuts that are below a certain "# in stock" will become very painful over time.  A scripted routine would be better in this case.

            1 of 1 people found this helpful
            • 3. Re: Update Product Counter from Portal 2

              Hi Lyndsay, thanks for your comment..

              I have attached a couple more snap shots that may give a better idea of whats going on..hopefully anyway


              In the PRODUCTS Table Form View each rocord pertains to one model of Radio only  As you can see in the portal the user can create new records for that Model which is done in the StockIN Table.

              What i need to do is have the qty counter update with these newly entred records. As you can see the portal shows only the related stockIn entries that pertain to the Product ID ( as i have used a fk id in StockIn Table)

              Otherewise I have tio manually change the qty to the counter eachtime I create a new stockIn item, record.


              So in essence Im wanting the have the Qty counter show the qty which you can see in the Portal.  The one on the left is the StockIn Table.


              Regards Mark  PS: Wim feel free to comment....

              StockIN table.JPGProducts table.JPG