7 Replies Latest reply on Jul 30, 2016 1:32 AM by Storganise

    help on filter out records

    tech@phillipit

      Hi,

      I have a table with some records. in regular layout all records will be shown one by one. Now I want to create one special layout only shows some of these records base on one specific condition, like some field is empty or the example below.

       

      For example,

      table A has: ID;  NAME; QTY_NEEDED;  QTY_INSTOCK;  _fk_TABLE_B_ID

      table B has: ID;  TIME_RECEIVING;  QTY

       

      tableA::QTY_INSTOCK  =  SUM(tableB::QTY)

       

      I want a layout for table A which only shows records with qty_needed greater than qty_instock.

       

      thanks advance

       

      Greg

        • 1. Re: help on filter out records
          philipHPG

          How many records do you have in table A?

           

          Do you want to see multiple records in one layout (as opposed to your existing layout that shows the records one-by-one)? If so, you can do that through a portal (and use the portal filter to specify the criteria) or a List View.

           

          Or do you still want to see records one-by-one, but just a subset of all the records. If so, one way to accomplish this is to use an OnLayoutEnter script trigger that Shows All Records and then cycles through the records omitting any where qty_needed is less than or equal to qty_instock. However, if you have a large number of records in Table A this could be a time-consuming process.

           

          Please give us more information about your exact needs.

          • 2. Re: help on filter out records
            philmodjunk

            Don't forget that your script can simply perform a find to produce the desired found set of records. This can be much faster than looping thru records to see which should be omitted one record at a time.

            • 3. Re: help on filter out records
              Storganise

              HI Greg,

               

              Further to the Phils' ideas, the other way of sorting this out is by making a self-join table occurrence in your Relationships Graph. You can join Table A to Table A Copy with criteria of qty_needed > qty_inStock. Then create a portal based on Table A Copy and you'll only see the records you want.

               

              James

              1 of 1 people found this helpful
              • 4. Re: help on filter out records
                philipHPG

                Yes, performing a find in a script is generally much faster and a better approach, but you are limited on what kinds of find you can perform that way. The original poster asked for a find based on a comparison of two fields (qty_needed > qty_instock).

                 

                Along those lines, an option would be to create a calculation field in table A: qty_needed > qty_instock and then do a find based on that field.

                • 5. Re: help on filter out records
                  tech@phillipit

                  really appreciate your advice! That is a great help.

                  I think I have the idea.

                  • 6. Re: help on filter out records
                    philmodjunk

                    The original poster asked for a find based on a comparison of two fields (qty_needed > qty_instock).

                    It's also possible to define an unstored calculation for that, or, in some transactional systems, you might have a stored In stock quantity already there as part of an update that takes place when the quantity needed is specified.

                     

                    If using an unstored calculation field, performance can be unacceptably slow as the total number of records grow, but one trick that has worked for me in the past is to use a two stage find:

                    Stage one narrows the found set as much as possible specifying criteria only in indexed fields.

                    Stage two returns to find mode, specifies criteria in unindexed fields (such as unstored calc fields) and then constrains the found set.

                     

                    If stage 1 can reliably produce a fairly small found set, the two stage find can be many times more rapid that specifying find criteria in an unindexed field and peforming a find.

                    1 of 1 people found this helpful
                    • 7. Re: help on filter out records
                      Storganise

                      Hi Philmodjunk, That's a really simple, obvious, and fine way of fixing a problem I have been troubling myself with for weeks! I'm off to sort that out right now! Many thanks. James