7 Replies Latest reply on Jun 28, 2016 11:49 AM by Mike_Mitchell

    Filtered Table Occurances or SQL VIEW

    lkeyes

      Wondering how to implement a SQL VIEW type of functionality....

       

      The use-case is that I'd like to be able to have specific layouts for different subsets of the records in a single table...  and not have the user need to worry about or even see a "found set".  So far, the best I can come up with is to query for the subset when entering the layout in OnLayoutEnter so that the user is working with just the found set. But this still allows the user to enter a record that doesn't conform to the criteria for the found set.  

       

      Example:  A contact table.    I have a specific layout for contacts for Massachusetts. On entering the layout, I query for state = "MA"   

      I add a new contact, and add the state (MA), and that increments the records in the found set. 

      I add another contact and add  the  a different state (NH) and that still increments the visible records in the found set. 

       

      The effect is similar to VIEWS in a SQL database....i.e. "cursors" that behave as regular tables... but that are populated from a query on another table.

       

      Thanks for ideas. 

        • 1. Re: Filtered Table Occurances or SQL VIEW
          siplus

          In your particular example, you can have a script doing just Perform Find[] attached to the layout's OnRecordCommit.

           

          The Perform Find[] will repeat the last search. MA record just created will be found, NH just created record will disappear.

           

          BUT

           

          From an interface point of view, a user creating a new record, filling it and committing it might have unexpected reactions to the just created record disappearing from their very sight, reactions like - for example - creating it again

          • 2. Re: Filtered Table Occurances or SQL VIEW
            lkeyes

            >>>>>>>>

            From an interface point of view, a user creating a new record, filling it and committing it might have unexpected reactions to the just created record disappearing from their very sight, reactions like - for example - creating it again

            >>>>>>>

             

            In that instance, I'd put in some error checking..and not allow the record to commit, or at least ask for confirmation,  rather than having it completely disappear.   

            • 3. Re: Filtered Table Occurances or SQL VIEW
              Mike_Mitchell

              I would suggest you do entry via a scripted process (perhaps with a popover). Then, before the user can commit the record, validate it against the current criteria and halt entry if it doesn't match. Solves siplus's valid UX concern.

              • 4. Re: Filtered Table Occurances or SQL VIEW
                siplus

                the "validate it against the current criteria" is the hard part, because the criteria is a Find request. And we don't want to alter it with another.

                 

                One could:

                 

                Capture the foundCount before NewRecord;

                Capture the found records pk's in a list;

                On create of the new record, capture its pk;

                On commit of the new record and after the Perform find [] has occured, compare the foundCount with the captured one.

                If it's the same, the just created record does not belong to the specified criteria. Go to it via its stored pk, capture its contents in the globals you use in your good suggestion of a popover, delete it, go to the records that were found via the list of pk's, open the popover again with the globals already filled in and mention that the record did not meet the criteria.

                 

                Overkill ? definitely.

                • 5. Re: Filtered Table Occurances or SQL VIEW
                  Mike_Mitchell

                  The criteria in the example are pretty simple: State = "MA".

                   

                   

                  • 6. Re: Filtered Table Occurances or SQL VIEW
                    siplus

                    Yeah but we know our clients. The next find will be all males over 50 from NY driving a Porsche who never went to Paris but did visit London at least twice in the last 7 years.

                    • 7. Re: Filtered Table Occurances or SQL VIEW
                      Mike_Mitchell

                      Yes, but were they wearing purple pants?