1 2 Previous Next 23 Replies Latest reply on May 25, 2012 11:40 AM by philmodjunk

    Search Records and viewing in Portal



      Search Records and viewing in Portal


      I am trying to create a search page for my Reqs and Items tables but I am getting no where. What I am trying to do is for Req table. I have a layout REQ-Search and have fields like Req#, PO, Date first - last to search, Firstname, Lastname, Vendor. They are all inside the Req table. I also would like a Portal at the bottom of the page that when you move to the Layout REQ-Search, the portal displays a list of the last 15 Req entered.

      Example: Say I put in a PO number and it pulls that Req information up. The information replaces the last 15 Reqs entered  in the portal at the bottom of the page and put the search result up. From the portal I can create the link to the new windows to see all the other information. Same resolute with the other search items.

      I am having many difficults with searching.

        • 1. Re: NoFields

          What relationship did you use for the portal? Have you tried using a portal filter? If so, how did that work?

          "last 15 Reqs entered" means what exactly? The last 15 Req records to be created? If so, a portal sorted by a primary key serial number in descending order that is 15 rows tall would always list the most recently created 15 records until you start manipulating data in your search fields.

          What kind of matching do you want to do in your search?

          Example 1:

          If the user types a C into the Last search name field, do you want to instantly see the portal update to show all records where the last name starts with C?

          Example 2:

          If the user types "ris" into the first name search field would you want to see records where the first name is Kris, Chris, Christopher, etc.

          Or do you want the user to fill out various fields and then click a button to see the portal update?

          It's all possible though I suggest choosing between example 1, 2 or an exact match strategy only and not try to do a combination of the two. Given the number of search fields, your portal filter expression is going to be pretty complex as it is and there can be challenges in getting the portal to update smoothly with each change to the data in your search fields.

          You may find the search portals in this demo file helpful: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

          • 2. Re: NoFields

            Also, I've been noticing the Thread title "NoFields" showing up at various times in the forum instead of the title entered by the poster. I'd like to forward more info to ModMan about how this might be happening.

            What browser and system platform did you use to post the message?

            Did you see any unusual behavior, error/warning messages whey you created this new discussion thread?

            • 3. Re: NoFields

              You need a separate table to store the searches. You can use this to store all of your searches and add a field to identify the search.

              Create a field for each field you want to search in the other table. Match field names helps.

              The steps are:

              Go to Form Field in the search table

              New Record
              enter your values
              Commit Record

              Or find and select the record you want to use

              Set variable $_A to Field_A
              Set variable $_B to Field_B

              Go to Master table form
              Enter Find Mode
              Set Field A to $_A
              Set Field B to $_B
              Set Field X to $_X
              Perform Find 

              This creates one record in your Table Of Search Criteria and when you select a record for the find, puts the values into variables and then uses to variables to perform your find. The records in the Criteria table are not modified or changed. You can timestamp them and sort by that field to show your last searches.

              • 4. Re: NoFields

                If I could do like a live update with the portal, that would be nice. I like example 1, where you type C and the portal instantly updates.



                I am using Windows 7 64-bit, and using Firefox  12.0. I did not notice any warnings or error when it was created. I just posted instead of viewing the post.

                • 5. Re: NoFields

                  See the demo file for two simplified examples. The challenge will be in crafting a filter expression that works with all of your fields.

                  Did I correctly understand which records you want to see in the portal when you first access the layout?

                  • 6. Re: NoFields

                    Yes. When you go to the Req-Search from my welcome page, the last 15 latest created REQs are shown. So if its a newly created Req I can just click a link to move to the details listing. Or I can just search for it by one of the fields.

                    How do I get the latest 15 to show up when you get to the screen? I assume I have to do scripting. In the relationships I assume I must duplicate the Req table to ref back to it.

                    ? Where are the demo files at?


                    Is there somewhere I can get more fimilar with I guess this is advance scripting? I have gone through the Lynda videos but its not as indepth to what I am trying to create. I though I was creating a simple DB but I guess not.

                    • 7. Re: NoFields

                      For the Demo files, see the link at the end of my first post in this thread. OR click this new one (I'm starting to move all my shared Filemaker Support files to drop box.):  https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

                      Just to show the most recent 15 records does not require a script.

                      If you have this relationship:

                      SearchLayoutTableOccurrence::anyfield X ReqTable::anyField

                      You can sort either the portal or the relationship to list the most recently created records first. If you have an auto-entered serial number field in the ReqTable, you can sort on this field in descending order. A field that auto-enters a timestamp on creation could also work for this.

                      • 8. Re: NoFields

                        A lazy guy skips reading all of the posts and suggests this:

                        Use a portal and sort it by either date or record number in such a manner as to put the newest at the top of the portal.

                        Now, size the portal for 15 rows. Forget all the rest, there's your 15 records. You can leave the scroll bar off if you really don't want to look at the rest. KISS...

                        The portal row can be made a button to run your search as described above.

                        Note that the portal can show up in any form in any table if you add a TO to the table with the search records and make the join and X so that all records show. Then sort to portal as above.

                        This is so much easier...

                        • 9. Re: NoFields

                          Jack, When I tried what you explained I only was able to get the one record for the layout. I had the 15 portals their but was unable to view the rest of the fields.


                          PhilModJunk, the example helped. The global option was what I was missing to get the records to appear in the portal to show the latest records entered when entering the page. I needed "tblReq::gsearchReq X tblReq2::__pkReqIndex" to make it all work.


                          Now that that is working, how do I get the search fields to work and change the portal to what I am looking for? From where should the fields be from? When I take the fields from the tblReq, it is trying to create instead of search. I have tried in Browse and Find mode.

                          Do I need to make a new table? and if so how do I link the 2 tables together without it trying to create a new record?

                          I dont need it to search with multiple fields, be it would be nice in another part layout thou.

                          I would like to go to the PO box and type 1204343 and in the portal it changes to the req with the PO of 1204343 in it. From there I can make a link to view the REQ. Same thing if I were to type in the REQ. If I type in Mark for first name, the Mark's appear. (That was be a spot were I would like to narrow it down to which MARK, Smith, Daniels, Sanders.)


                          • 10. Re: NoFields

                            You can do this with a complex filter expression and the use of either a case function or a lot of Or operators.

                            You can use three global fileds for PO, REQ and First name. I'll name them gPO, gREQ and gFirstName in this example:

                            case funciton:

                            Case ( IsEmpty ( YourTable::gPO & YourTable::gREQ & YourTable::gFirstName ) ; True ;
                                      YourTable::gPO = REQ::PO ; True ;
                                      YourTable::gREQ = REQ::REQ ; True ;
                                      False )

                            Using Or:

                            IsEmpty ( YourTable::gPO & YourTable::gREQ & YourTable::gFirstName ) Or
                                      YourTable::gPO = REQ::PO Or
                                      YourTable::gREQ = REQ::REQ

                            The results will be the same when you match on single values in a single field. The second expression will allow you to get two records in the portal if you enter both a PO and and REQ value that refer to different records.

                            The additional fields used in the filter will require that you either include the additional fields with X in the relationship or you use a Set field script step that set's the global field you already in the relationship with X to the value it already has to force the portal to update.

                            • 11. Re: NoFields

                              Should I start a new thread? The first problem is solved with being able to open the page and the list of REQs appear. But I am having a hell of a time trying to make my search fields work with the portal.

                              This is what I have. The Layout is called REQ_INTRO and show records from tblReq, I would like to have search function to look for REQ, PO, Fname, Lname, vendor or by date. The portal at the bottom of the page is showing all the REQ that are entered. This part it working great.

                              I can search based on Req number, but it because that is how the table is linked to the other table. I cant search based off of any of the other fields. I am currently just trying to test with REQ, PO and Lname fields. I have tried putting in both versions of the filter into the portal filter with no sucess.

                              The layout is using the fields from tblREQ::gReq, gPO, gLname.
                              The Portal Show related records from = tblReq2_4Portal

                              tblREQ2_4Portal::__pkReqIndex X tblReq::gsearchReq

                              I have tried adding:
                              tblREQ2_4Portal::REQ X tblREQ::gREQ
                              tblREQ2_4Portal::PO X tblREQ::gPO
                              and vice versa.

                              With the portal filter on, if I enter info in to gREQ field and leave the field, nothing happens to the portal, but it I put a PO in and leave the field the hole table goes blank till I delete the info.

                              I have lost alot of time on this issue.


                              • 12. Re: NoFields

                                What portal filter expression did you use?

                                • 13. Re: NoFields
                                   case funciton: Case ( IsEmpty ( YourTable::gPO & YourTable::gREQ & YourTable::gFirstName ) ; True ;           YourTable::gPO = REQ::PO ; True ;           YourTable::gREQ = REQ::REQ ; True ;           False ) Using Or: IsEmpty ( YourTable::gPO & YourTable::gREQ & YourTable::gFirstName ) Or           YourTable::gPO = REQ::PO Or           YourTable::gREQ = REQ::REQ I tried both but was using IsEmpty without case more. But was unable to get either to work.
                                  • 14. Re: NoFields

                                    I wanted the actual expression, not my suggested examples. "yourtable" would trigger a syntax error as you would need to use your tables and fields in place of mine.

                                    Also, as a test, run this script step:

                                    Refresh WIndow [flush cached join results]

                                    To make sure that you do not have a window update problem. (The script should not be needed with the right relationship using the X operator, but it's a useful test to help isolate where the trouble lies.

                                    1 2 Previous Next