13 Replies Latest reply on Apr 2, 2011 10:10 PM by bookalaka_1

    Portal with filter how to show all?

    bookalaka_1

      Title

      Portal with filter how to show all?

      Post

      I have a portal using a global field to find the product code which works great however if I dont have an item code in the search field the portal is of course blank. I would like to know how to show all the records and have them sorted by code or date when an individual code is not in the search field?

      Thanks for the help

        • 1. Re: Portal with filter how to show all?
          philmodjunk

          Is this FileMaker 11 that you are using?

          Assuming that it is and you have this portal filter expression:

          YourTable::Globalfield = PortalTable::Field

          change it to:

          IsEmpty ( YourTable::GlobalField ) Or ( YourTable::Globalfield = PortalTable::Field )

          • 2. Re: Portal with filter how to show all?
            LaRetta_1

            If a relationship is currently used between a global ProductCode and Products::ProductCode then filtering to EXTEND that relationship won't work.  I believe you will need to populate the global with the Code (or Codes) you wish to relate.  There are two ways to easily adjust your existing setup:

            METHOD 1:

            Keep the relationship as is based upon global to ProductCode using =.  Create a value list based upon field values - all values from your Products table (if you don't already have one).  Name the value list PRODUCTS.  Then add an auto-enter 'Calculated value' to your global field which will be:

            Case (
            IsEmpty ( gCode ) ; ValueListItems ( Get ( FileName ) ; "Products" ) ;  gCode
            )

            ... now back at the auto-enter tab, uncheck 'Do not replace existing value...'

            When you enter one Product Code, only those values will appear in the portal.  When you remove the Product Code from the global, the auto-enter (Replace) will populate the global with all Product Codes and the portal will produce 'all'

            METHOD 2:

            Keep the relationship as is based upon global to ProductCode using =.

            Add a calculation to the child table which is text with:

            ProductCode & ¶ & "-ALL-"

            Create a value list using this calculation field and attach this value list to your global field. 

            NB:  You can add Method 1 to your portal filter as well, but portal filtering will be slower than relationship filtering and it should be used only for filtering beyond primary relationships.

            • 3. Re: Portal with filter how to show all?
              bookalaka_1

              Method 1 does work but how do I not show the value list in the global field? I have as an edit box but still shows the full list?  I prefer to show a blank field when showing all records and then type in the code for the specific find. havent gotten that far yet is that method 2?

              • 4. Re: Portal with filter how to show all?
                LaRetta_1

                If method 1, leave your relationship using = but remove the auto-enter calculation and instead create a text calculation in your Main table with:

                Case (
                IsEmpty ( gCode ) ; ValueListItems ( Get ( FileName ) ; "Products" ) ;  gCode
                )

                ... then relate this calculation to your Products table instead.  Or try method 2.  Both methods are used regularly.  And filtered portals have eliminated the need of either methods in some situations.

                You never answered Phil's question so we still don't know your version.  If you use vs. 11 then you can try Phil's calculation but switch your relationship to Cartesian Product operator (use the X) instead of =.  This naturally relates all records and filters down to one Product when you type into the global field.  If you have a lot of Product records, the slow-down in speed may force you back to one of the other methods. 

                • 5. Re: Portal with filter how to show all?
                  Kays

                  heyy,

                  while u r talkin about portals, i set portal filtred by a drop down list, everything works perfectly except that when i set the drop down list filed as global the filter does not work.

                  any ideas ?

                   

                  K|Z

                   

                   

                  • 6. Re: Portal with filter how to show all?
                    philmodjunk

                    For filtering purposes, a global field used in the filter expression should evaluate correctly. You might want to post your filter expression so we can see it.

                    • 7. Re: Portal with filter how to show all?
                      bookalaka_1

                      I have my gCode as an edit box prefer not to use a drop down list. since this is used on the iphone definetly want to avoid the pain of the rotary drop down list. Currently when i have cleared the field and see all the records the value list is present in the gCodefield? Ideally I would like to see a blank field  when all records are shown. I tried creating blank spaces in the value list this didnt work.

                      What am I mising?

                      I played around with Method 2 but couldnt figure how to add the calculation?

                      ProductCode & ¶ & "-ALL-"

                      My relationship is  RecordID:log= RecordID_LogLineitems and, gCode::Log=gCodeLogLineitems

                      my global field is gCode

                      Thanks for clarificattion

                      • 8. Re: Portal with filter how to show all?
                        philmodjunk

                        This thread was focused on a portal, but you appear to describe a value list?

                        If g indicates a field with global storage, you can't have a global field on the child side of the relationship, only on the parent (the left in this example) side.

                        • 9. Re: Portal with filter how to show all?
                          LaRetta_1

                          My relationship is  RecordID:log= RecordID_LogLineitems and, gCode::Log=gCodeLogLineitems

                          Your description makes no sense; your naming convention is too difficult to understand the meaning of the tables and how they are related.

                          The left side of the :: indicates the table name and the right side indicates the field name.  You cannot have RecordID (unique auto-enter from one table) relating to RecordID (unique auto-enter in another table).  If your table is named LOG and it is related to another table called LOGITEMS, then you would indicate that similarly to:

                          LOG::pk_LogID = LOGITEMS::fk_LogID

                          The pk means Primary key (unique auto-enter number for that table) and fk means Foreign Key (the LogID from the LOG table as regular number field in your LogItems table.  But it is too confusing to understand your naming conventions.

                          In no place did I (or anyone) suggest you add blanks to your Value List.  If you don't want the drop-down on that global field, remove it and just type the Product Code into it.  Neither did you answer the question repeatedly asked ... what version of FM are you using? 

                          I feel there are deeper issues at work here ... and I wonder if you have a grasp on how basic relationships work.  I highly suggest that you upload your file somewhere and let us download it view your structure.  Otherwise, we will continue talking in circles.

                          As for method 2, we shouldn't waste time attempting further to solve the problem until we understand exactly what we are dealing with.  :^)

                          • 10. Re: Portal with filter how to show all?
                            bookalaka_1

                            First of all sorry for not posting this yes I use FM11 Advanced.   Because this layout is used on FM Touch/Iphone which doesn't support list views(It does but not modifiable) The only way I read to get a list view is with a self relating portal so in this instance I use

                            RecordID=RecordID  this is an AutoEnter Data that simply puts the word "LOG" in the record field so they match in both tables.  I  use a portal for the line items.

                            My tables( i corrected after seing your post I did have backward) is LOG (parent) and LogLineItems(Child)

                            Relationship is RecordID=RecordID and gcode=code_sup  (code is a three letters for each supplier)

                            in regards to the value list I tried to follow the instructions listed below the blank spaces I did on my own (obviously a blunder but I had the field as an edit box with no list attched and it still showed all the values? I tried out of desperation I had never come accross this before)

                            Keep the relationship as is based upon global to ProductCode using =.   Create a value list based upon field values - all values from your  Products table (if you don't already have one).  Name the value list  PRODUCTS.  Then add an auto-enter 'Calculated value' to your global  field which will beAs

                            If this doesn't clarify I can send up a duplicate file.

                            Thank you

                            • 11. Re: Portal with filter how to show all?
                              LaRetta_1

                              "RecordID=RecordID  this is an AutoEnter Data that simply puts the word "LOG" in the record field so they match in both tables"

                              Ah.  This was another piece we were missing.  You do not need the field with the word LOG in it to make all records relate to all records.  You can use, as I indicated, the Cartesian Product Operator to join ANY two fields and they don't have to even match or have a value in them.  Exceptions for fields to use in Cartesian Product Operator are containers or summary fields.  And this is also the only time that a relationship will work using globals or unstored calculations on the child side.  In fact, so I know when viewing the graph that the relationship is Cartesian, I create global fields on both sides, create the Cartesian join between the tables and then delete both globals.  The relationship is still valid without fields and the relationship lines will then both point to the heading which usually means it is broken but not in Cartesian join.

                              The global still showed all the values because of the auto-enter which (I assume) you applied to the global field as I first suggested.  I haven't used FM Touch so I have moved into unknown territory.  But, since you use vs. 11, this should work for you.

                              Join as:

                              Log::any field X LogItems::any field  (notice X Cartesian join instead of =)

                              This then relates all records to all records it is the only relationship you will need.  Since you want the global to be empty to relate to all, 1) remove the auto-enter calculation from the global, 2) remove the value list from the global field, 3) clear the global and filter the portal as Phil suggests with:

                              IsEmpty ( Log::gCode ) Or ( Log::gCode = LogItems::code_sup )

                              NOTE: I removed the extra parenth indicated in red.  They are not necessary.

                              Now when you type the three letter code in the global, your portal should filter down to that supplier.  When you blank the global, all Log records should display.  If speed becomes an issue, you will want to remove the Cartesian, remove the filter and use only the global field as I've outlined in one of the two methods above.

                              • 12. Re: Portal with filter how to show all?
                                bookalaka_1

                                Looking good and have followed your instructions clearing the gCode although confused on where to put Phils expression?

                                Phils portal filter expression:

                                YourTable::Globalfield = PortalTable::Field

                                change it to:

                                IsEmpty ( YourTable::GlobalField ) Or ( YourTable::Globalfield = PortalTable::Field )

                                Where do I do this?? I created a Text Calculation field and called it cPortalField  and used  the code

                                IsEmpty ( Log::gCode ) Or   Log::gCode = LogItems::code_sup 

                                The cPortalField returns a 1 or a 0 when a supplier code is entered however the list of items remains the same.

                                "If you use vs. 11 then you can try Phil's calculation but switch your  relationship to Cartesian Product operator (use the X) instead of =.   This naturally relates all records and filters down to one Product when  you type into the global field."

                                So close just  don't have a handle on this process yet. Thanks for the clarification of the Cartesian join is very helpful

                                Can I upload a file to you?