11 Replies Latest reply on Oct 11, 2012 10:39 AM by philmodjunk

    server Export records

    jb_1

      Title

      server Export records

      Post

           HI;

           I trust to you and i hope that you help me for many problems .

           I have a layout that display a list of Cars( ID, Name, Type, Color..). Well, what I did, a script that where I chose Some Cars ( By a chekbox ); I export all records ;what it was display in the layout and other field that is in the "Table CArs"( like: Price, Number of Doors,......); in a ".XLS" file then I send it with a mail. What I need now ,how Export XLS file records in Server and which Path can I use to send that export in a mail??? I want do not chose the same cars every time, I want to choose some cars then RESET the list for onother export ;; THKS

        • 1. Re: server Export records
          philmodjunk

               You may want to read this article on server scripted import or export scripts: http://help.filemaker.com/app/answers/detail/a_id/7035/kw/export%20script%20server

               The basic method works like this:

               Perform a find for the records you want to export.
               Compute a file path for the exported records:

                  Set Variable [$Path ; value: Get ( DocumentsPath ) & "FileToEmail.Xls"] //put file name and extension you want in place of quoted text.

               Use Export Records or Save As Excel and specify $Path as the location to which to export or "save" the data.
               Use Send Mail with an SMTP server and use $Path to specify the attachment.

          • 2. Re: server Export records
            jb_1

                 "Use Export Records or Save As Excel and specify $Path as the location to which to export or "save" the data."

                 but Export records and " Save records " don't work in web publishing; users need to acces to the  DataBase from the web .Is that possible to use exprt records or save as excel ??!!

            • 3. Re: server Export records
              philmodjunk

                   But it will work as a server scheduled script and the IWP user can set up data in a table such that when the server schedule runs the script, the data is exported and emailed.

                   In another thread where you asked that question, I suggested that the only way you might get this to work is to use a server scheduled script that checks a special table of "export requests". I assumed your question here was in response to that suggestion.

              • 4. Re: server Export records
                jb_1

                     I understood your solution , by creating a new special table " export requests" , But , I have a list and I have to choose from records (checked list); was that a script that SAVE records choosed in that special tables!!?? can you give me an exemple please??!! Thaks .

                • 5. Re: server Export records
                  philmodjunk

                       The idea that I am explaining is that you would save the criteria used to select the records. I had assumed that your users would be specifying criteria such as "all cars of this make, this model and in this range of model years (or price range, or....)

                       Ths scheduled script could then recreate the find in order to export and email the results back to the user.

                       Selecting from a check list by clicking check boxes creates problems just in making the selections as two different users might be clicking check boxes at the same time.

                       The solution to that issue then also solves the problem in how you would record those selections in the "export request table".

                       Make sure that each record has a primary key field--usually this is an auto-entered serial number field.

                       Replace the check box field with a button that adds/removes a given record's primary key from a list in a global field or variable. A calculation field (If not for IWP, I'd use a conditional format on layout text), can then indicate whether or not a given record has been selected.

                       Here's the script for that button:

                       If [ ValueCount ( FilterValues ( Globals::gSelectionList ; YourTable::PrimaryKeyField ) ) // record is already selected]
                          #Remove key from list in gSelectionList
                          Set Field [Globals:::gSelectionList ; Substitute ( ¶ & Globals::gSelectionList & ¶ ; ¶ & YourTable::PrimaryKeyField & ¶ ; ¶ ) ]
                          Set Field [Globals::gSelectionList ; Middle ( Globals::gSelectionList ; 2 ; Length ( Globals::gSelectionList ) - 2 ) ]
                       Else
                          #Add key to list in gSelectionList
                          Set Field [ Globals::gSelectionList ; List ( Globals::gSelectionList ; YourTable::PrimaryKeyField ) ]
                       End IF
                       Commit Records

                       And this is the calculation for the calculation field that can show that the record is or is not selected:

                       If ( ValueCount ( FilterValues ( Globals::gSelectionList ; YourTable::PrimaryKeyField ) ) ; "X" )

                       Making a record in the export request table simply requires using set field to copy the contents of Globals::gSelectionList to a field in that table.

                       Your scheduled script can then use Go To Related Records to bring back this list of selected records for export if you define a relationship matching the field in Export Requests to the primary key field in your table.

                  • 6. Re: server Export records
                    jb_1

                         ""

                         Making a record in the export request table simply requires using set field to copy the contents of Globals::gSelectionList to a field in that table.

                         Your scheduled script can then use Go To Related Records to bring back this list of selected records for export if you define a relationship matching the field in Export Requests to the primary key field in your table.""

                         I Can't inderstand what you say whit that. the boutton work correctly but , I Can't export records from table é" Export request" ; well I add two table ; "Globals" where I ahve gSelectionListe and "Export Request" whre I add a global field that contain reciords from "Global" table!! what would you say by" Matching the field in export Request to the primary key field in your layout?? !! yhanks

                          

                    • 7. Re: server Export records
                      philmodjunk
                           

                                I Can't export records from table é" Export request"

                           You don't export data from the Export Request table. Your scheduled script performs a find on this table to find any records added since the last time the shedule ran. There should be one new record in the table for each request made by an IWP user. The script then loops through these records, using the data stored in the record to pull up a found set of records from the table of data from which you originally intended to export data. It then exports the data to an allowed location on the server, attaches the file to an email and uses Send Mail with an SMTP mail server to send out the email.

                      • 8. Re: server Export records
                        jb_1

                             I Thak you for your help and to taking time to answer my questions .. But Im sorry , I don't understood you , after creating boutton in place for chek box, and add 2 tables " Globals" and "exports Requests" , what relations between this tables and what I have to do ??!!! I m beginer in filemaker please :)

                        • 9. Re: server Export records
                          philmodjunk

                               I did warn you that this is not a simple thing to set up.

                               Fields with global storage can be accessed from any layout or script in your file. No relationship to the field's table is needed for them. Thus, you do not need any relationships to Globals.

                               If you have a table named "cars" from which you want to export data with a field named __pkCarID defined in it as an auto-entered serial number to uniquely identify each record in cars, this would be the field your script adds to or removes from the global gSelectionList field in Globals and which is then copied to a field in Export Request. Let us say that the field in Export Request is called SelectionList. Then your script can be:

                               Go to Layout [Export Request]
                               New Record/Request
                               Set field [ExportRequest::SelectionList ; Globals::gSelectionList]

                               At this point the user would still need to enter their email address so that the scheduled export script can email the attached file of exported data back to them.

                               The only specific relationship between ExportRequest and the rest of your database you would need might be this one:

                               ExportRequest::SelectionList = Cars::__pkCarID

                               This would allow the export script, working form the Export Request layout, to use Go To Related Records to pull up the found set of records in Cars so that they can be exported.

                          • 10. Re: server Export records
                            jb_1

                                 Thank you for your time and explain. Then , how can I make export from this " Export table" after copiend record in it !!??

                            • 11. Re: server Export records
                              philmodjunk

                                   Use FileMaker Server to schedule that this script run at regular intervals

                                   The script would perform a find on the Export Requests table to find all requrests records created since the last time it ran. An easy way to do this is to have the script set a value in a field each time it processes that record. Performing a find for all records where this field is empty will then find all new request records.

                                   What happens next is what I have already outlined in several previous posts.