6 Replies Latest reply on Sep 5, 2016 6:51 PM by gmotta

    Searching Portal Records

    gmotta

      I"m new to File Maker...So pardon me if my question seems dumb or if I'm being very ambitious on my portal.

       

      I'm trying to create a Portal that will let me search through records dynamically.

       

      I have a table with an UserID, a Department and a Shift. On Another table I'm recording errors for each User and they are displayed on the portal whenever I search for the UserID. My question is....How do I get the portal to display all records when no User is selected...and how can I see all users from Shift 2 for example...or all UserIDs from department MOTTAPIC? Appreciate the help in advance.

       

       

        • 1. Re: Searching Portal Records
          Johan Hedman

          Then you use the Filter option in your Portal Settings.

          Unknown.png

          In the calculation you then use Global Fields from above your portal to match information in your related records

           

          If you like to learn more about FileMaker, read the free FileMaker Training Series

          FileMaker Training Series | FileMaker

          • 2. Re: Searching Portal Records
            BruceRobertson

            "I'm trying to create a Portal that will let me search through records dynamically."

             

            Generally, a portal is designed to show records which come from some kind of specific data relationship.

            A filter can be used to further refine (restrict) which of those related records to actually display.

            Also, although it can be done, generally, it is not a good ideal to use a portal to point to "everything", especially if the set of related records numbers into the thousands.

             

            A portal is not the place for performing general finds. A portal STARTS with a subset of related records.

            (Example, the history of invoices for this customer).

             

            What is the nature of this relationship? What is the parent record?

            • 3. Re: Searching Portal Records
              gmotta

              Thanks Johan!!!! I managed to make it work using the Global field and the filter option. And yes...I agree with you Bruce, it's probalby a better idea not to show all records.

               

              But now I'm running into a different problem...how do I count the values that are filtered? Now my count is counting all my records...thanks.

              • 4. Re: Searching Portal Records
                gmotta

                I'm managing to count the filtered values now via an executeSQL! Man this  is awesome!!!

                • 5. Re: Searching Portal Records
                  BruceRobertson

                  "I'm managing to count the filtered values now via an executeSQL! Man this  is awesome!!!"

                  Well; that seems very unlikely.

                  ExecuteSQL knows nothing about interface and found set related constraints, including such things as portal filters.

                  • 6. Re: Searching Portal Records
                    gmotta

                    Well,

                     

                    It seems to be working fine my SQL....it will count what ever filter parameter I'm searching on my portal.

                     

                    only problem is I can't get the date part to work....here is the SQL...underlined is the part I'm having trouble with....

                     

                    I'm using my portal to filter between shifts, departments, and userIDs....

                     

                    If(CDCAssociates::Global=1;ExecuteSQL ("SELECT Count (Shift) FROM CDCPPMLog WHERE Shift=? AND CDCPPMLogDate ≥ ?"; "" ; "" ;CDCAssociates::Global;CDCAssociates::SearchStartDate);

                     

                     

                    If(CDCAssociates::Global=2;ExecuteSQL ("SELECT Count (Shift) FROM CDCPPMLog WHERE Shift=?"; "" ; "" ;CDCAssociates::Global);

                     

                     

                    If(CDCAssociates::Global=3;ExecuteSQL ("SELECT Count (Shift) FROM CDCPPMLog WHERE Shift=?"; "" ; "" ;CDCAssociates::Global);

                     

                     

                    If(CDCAssociates::Global="GUTHRIERET" or CDCAssociates::Global="JENNINGSEQ" or CDCAssociates::Global="JENNINGSINV" or CDCAssociates::Global="JENNINGSREC" or CDCAssociates::Global="KANEEQ" or CDCAssociates::Global="KANEPP" or CDCAssociates::Global="LUCENTEREC" or CDCAssociates::Global="MOTTACP" or CDCAssociates::Global="MOTTAPIC" or CDCAssociates::Global="PORCHEDDUCP" or CDCAssociates::Global="PORCHEDDUPIC" or CDCAssociates::Global="VOLLRATHEQ" or CDCAssociates::Global="VOLLRATHPP" or CDCAssociates::Global="VOLLRATHREC";ExecuteSQL ("SELECT Count (Department) FROM CDCPPMLog WHERE Department=?"; "" ; "" ;CDCAssociates::Global);

                     

                     

                     

                     

                    ExecuteSQL ("SELECT Count (UserID) FROM CDCPPMLog WHERE UserID=?"; "" ; "" ;CDCAssociates::Global)))))