13 Replies Latest reply on Jul 28, 2015 7:21 AM by priyabratasahoo21

    Multiple Search Criterias in Portal

    Gilo

      Title

      Multiple Search Criterias in Portal

      Post

      Hello 

      Does FM allow us, to filter records by multiple criterias in a portal?

      I have a table, called document, with records having client_no., status, paiement. I've created a matrixfield in documents, where i get different codes style @ABA or @ABC. this works fine unless i want to see all records where status does not matter (ok AND not ok). so I would need @A*C. Is this possible via a portal relationship?

      So I need a solution where I can filter my portal which 3 criteria but where is a possibility where i can also see all records without filter. Right now my calculated field has already 60+ lines. And if a change is due, this would take hours to replace all spots on the cal. script.

       

      I do not know SQL at all, so would appreciate a FM solution. 

        • 1. Re: Multiple Search Criterias in Portal
          SteveMartino

          Looks like you need more of a list view then a portal.  But here's a simple Master/Detail with type ahead filtering of a portal. You could put a dropdown global find field at the top, and OnObjectModify trigger performs each find you are looking for.  You can also set up radio buttons (if it's only 3 criteria or so).  You can also use 3 tab panels, with 3 portals sorted the 3 different ways.

          https://dl.dropboxusercontent.com/u/71328624/MasterDetailSimple.zip

          edit:  U/N admin, PW 1234

          • 2. Re: Multiple Search Criterias in Portal
            Gilo

            Hi thanx for your reply

            Unfortunately this is not what i need. 

            I have a table documents:
            client no.       paid       status
            123                yes        ok
            434                yes        nok
            234                no          nok
            342                no          ok
            243                yes        nok

            Thus table is shown on layout menu via portal, where menu has 3 fields (menu_clientno; menu_paid; menu_status).

            If you enter menu_clientno: 243    menu_paid: yes   menu_status: nok, the portal only shows me the last line. This works fine, but i also need that if menu_clientno: ""    menu_paid: yes   menu_status: nok, it shows me all records where paid = yes and status = nok. And this i do not get to work.

            • 3. Re: Multiple Search Criterias in Portal
              philmodjunk

              Please note that when you perform a search for records and enter criteria in a portal, the find does not find records in the portal. The find finds records in the layout's table that have at least one portal record matching your criteria. Once the find is performed, the portal then shows all related records that meet any specified filter criteria, not just the portal record matching the specified criteria.

              To search for records where a field is blank, enter a lone = find operator in to that field. so in your last example you would use:

              menu_clientno: =    menu_paid: yes   menu_status: nok

              • 4. Re: Multiple Search Criterias in Portal
                Gilo

                Hi Phil

                Thanks for the reply

                Well I do not perform a search, I use fields in the menu layout, which work with a relation to show records in a portal. So the = wan't work. 

                • 5. Re: Multiple Search Criterias in Portal
                  philmodjunk

                  Then these are FILTER criteria, not SEARCH critera.

                  Please post the filter expression that you are trying to use that isn't working for you.

                  Warning: Portal FIlter expressions with multiple criteria can become very complex to set up.

                  • 6. Re: Multiple Search Criterias in Portal
                    Gilo

                    I have a layout Menu, with 3 fields menu_client menu_paid menu_status

                    I use a portal based on the table documents, where i also have doc_client doc_paid doc_status.

                    I get it to work with a matrix where i have matrixfield which generates a 3 letter code which represents the different possibilities for the 3 fields. But i don't know how to do it if I leave field blank. 

                    So for ex: if client = 123 the matrix code would look like @A

                    If client = 243 => @B

                    If client = 123 And paid = yes => @AA

                    If client = 243 and paid = yes => @BA 

                    If client = 243 and paid = no => @BB

                    But my problem is to make fm show me all paid whatever the client is. Something like @*A

                    • 7. Re: Multiple Search Criterias in Portal
                      philmodjunk

                      I don't see the purpose for the "matrix field" and you haven't explained how client ID and whether they are "paid" generates that value anyway. (And does a change in status also change this value?)

                      What I asked for was the portal filter expression  that you are using. Perhaps you are not using any filter but instead using this "matrix field" as a match field in the relationship?

                      You can set up a relationship using the Cartesian Join operator: X instead of =. Without a portal filter, this portal will list all records from the portal table.

                      Using just the client ID and "paid" in this example, here's a start on the portal filter expression that you might use:

                      ( IsEmpty ( LayoutTable::clientID ) and IsEmpty ( LayoutTable::Paid ) ) or
                      ( IsEmpty (LayoutTable::clientID ) and LayoutTable::Paid = PortalTable::Paid ) or
                      ( LayoutTable::clientID = PortalTable::clientID and IsEmpty ( LayoutTable::Paid ) or
                      ( LayoutTable::clientID = portalTable::clientID and LayoutTable::paid = portalTable::Paid )

                      Including a Status field will greatly increase the number of possible combinations to allow for.

                      BTW, you can't use wildcard operators such as * in portal filter expressions

                      • 8. Re: Multiple Search Criterias in Portal
                        Gilo

                        hi Phil

                        sorry I think I messed up with the right terms in fact i do use matching fields in a relations to show the records i need.

                        I have 3 relations

                        menu_client -< doc_client

                        menu_paid -< doc_paid

                        menu_status -< doc_status

                        this is working fine but the thing I would need is that i could leave one menu_field empty and that fm shows. me all records with the two remaining matches. like i tried to describe in the 3rd post. 

                        again sorry for the confusion

                        • 9. Re: Multiple Search Criterias in Portal
                          philmodjunk

                          It's more a case of missing information in your posts than wrong terms, but it's not working fine as it does not do what you need. You can't use a relationship to match to your records when one of the matching values might be null (empty).

                          The portal filter method, on the other hand can be set up to handle the possibility of a null value in your search.

                          You can also script finds on the portal table using a layout based on the portal table and there are ways to use the ExecuteSQL function as well.

                          • 10. Re: Multiple Search Criterias in Portal
                            Gilo

                            Hi again

                            So I started working on a filter solution for the portal. 

                            I have 4 criteria, what's the best, shortest and easiest way to write the filter code? Do i have to do a let with 16 different cases or is there a better way?

                            • 11. Re: Multiple Search Criterias in Portal
                              SteveMartino

                              If you have 4 criteria, why do you need 16 cases.  Take a look again at the sample file I originally posted.  It filters the portal as a type-ahead, but more importantly notice how the relationship is set up.  You use the same global field on each side of the relationship.  Selecting a criteria, or category with a refresh portal script step may accomplish what you are looking for. 

                              • 12. Re: Multiple Search Criterias in Portal
                                Gilo

                                Hi Steve

                                Well I can't work with a relationship.
                                You have 1 find field, I have 4 find fields which search 4 fields, like:

                                name    last name     email    phone

                                So I want to be able to find an email, so I leave name, last name and phone empty, and fm should show me the record corresponding to that email address. A relation wan't work with empty fields. 

                                My solution is - with the huge help of phil- to filter my portal with the following code:

                                IF ( Menu 2.0::menu_int  ≠  "" ; Menu 2.0::menu_int = comptants::doc_int;comptants::doc_id  ≠  comptants::doc_int ) AND IF ( Menu 2.0::menu_periode  ≠ "" ; Menu 2.0::menu_periode = comptants::doc_date_apayer_moisan ) AND IF ( Menu 2.0::menu_comptant_status  ≠ "" ; Menu 2.0::menu_comptant_status = comptants::doc_status ; comptants::doc_id  ≠  comptants::doc_int ) AND IF ( Menu 2.0::menu_status_comptant_extrait  ≠ "" ; Menu 2.0::menu_status_comptant_extrait = comptants::doc_status_extrait ; comptants::doc_id  ≠  comptants::doc_int )

                                This does what I want, I can search with 1 criteria only, leaving 3 blank, or with all 4. 

                                • 13. Re: Multiple Search Criterias in Portal
                                  priyabratasahoo21

                                  Hi,


                                  For using the portal search filtering using relationship, we can use another technique for that we need to have some new fields.
                                  We need to have the 4 new fields if we need 4 search criterias.
                                  These 4 fields will be calculation fields
                                  The calculation field will have:

                                  (Field1Value) & ¶ & "All"


                                  And for the portal relationship, use the above calculation fields to be matched with the search fields in the relationship that you use to filter the portal.
                                  And then need to default the search fields to have value as "All" as default instead of blank.