3 Replies Latest reply on Apr 16, 2016 11:06 PM by electon

    inventory menagment - How to show only some part of chart?

    SzymonHajduk

      Hi I'm novice at filemaker,

      I trying to build some simple inventory menagmet database.

      Balance table where I count down inventory stock looks like that:

       

      BALANCE TABLE:

      Id

      Generation_date

      Product_fk

      In

      Out

      Sum_of_in (running)

      Sum_of_out (running)

      Calculation (sum_of_in (running) - sum_of_out (running)) - When sorted by products give me idea how stock change

      Sum_of_in

      Sum_of_out

      Calculation(sum_of_in - sum_of_out) When sorted by product give me up-to-date stock

       

      Now. When I'm in PRODUCT TABLE i want to show how stock change. Using relation between PRODUCT TABLE and BALANCE TABLE

      Show Product_fk = Product_Pk

      and extra because I want to see only changes of stock that was taken since some date. HOW TO DO THAT??

       

      when I add extra filed to relational between PRODUCT TABLE and BALANCE TABLE I can see only part of records but Calculation also change and dosen't show real value.

       

      Sorry for my english but outcorretion seems to not work.

        • 1. Re: inventory menagment - How to show only some part of chart?
          erolst

          Several ways:

           

          1. Filtered portal

           

          Create a global date field in the Product table; use it to filter a Balance portal with

           

          IsEmpty ( Product::gFilterDate ) or Product::gFilterDate ≤ Balance::Generation_date

           

          Make sure you add some kind of field trigger to refresh the display.

           

          Depending on the number of related Balance records per product, and/or your further plans in using (and not just displaying) the related values, this may not be a solution for you; in that case …

           

          2. Filter by relationship

           

          Again, create a global date field; give it an auto-enter calculation of

           

          Case ( IsEmpty ( self ) ; Min ( Balance::Generation_date ) ; self )

           

          Create a new table occurrence of Balance, say, Balance_filteredByDate, and use it to create a new relationship:

           

          Product::Product_pk = Balance_filteredByDate::Product_fk

          Product::gFilterDate ≤ Balance_filteredByDate::Generation_date

           

          Place the global field and a portal into that new relationship on your Product layout, and use it …

           

          SzymonHajduk wrote:

          I want to see only changes of stock that was taken since some date […]

          when I add extra filed to relational between PRODUCT TABLE and BALANCE TABLE I can see only part of records but Calculation also change and dosen't show real value.

           

          Well, that's the effect you're after, right? Filtering the relationship (i.e. adding another predicate) will result in a smaller subset than an unfiltered relationship (fewer predicates). Not sure what you mean by “real value”.

           

          SzymonHajduk wrote:

          HOW TO DO THAT??

           

          Please be so kind and don't use ALLCAPS and/or excessive punctuation!!!!

           

          Most users in this forum are neither deaf nor blind  … so that wouldn't usually be necessary

          • 2. Re: inventory menagment - How to show only some part of chart?
            SzymonHajduk

            Hi thanks for fast reply.

            I did what you advice but this is not working. I think it will be more clear if I add example:

            Look at Stock change in excel. Everything is explained there.

             

            Calculation change when I make filtered by relationship and I would like to see real value.

            • 3. Re: inventory menagment - How to show only some part of chart?
              electon

              I'm afraid that will not work If you use summary fields for sum_of_in ( running ) and sum_of_out ( running ).

              As soon as you limit your data set by date the summaries will re-count from the first record shown upwards.

              So it will not show correctly how much total inventory was on that date, just how much you added or removed.


              If you want to see how much inventory was on a given date the table should look more like this:


              ID

              Generation date

              Product

              Stock Change

              Stock Total

              1

              4/16/16

              A

              10

              10

              2

              4/17/16

              A

              10

              20

              3

              4/18/16

              A

              -5

              15

              4

              4/19/16

              A

              -1

              14

              5

              4/20/16

              A

              5

              19

              6

              4/21/16

              A

              -2

              17

              7

              4/22/16

              A

              5

              22

              8

              4/23/16

              A

              -3

              19

              9

              4/24/16

              A

              1

              20

               

               

               

              These are all stored numbers so you create scripts that create a record in this table every time there's a change to inventory.

              The tricky bit is ( when you make the change ) how to calculate the new Stock Total as you will need the last record.

              This can be done by having a relationship to the Product sorted by ID ( descending ).

               

              I hope i'm not making it overly complicated.