5 Replies Latest reply on Mar 16, 2009 2:22 PM by philmodjunk

    Portal searched solved....

    synergy46

      Title

      Portal searched solved....

      Post

       

      I figured it out:  If you want to read the problem; read on.  The solution is below.  (I guess portals area for displaying data only and you can't Find in their contents)

      I have 2 tables:

       

      Members:

      MemberID.pk

      name

      etc

       

      History:

      MemberID.fk

      Event (text  drop-down list)

      datee(date)

       

      The Event and eventDate are on a portal and work well.

      However, I need to search the Event field of the current Member, and if the string "-Dismissed" is found, to return the date of the event "-Dismissed".  

       

      I have tried this eventTriggered script:

      If [history::event="+Reinstatement (Dismissed)"]

         Set Variable[$$recordnum; Value:history::MemberID.fk]

         Enter Find Mode[]

         Set field [history::event;"-Dismissed"] // This does NOT appear to happen???

         Set field [history::MemberID.fk; $$recordnum]

      Perform Find[]

      Set Variable [$$found_date;Value:history::datee]

      set field [history::Notes; $$found_date & "  " & history::notes]  // add the 'dismissed' date to the notes field

       

      If I run a manual search by putting "-Dismissed as a search field and I get all records in the portal ????

       

       

      Solution:

      Apparently Portal records are 'read only'.  So, I had to create a separate layout with the necessary fields.  Then store the MemberID in a variable.  Find the MemberID and Event="-Dismissed".  This brings up the correct record.  Then set a varaible to the value of the date field.  Find again for the Event="+Reinstated (Dismissed)" and Member ID.  This brings up the record where I started.  Now I just SetField Notes with the value of datee & notes.   Done.

       

       

       

       

        • 1. Re: Portal searched solved....
          philmodjunk
            

          Is the script you post complete or is it enclosed in a loop that steps through each line in your portal?

           

          Going just by what I read in your post, it looks like you are searching your members table for data stored in the history table which won't get you what you want. There are numerous possible solutions depending on what layouts are present in your database and your own design requirements for your system.

           

          The simplest method is to write a script that steps through your portal row by row, looking for the "disimissed" text in order to process it. This is workable if you are dealing with small numbers of portal rows.

           

          Alternatively, you can switch to a layout that refers to the history table and create a find that specifies both the memberID and the "dismissed" text. From here you have a found set listing the desired information and you should be able to do what you need from there.

          • 2. Re: Portal searched solved....
            synergy46
              

            Thanks.

            I used the latter (creat a new layout) method.  

             

            How does the 'loop through the portal' system work?  It seems that the portals are not 'writeable' from a script???

             

             

            • 3. Re: Portal searched solved....
              philmodjunk
                

              Actually you can perform finds on portals--they just won't give you the results you need in this case. You have to keep in mind that you have to have at least two tables to have portals, one table (we'll call Parent) serves as the record source for your main layout. The second table (we'll call Child) is the one you refer to when you create your portal. When you  enter find mode and specify data in the portal you are telling Filemaker "Find all records in the Parent table that have at least one record in Child that match the specified portal criteria." Try doing some manual finds with criteria entered into the portal rows and you'll quickly get an understanding of how this works.

               

              "It seems that the portals are not 'writeable' from a script???"

               

              I use scripts to write to portals all the time. Use Set Field [PortalrelationshipName::FieldName] and you can put data write into a portal row in your record. There are a variety of portal manipulation script steps you can use in scripts, including Go To Portal Row, which can be used to specify the specific portal row and which you can use to "loop through the portal".

               

              Look them up in the Help files and try them out.

              • 4. Re: Portal searched solved....
                synergy46
                  

                Using a manual 'find' results in all records in the portal being shown. 

                 

                And, I have had no 'luck' in writing to the portal with setfield.  Yes, I checked and made sure my field was writeable and it is.  

                 

                What has worked so far is creating a separate 'find' layout.  Collect the MemberID.fk, Enter Find, SetField in the MemberID.fk...Specify the Setfield(Event) as "+Reinstated (Dismissed" and then Perform Find.  This works and successfully locates all the desired Event records for a Member.  However, if there are multiple rows/records in the Found Set, there doesn't seem to be an 'elegant' way to move to successive records and specific fields.  

                 

                Onward through the fog.

                 

                Thanks for your ideas.

                 

                ron

                 

                I haven't used/tried 'goto portal row' yet.  

                • 5. Re: Portal searched solved....
                  philmodjunk
                    

                  "Using a manual 'find' results in all records in the portal being shown. " That's exactly right. When you perform a find on your layout. You aren't searching for the records in the portal. You are searching for records in the layout's table. Any criteria that you enter into a portal will help determine what records will be found in the main, "parent" table. Once your find has pulled up those records in a found set, each found record will display all related records in their respective portals.

                   

                  As you have discovered, in order to find a specific record in the portal, you must use another method.

                   

                  Example:

                  My company has an "invoice" style record in one of our Databases. The layout's main table records the customer's name, the transaction date and other information about the customer. The portal lists each item purchased from the customer in that transaction. (The items are all materials sold to a scrap metal yard.) If I enter find mode and enter "iron" in the portal's material field and today's date into the transaction date field, I will get all of today's transactions where the customer sold us some "iron". On each such record I will not only see the "iron" item but any other items that were sold to us on the same transaction. Make sense?

                   

                  "And, I have had no 'luck' in writing to the portal with set field."

                   

                  Your portal potentially displays many records, (each row is a different record). Your script has to determine which record contains the field you wish to change with a set field instruction. If you don't, your script will attempt to modify the first portal row's record that may not produce the results you want.

                   

                  Go to portal row can be used to select a specific portal row. Then use set field to modify a field in that row. You also need to be careful to select the correct table reference when specifying the target field for this step.