1 2 Previous Next 16 Replies Latest reply on Jul 18, 2012 3:13 PM by fred@kca-inc.com

    Multiple filters for one portal

    JillX

      Title

      Multiple filters for one portal

      Post

      I have table E, with fields look like following:

      Factory Model OrderNumber
      a           a1           1
      a           a1           2
      a           a2           1
      b           b1           1
      b           b2           2
      b           b1           2
      c           c1            1
      c           c2            1

      I would like to be able to filter with factory, model and Order Number through a portal built in another related table.
      How can I achieve more than one filter for a portal?

      Thank you!

        • 1. Re: Multiple filters for one portal
          philmodjunk

          A portal filter expression can use AND, OR, XOR to combine terms that compare Factory, model and Order Number values. How you would create that filter expression depends on what you want the user to be able to see in this portal.

          Do you want to see all records with the same Factory and model and OrderNumber?

          Do you want to see same factory or model or OrderNumber?

          Come combination of the two?

          And you'll need to think about how the user will specify the filter criteria to be used. (click a button, checkboxes, radio buttons....)

          • 2. Re: Multiple filters for one portal
            JillX

            I think it will be combination of two.

            Can I have drop boxes on top, selecting the factory, model, OrderNumbers for portal to filter data?

            • 3. Re: Multiple filters for one portal
              philmodjunk

              Yes, if you use global fields for each.

              What type of relationship do you want for the portal, before you start filtering out records? Do you want to see all records in the table until a user selects values from one or more of these fields?

              Will the portal display records from E or a different table? (I originally understood that the layout would be based on E and the records in the portal would be from a different table.)

              • 4. Re: Multiple filters for one portal
                JillX

                The portal table will be displayed in table C, has relationship with E: C::Factory=E::Factory. It's fine if the portal is blank before I select anything.

                I want to build the portal in table C to see specified information of E.

                • 5. Re: Multiple filters for one portal
                  philmodjunk

                  I had to read that a time or two to make sure I understood it. Keep in mind that layouts and tables, while named identically, are not the same thing.

                  I believe that you want a Layout to table C with a portal to Table E.

                   C::Factory=E::Factory

                  This relationship will not allow you to filter by factory as you will already be limited to the value in C::Factory. If you want the user to be able to specify a different factory and see records from E for that different factory, you'll need to use a different relationship such as:

                  C::anyfield X E::anyfield.

                  But don't rush out and create that relationship just yet as filtered portals can benefit from using just the right fields in place of "C::anyfield" in that relationship. I'm using this as an example of a relationship you may not have considered. With the X operator used in place of =, you can match all the records in E to any record in C. In terms of matching records, it does not matter what fields you use. But if we select the right fields for the layout's side of such a relationship, our filter controlled portal will update automatically each time the user changes the value of a field used in the filter expression.

                  For now, I'll assume that for a given record in C, the user wants to see all records from E for the same factory and any filtering will be to match for different values in Model and OrderNumber. If you do want to include the option for the user to select different factories, adding that option in will be fairly easy to do.

                  Define to global fields in C: gSelectedModel, gselectedOrderNumber. The 'g' is a naming convention I use to identify fields where I have specified global storage on the storage tab in field options.

                  Define this relationship for your portal:

                  C::Factory=E::Factory AND
                  C::gSelecteModel x E::anyfield AND
                  C::gSelectedOrderNumber x E::anyfield

                  You can use any field you want for E::anyfield, but be sure to include the global fields from C in the X predicates of this relationship.

                  Now define this portal filter expression:

                  C::gSelectedModel = E::Model OR C::SelectedOrderNumber = E::OrderNumber

                  You can place the gSelecteModel and gSelecteOrderNumber fields on your layout with value list formats such that a user can select values in either or both fields. If both global fields are empty, the portal will be empty too.

                  Note: this portal should update automatically any time the value in either global field is changed because we've included that field with the X operator in the portal's relationship. If you do not do that, you have to use the Refresh Window [Flush cached Join Results] Script step to force the portal to update. This can produce long and unacceptable delays in getting your layout to update in a number of situations so I've used a method that avoids that issue.

                  • 6. Re: Multiple filters for one portal
                    JillX

                    I tired with Relationship

                    C::Factory=Lineitem3::Factory AND
                    C::gSelecteModel x Lineitem3::anyfield AND
                    C::gSelectedOrderNumber x Lineitem3::anyfield

                    And I have portal filter:

                    C::gSelectedModel = Lineitem3::Model OR C::SelectedOrderNumber =Lineitem3::OrderNumber

                    But I got an empty portal using this filter... What may lead to this problem?

                    This is the link to file I have now:

                    https://www.dropbox.com/s/13f44gr5k7ibuya/1.fmp12

                    Thank you so much!!

                    • 7. Re: Multiple filters for one portal
                      philmodjunk

                      The problem lies with the LineItem::Factory field. It's an unstored calculation:

                      List(Company 2::Company)

                      An unstored calcualtion field cannot be used on the portal's side of the relationship. It must be a stored relationship. This calculation cannot be stored as it refers to a field in a related table.

                      Why do you need a list of companies in this field? You can't order the same item from two or more companies so I'm puzzled as to why you need a list.

                      The work around for this is to use an auto-entered calculation so that your field can be stored and indexed. This may in turn require a script to update this field any time a change to data in the company table requires such an update of the values in the lineitem table.

                      • 8. Re: Multiple filters for one portal
                        JillX

                        You concern is right, one OrderNumber to one factory makes more sense here.

                        The reason I need a field of factory is there was no information about factory I can refer to in one Order: In Order table, facotry is a global field. In the original Lineitem table, I only have model information without a facotry. While If I need to make payment, I pay to the factory. That's why I need to specify a facotry field for each order.

                        Further more, I need to see what kind of Orders I placed with one facotry, so I thought I need to add a factory field in Lineitem table to allow me filter the orders I wanted.

                        For Order table, how can I generate stored factory information if I have only one facotry under one ordernumber?

                        Thank you!

                        • 9. Re: Multiple filters for one portal
                          JillX

                          And for Lineitem Table, how can I get a stored calculation of facotry field?

                          Thanks again!

                          • 10. Re: Multiple filters for one portal
                            philmodjunk

                            To repeat from my last post:

                            The work around for this is to use an auto-entered calculation so that your field can be stored and indexed. This may in turn require a script to update this field any time a change to data in the company table requires such an update of the values in the lineitem table.

                            Since you can now just enter a single value, the looked up value option can also be used to copy the data into this field.

                            But you might also consider this option for the portal:

                            C::gSelecteModel x Lineitem3::anyfield AND
                            C::gSelectedOrderNumber x Lineitem3::anyfield

                            But with this Portal filter:

                            C::Factory = Company 3::Company and ( C::gSelectedModel = Lineitem3::Model OR C::SelectedOrderNumber =Lineitem3::OrderNumber )

                            You'd link Company 3 to LineItems 3 by the Model field.

                            • 11. Re: Multiple filters for one portal
                              JillX

                              I have a question with Portal filter, why not use

                              C::Factory=Lineitem3::Company

                              instead of C::Factory = Company3::Company?

                              • 12. Re: Multiple filters for one portal
                                JillX

                                If I have filter:

                                C::Company = C3::Company  and  (C::gSelectedModel = LineItem 3::Model  or  C::gSelectedOrderNumber = LineItem 3::OrderNumber)

                                It will first select same value's company from Lineitems and then select those either have same model or OrderNumber as selected, is my understanding correct? Please correct me if I'm mistaken.

                                Now I'm trying with Portal

                                C::Company = Lineitem3::Company  and  (C::gSelectedModel = LineItem 3::Model  or  C::gSelectedOrderNumber = LineItem 3::OrderNumber)

                                And I tried with additional Portal to filter Model independentaly.

                                What should I do if I want to filter Company, Model and OrderNumber independently from each other, is this workable on the same portal, or shall i add different portals to achieve this?

                                Thank you very much!!

                                Link is here :  https://www.dropbox.com/s/13f44gr5k7ibuya/1.fmp12

                                 

                                 

                                • 13. Re: Multiple filters for one portal
                                  philmodjunk

                                  I have a question with Portal filter, why not use

                                  C::Factory=Lineitem3::Company

                                  instead of C::Factory = Company3::Company?

                                  You can, as long as Lineitem3::company is a stored, indexed field. It can't be a calculation field that copies data from the company table. This means that it would have to be a data field that auto-enters the company. Using a reference to Company3::Company eliminates the need for a company field in LineItem and, unlike the auto-entered data in lineItem, will automatically update if you change the data in the company table.

                                  If I have filter:... is my understanding correct?

                                  Yes, and this matches the behavior you'd expect to get if you only used the terms in parenthesis, but defined a relationshp that matched by company. The first part, outside of the parenthesis is something I added so that a relationship based on the cartesian join operator (X) filters down to just records matching by company.

                                  What should I do if I want to filter Company, Model and OrderNumber independently from each other,

                                  With the given expression, Model and OrderNumber are already independent of each other, but everying is then limited to records for one company. To see records for a given company or a model or an ordernumber, you could use this expression:

                                  C::Company = Lineitem3::Company  or  C::gSelectedModel = LineItem 3::Model  or  C::gSelectedOrderNumber = LineItem 3::OrderNumber

                                  • 14. Re: Multiple filters for one portal
                                    JillX

                                    I think filter

                                    C::Company = Lineitem3::Company  or  C::gSelectedModel = LineItem 3::Model  or  C::gSelectedOrderNumber = LineItem 3::OrderNumber

                                    will give me all records match any of the above three, while I was thinking to filter flexiblely as combination of three.

                                    For example, I may want to filter Company only, or Company and Model, Or OrderNumber and Model.

                                    It's like choose 1,2 or 3 from company, model and OrderNumber.

                                    Now I have Company, Model and ON, as long as three of them all with value in the select box, I can not filter 1 or 2, I can only filter with three conditions in this portal.

                                    If I want to achieve the flexible filter I mentioned above in one portal, is this possible?

                                    I know I'm struggling with one question for a long time, thank you so much Phil for your time and patience and knowledge.

                                    Thank you!

                                    Link: https://www.dropbox.com/s/13f44gr5k7ibuya/1.fmp12

                                    1 2 Previous Next