10 Replies Latest reply on Nov 16, 2009 10:22 AM by pvhaute

    Filter "0"-values from portal

    pvhaute

      Title

      Filter "0"-values from portal

      Post

      Feels like a stupid one ... but can nevertheless not find the solution :smileysad:

       

      I have a table with a record for each inventory-movement.

      I would like to show a portal where i only show the batchnumbers (1 article can have many batchnumbers, a batchnumber can have many inventory-movement-entries) that are still available in stock (quantity <>0, can be negative as well!).

       

      Can anybody help?

       

      Thanks a lot,

      StPeter

        • 1. Re: Filter "0"-values from portal
          ninja
            

          Howdy St.Peter,

           

          A question and an approach to use if my assumptions on the answers are correct.

           

          Q1: How can an inventory quantity be negative?  Does this flag an oversold situation....?  Did you create a black hole?

           

          Approach: (Child shown in portal, layout based on Parent)  (<> means "not equal")

          Have the relationship to the Child include a second criteria of <>0.  To do this, create a global field in the parent table that =0.  Write a SetField into your startup script to set it to zero whenever the Dbase is opened.

          Now add a criteria in the existing Parent-Child relationship that Parent::globalzero <> Child::Quantity.

           This should filter out the zero quantities but making them not related, but leave any non-zero number related.

           

          NOTE: Think through what this may cause for any other existing functionality...you may want to use a new child TO for this filtered portal if you need the unfiltered relationship for other tasks.

          • 2. Re: Filter "0"-values from portal
            pvhaute
              

            Thanks Ninja,

             

            Q1: yes, indicates an 'oversold' item

            Q2: build this additional criteria in the relationsship, but i get a portal with nothing in it

             

            I need 3 instances of the same table in the meanwhile:

             

            Inv1 = Parent (of layout)

            Inv2 = Child (=table of portal)

            Inv3 = source table to get the 'sum Qty' per batch (=summary field) to Inv2 (remember: a batchnumber can have multiple movements, which need to be summarised to check whether they total to 0 (=no longer available in stock) or not (in which case the batchnumber needs to be shown in the portal, with the right quantity)

             

            As i cannot compare with the 'sum-field' in Inv2 in the relationship Inv1-Inv2, i created a calculated field (in Inv2) that holds the summary qty from Inv3.

            Then i added the criteria Inv1::globalzero <> Inv2::sum_qty_Inv3.

             

            ... the result is <blank>

             

            can you help me further?

             

            thx,

            StPeter

             

            P.S. as you probably deducted by now: the relationship Inv1-Inv2 already existed as i needed a means of showing each batchnumber only once in my portal (i did this through a 'unique batchnr flag'-field) ... this is probably not the cleanest way of showing a 'unique list' in a portal so if you would happen to have a solution for this one as well it would be very appreciated.

             

            • 3. Re: Filter "0"-values from portal
              ninja
                

              Lets walk it through step by step:

               

              1: Inv1 relates to Inv2 by (Product=product)...I'll assume so.

               result: A layout based on Inv1 with a portal from Inv2 performs correctly, but shows all including zeros. This was your starting point.

               

              2: Inv1 relates to Inv2 by (Inv1:: Product = Inv2:: Product) AND (Inv1::gzero <notequal> Inv2::Sum_qty_Inv3)

                Result: nothing shows up in the portal.

               

                Questions: (A)did you enter a zero in the gzero field?  (shouldn't make a difference, but just to check)

                               (B) If you make the relationship ...AND Inv1gzero = Inv2::Sum_qty_Inv3...do only the zeros show up? (they should).

                               (C)I don't use a summary field for my filters (no real reason), I use a calc (indexed) like

              Sum(Inv2::Sum_qty_Inv3).  Does that make a difference?

               

              I know the technique works, I use it.  Let's just work out the syntax and issues together.

              • 4. Re: Filter "0"-values from portal
                pvhaute
                  

                Hi Ninja,

                 

                 

                1: Inv1 relates to Inv2 by (Product=product)...I'll assume so.

                 result: A layout based on Inv1 with a portal from Inv2 performs correctly, but shows all including zeros. This was your starting point.

                 

                correct

                 

                2: Inv1 relates to Inv2 by (Inv1:: Product = Inv2:: Product) AND (Inv1::gzero <notequal> Inv2::Sum_qty_Inv3)

                  Result: nothing shows up in the portal.

                 

                correct

                 

                 

                  Questions: (A)did you enter a zero in the gzero field?  (shouldn't make a difference, but just to check)

                 

                yes

                 

                                 (B) If you make the relationship ...AND Inv1gzero = Inv2::Sum_qty_Inv3...do only the zeros show up? (they should).

                 

                no (same result: nothing  is shown)

                 

                                 (C)I don't use a summary field for my filters (no real reason), I use a calc (indexed) like

                Sum(Inv2::Sum_qty_Inv3).  Does that make a difference?

                no difference

                 

                my experience is that this has something to do with using fields in relationships that are using the value of a field(s) from another table themselves (like in a calculation field)

                 

                (hang in there please :mansad:)   (i would include a screenshot here if i only knew how to do that)

                • 5. Re: Filter "0"-values from portal
                  ninja
                    

                  Cool, learned something new...

                   

                  As I look through all of my filtered portals and relationships I don't see a single global-to-calculation filter.  Interesting.

                   

                  I mocked up "your" database and found that you can get what you want pretty simply another way.

                   

                  Change your globalzero to a staticzero by removing the global storage,

                  setting autoenter on creation to enter zero to take casre of new records,

                  then replacing field contents of all existing records to zero. (Always make a backup first)

                   

                  For some reason, probably a good one I can't think of, the global-to-calc filter does exactly what you describe...filters everything out.

                   

                  Let me know how it's working...

                   

                  Now follow the routine posted earlier using the staticzero field in place of the globalzero field.  Sorry to initially lead you the wrong way, it wasn't intentional.

                  • 6. Re: Filter "0"-values from portal
                    pvhaute
                      

                    I'm sorry Ninja ... but i'm still getting the same result (=nothing in the portal)

                     

                    I still think it has to do with using a reference-to-another-table (=sum of qtities) as match-field of the relationship.
                    Did you try with 3 selfjoining tables?

                     


                    • 7. Re: Filter "0"-values from portal
                      ninja
                        

                      Howdy StPeter,

                       

                      I've mocked this up a number of different ways, most work some don't.  I'm figuring you're on one of the ways that doesn't.

                       

                      On your double join:

                       

                      Inv1::Item = Inv2::Item

                      AND

                      Inv1::sum <> Inv2::zerofield

                       

                      I found that the sum field in the relationship must be on the same table that the layout is based on.  If the layout is based on Inv1, then the above works.  If the layout is based on Inv2, then the second relationship condition must be

                      Inv1::zerofield <> Inv2::sum.

                       

                      I suppose this makes sense...the calc updates for the record you're currently on, from the perspective of the layout's table, and not over at the child side.

                       

                      You reference inventory movements...but I see no reference above to where these inventory movements take place.  Is each "movement" a different record in Inv?  If so, the double relationship will work but you'll get all-or-nothing portal filling.  Shouldn't there be a transactions table involved here somewhere?  Where is that table?

                      • 8. Re: Filter "0"-values from portal
                        pvhaute
                          

                        Hi Ninja ... you're a darn clever guy!!

                        You hit the nail on the head ... and i didn't really help in visualising, did i?

                         

                        Anyway: you're right in your setup and conclusion: it's all or nothing (in the child-portal) ... which is not really the intention.

                         

                        Allow me to explain a bit (as i need your help to sort this out i'm afraid):

                         

                        The inventory-movement-file ("Inv") stores each stock-change on a level ArticleNr-BatchNr-PackageId ("ArBaPa") in a different record.

                        The file, with its most important fields, would look like this (GLO stands for Global-fieldtype):

                         

                        ArBaPa         TransactionDate       Quantity       GLO_Yes       ArBaPa_unique      GLO_ReportDate

                        Apple11A       01.06.2009            200            Yes           Yes                30.9.2009

                         

                        Orange12A      13.10.2009            -100           Yes           Yes                30.9.2009

                        Lemon11B       20.09.2009            50             Yes           Yes                30.9.2009

                        Apple11A       21.09.2009            -25            Yes           No                 30.9.2009

                        Lemon11B       23.09.2009            -50            Yes           No                 30.9.2009

                         

                        What i would like to see is a portal with a line for each (unique) ArBaPa for which the inventory is not zero.

                         

                        I need to show ONLY the unique ArBaPa ... hence i need a relationship in which i match the GLO_Yes and the ArBaPa_unique field.

                        Now i still need to tell FM that i only want those ArBaPa that have a non-zero sum Quantity considering all transactions before the GLO_ReportDate.

                        ... and here our "all or nothing" situation kicks in

                         

                        Can you help me any further now that you know this?

                         

                        Thanks a lot for all your effort so far!!

                        Piet

                         

                         

                         

                         

                         

                           

                        • 9. Re: Filter "0"-values from portal
                          ninja
                            

                          Ah,

                           

                          Inv IS the transaction table.  Got it.  I see "Inv" and think "Inventory" and figured it was an overview table.

                           

                          Q1: What other tables are around in your database...we'll likely need one of them. 

                           

                          Q2a: Is there some sort of overview table that is linked to an occurrence of Inv? 

                          Q2b: When you want an overview of all the transactions of "Apple11A", what table is that layout based on?

                          Q2c: Is it a list view based on Inv, or is it a portal in a lyout based on an overview table?

                          Q2d: Where do you store the pertinent data about Apple11A so that you can tell the difference between it and Lemon11B? 

                          Q2e: What is that Table name and how do you have it connected to Inv?

                           

                          It needs more questions before thorough answers can be assembled...

                          • 10. Re: Filter "0"-values from portal
                            pvhaute
                              

                            (pardon me the disappearance Ninja ... had to solve other things first)

                             

                            Q1: What other tables are around in your database...we'll likely need one of them. 

                             

                            In the inventory.fp7 -file there are 5 tables:

                            - inventory (main table with a record for each inventory-move)

                            - production (table with internal production orders = to transform article A into article B)

                            - refill (table with internal refill orders = to transform article A in Drums to (article A) in Cannisters)

                            - reception (table with incoming-goods = receipts)

                            - inventory_count  (finance technical table to store a snapshot of the inventory for physical stock-count purposes)

                            Does this help you in any way? (sorry, don't see it)

                             

                             

                            Q2a: Is there some sort of overview table that is linked to an occurrence of Inv? 

                             

                            No, with the exception of a self-joint to filter out the 'unique' ArBaPa as described above.

                             

                             

                            Q2b: When you want an overview of all the transactions of "Apple11A", what table is that layout based on?

                             

                            An instance of 'inventory'.

                             

                             

                            Q2c: Is it a list view based on Inv, or is it a portal in a lyout based on an overview table?

                             

                            Both are needed (i think), but let's first tackle the view on-screen ... for his the inventory should be presented in a portal.  The 'overview table' you're talking about is probably the self-join (see above)

                             

                             

                            Q2d: Where do you store the pertinent data about Apple11A so that you can tell the difference between it and Lemon11B? 

                             

                            Don't know if i understand your question correctly.

                            All records in the 'inventory'-table have a field called 'ArtNr'.  The attributes of each 'ArtNr' can be found in the Article-table.  In the inventory file i can make the difference between the records for Apple11A and those for Lemon11B based on the field 'ArtNr' which would be 'Apple' for the former and 'Lemon' for the second.

                             

                             

                            Q2e: What is that Table name and how do you have it connected to Inv?

                             

                            The Article-table is basically used to lookup the name of the ArtNr, but that's all.

                            I think i know where you are heading at: you would use a layout based on the Article-table, and use a portal based on the Inventory-table (which is linked to the Article-table).  There is an issue there though: i don't want to see the quantity in stock per ArtNr, but i want to see the quantity in stock per ArtNr + BatchNr + PackageType (=ArBaPa).  In the Article-table the key-field is 'ArtNr', in the Inventory-file the key-field is 'ArBaPa'.

                             

                            Did i make it more complicated now?

                             

                            Thanks for your thinking here!!

                             

                            Rgds,