7 Replies Latest reply on Feb 25, 2013 12:26 PM by philmodjunk

    Exporting portal records

    EP

      Title

      Exporting portal records

      Post

           I have a dashboard with multiple portals on it.  I am trying to create buttons for each portal that will create reports.  The first portal I am starting with has 3 global fields in which the portal is filtered by:

           Global field #1 is called gType; #2 is called gStartDate; #3 is called gEndDate

           These fields allow the user to sort the protal by date range (using g fields #2 and #3), as well as by "type" (new, used, damaged).  Based on these settings, portal records are shown to match the "Type" selected by the user.

           I would like to create a script that generates a report by the date range selected, HOWEVER, I want the report to show each "Type" listed separately for that date range.  Is this possible?  If so, can anyone help me with the script?

           P.S. a PDF is desirable, but excel is okay as well.

           Thanks!

        • 1. Re: Exporting portal records
          philmodjunk

               Note: I am assuming that you may have multiple records of the same "type" for a given date range.

               You don't need to export any records.

               A script can use the same three global fields to perform a find on a layout based on the portal's table.

               The results can then be sorted by Type to group the records by type.

               This layout can be set up with a sub summary layout part "when sorted by type"

               If needed, summary fields placed in this sub summary layout part can display sub totals, averages etc if such are needed

               And your script can use Save As Pdf with this result on this layout to produce a PDF of your report.

               Here are some links that may prove helpful:

               Examples of scripted finds that use global fields: Scripted Find Examples

               Scripting Save As PDF: Found Sets to PDF with unique file names

               Creating a summary report: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Exporting portal records
            EP

                 Thanks Phil.  I should have mentioned that my gType field uses a value list.  The portal filters based on the type chosen in the gType field, but there is no actual "type" field in the portal- there is a field called "method" and the portal filters via a calc.  I think I should maybe create a calc field in the table called cType so I actually have a type field.  Do you agree?

            • 3. Re: Exporting portal records
              philmodjunk

                   Probably not, but I'd need to see the calculation.

                   A scripted find can use the value of the global type field to specify search criteria in the method field with a set field step. The calculation you use in the portal filter might be adapted for use in the set field step.

              • 4. Re: Exporting portal records
                EP

                     In the scripted find, would I be able to set the value of gType using set field, then use set field to specify a different value for gType and have both show up on the report? For example, the report would show the results for 2 different "types."

                • 5. Re: Exporting portal records
                  philmodjunk

                       You would not modify the value of gType, but a list of values in gType can be parsed into different requests in the scripted find to find records that match any one of the values listed in that field.

                       Say you have two values separated by returns in gType: Apple, Orange and you want to find all records with the value "apple" or the value "orange" in a field named fruit:

                       Enter FInd Mode []
                       Loop
                         Set Variable [$K ; value: $K + 1 ]
                         Set FIeld [ YourTable::Fruit ; GetValue ( YourTable::gType ; $K ) ]
                         Exit Loop if [$K > ValueCount ( YourTable::gType ) ]
                         New Record/Request
                       End Loop
                       Set Error Capture [on]
                       Perform Find []

                  • 6. Re: Exporting portal records
                    EP

                         Got it all working except for date range filtering.  I have 2 globals gDateStart and gDateEnd.  Not sure where to implement this in my scripted find.  Any advice? (Let's make believe I have a field called SaleDate that I want to fall within the date range).  Thanks

                    • 7. Re: Exporting portal records
                      philmodjunk

                           Did you see the example of a date range find in this thread that I recomended earlier?

                           Examples of scripted finds that use global fields: Scripted Find Examples

                           Here's a modified script that generates multiple requests with a date range criterion specified in each request:

                           Enter FInd Mode []
                      Set Field [YourTable::Date ; gDateStar & "..." & gDateEnd ]
                           Loop
                             Set Variable [$K ; value: $K + 1 ]
                             Set FIeld [ YourTable::Fruit ; GetValue ( YourTable::gType ; $K ) ]
                             Exit Loop if [$K > ValueCount ( YourTable::gType ) ]
                             Duplicate Record/Request
                           End Loop
                           Set Error Capture [on]