4 Replies Latest reply on Oct 2, 2014 2:00 PM by TaukoririMeita

    How to auto update records when change is made from another table via Portal?

    TaukoririMeita

      Title

      How to auto update records when change is made from another table via Portal?

      Post

      This question is based on a Simple POS (Point of Sale) System that I'm trying to design for my small shop.

      Current Setup: (Table and Fields)

             
      1. Date Table: (User, Date)
      2.      
      3. Sales Table: (Item, Code, Price, Qty, Total)
      4.      
      5. Products Table: (Item, Code, Price, Qty, Total)
             

      In Date Layout mode there is a Sales Portal that links to Sales Table so a user can enter date and start the sale that day. I'm currently stuck with the Products table: see my goals below.

      My Goals:

             
      1. Users can only sell Items from the Products table.
      2.      
      3. When an Item is entered in Sales Portal the other fields (Code, Price, etc...) will auto enter based on what product that is in the Products Table.
      4.      
      5. When an Item has entered in Sales Portal, that Item in Products table will automatically updated (reduced quantity, total, etc…) based on the sale.

      My Questions: (How to do list below?)

             
      1. In Sales Portal, user can only sell items from Products table.
      2.      
      3. When an item is added in Sales Portal, that item in Products Table will auto calculate the remaining quantity, etc…
      4.      
      5. When new date is created, Products will stay the same but Sales Portal will reset.

      I hope what I mean is clear, please me to make things more clear if you’re confused.

       

      Thank you.

       

       

        • 1. Re: How to auto update records when change is made from another table via Portal?
          philmodjunk

          3, Creating a new record in Sales with a new date should result in a portal that is initially empty, but you haven't indicated what fields are used as match fields in your relationships so my guess that you are matching records by date may not be correct.

          It looks like you are missing a table here.

          Most POS systems work from this basic structure (often with many enhancements added:)

          Invoices----<LineItems>-------Products

          Each record in Invoices represents one customer receipt for one customer sale. The LineItems table lists each item purchased on a given receipt and fields in LineItems use auto-enter options to "look up" data from products such as the unit price for each product.

          For inventory control, thie LineItems table can be set up as a "transactions ledger" that logs each change in inventory so that a total on hand can be computed. See this thread for more details: Managing Inventory using a Transactions Ledger

          • 2. Re: How to auto update records when change is made from another table via Portal?
            TaukoririMeita

            Thanks PhilModJunk your relationship solution Invoices----<LineItems>-------Products works great (I re-structured my database and relationship). Now I'm puzzled with another problem. In Products I have fields "Items", "Price", "Quantity", "Total Price". Then I created another field called "Remain" that will show how many Item remains after an Item has been added in LineItems Portal. The formula that I used is just a subtraction of the Quantity in Products table with Quantity in LineItems. So (Products::Quantity - LineItems::Quantity = Productions::Remain). The calculation works but just wouldn't update normally. When an Item is added in the LinesPortal, the remaining items in Productions won't update unless I re-open the calculation formula window or restart filemaker.

            How can I solve this problem?

            Thanks again.

            • 3. Re: How to auto update records when change is made from another table via Portal?
              philmodjunk

              Why would you have fields for Quantity and Total Price in the Products Field.

              The method that you are using is not the "inventory ledger" method that I recommended in my first post.

              My best guess is that you have taken a number field and defined an auto-enter calculation for it in Field Options. This field should be defined as a field of type calculation not an auto-entered calculation.

              • 4. Re: How to auto update records when change is made from another table via Portal?
                TaukoririMeita

                Yes Phil, I do have a field with auto-enter calculation, this is so that users can select the field and a list of Items will drop down, select one of the item and the rest of the fields in the portal are automatically filled according to the relationship except the quantity field. Then they will enter the quantity of that item and the Total will be calculated.

                The reason why I have fields for Quantity and Total Price in the Products Field is that I want to keep track of how many Items left in my Products after a sale and what is their current value (for easy stock taking in the future).

                It might not be the inventory ledger solution but it certainly is for inventory(products) we just might have different requirements.

                Thank you very very much Phil, I will try and work around with the auto-enter calculation problem atm.

                God Bless you.