10 Replies Latest reply on Jul 23, 2012 8:26 AM by philmodjunk

    Exporting Records Based on Dates

    AlastairMcInnes

      Title

      Exporting Records Based on Dates

      Post

      I have a layout based on two linked tables - one holding book Title information and the other holding information specific to a particular edition of the book (hardback, paperback, kindle, etc.) The two tables are linked on the relationships graph by Titles::TitleID = Editions::TitleID.

      I need to be able to export records which have changed since the last time the export was run in order to update an external database.

      I have created a "last exported" global field in a table called (imaginatively enough) Globals.

      The export needs to be run in two parts as I have to export Retail Books and Trade Books separately. There are fields on the layout detailing which are which - they're text fields displayed as radio button yes/no options.

      So what I'm trying to do is find the records where either the Titles or Editions record was modifed since the last export and where the Retail flag is set to Yes.

      I have created a form based on my Globals table which has buttons on it to run the export routine - this form is opened from the main form by the user pressing a button. I didn't want to clutter up the main form with multiple export buttons. 

      My script is as follows:

      #Run when the user presses the Retail Export button.
      Freeze Window
      Go to Layout [ “Browse Titles” (Titles) ]
      Enter Find Mode [ ]
      Set Field [ Editions::Retail; "Yes" ]
      Set Field [ Editions::ModifiedDate; " > " & Globals::LastRetailExport ]
      Duplicate Record/Request
      Set Field [ Titles::ModifiedDate; " > " & Globals::LastRetailExport ]
      Set Error Capture [ On ]
      Perform Find/Replace [ ]
      [ No dialog ]
      Set Variable [ $ErrCode; Value:Get ( LastError ) ]
      If [ $ErrCode ≠ 0 ]
      Go to Layout [ original layout ]
      Show Custom Dialog [ Title: "Retail Export"; Message: "An error has occurred during the export." & ¶ & ¶ & "Error Code = " &
      $ErrCode; Buttons: “OK” ]
      Halt Script
      End If
      Export Records [ FileName: ... ]
      [ No dialog ]
      Insert Current Time [ Globals::LastRetailExport ]
      [ Select ]
      Go to Layout [ original layout ]
      I have removed all the filespec stuff from the Export Records step for brevity.
      The script is failing at the error check - reporting an error of 5. When I try to manually follow the script through, it does the find without any problems.
      Can anyone see where I've gone wrong or suggest a better way of doing this?
      Many thanks,
      Alastair 

        • 1. Re: Exporting Records Based on Dates
          davidanders

          Not knowing your field definitions, but supposing ModifiedDate is a Date field...

          "Set Field [ Editions::ModifiedDate; " > " & Globals::LastRetailExport ]"

          Description
           •
          The result of the calculation must match the field type. For example, you can't assign a date calculation to a container field.
           •
          The specified field doesn’t have to be on the current layout.
           •
          If the result of the calculation doesn’t match the target field type, and the validate option for the field is set to Always, the field will not be set and an error code is returned (which can be captured with the Get(LastError) function).
           •
          When possible, the Set Field script step makes the record active and leaves it active until the record is exited or committed. Scripts that use a series of Set Field script steps should group these steps together if possible, so that subsequent Set Field script steps can act on the record without having to lock the record, download and upload data, index the field, and so on, after each individual Set Field script step. These functions and record level validation are performed after the record has been exited or committed.
          • 2. Re: Exporting Records Based on Dates
            AlastairMcInnes

            Good points, David.

            The Modified Fields and the Global Last Exported field are all actually TimeStamps - I need to be able to know if a record was modified since the last export even if it was on the same day as the last export so a date field wasn't accurate enough.

            The Modified dates on the Titles and Editions records are set to Auto-enter the modification date and time with alteration during data entry prohibited but there's no validation on them at all. I can type values in to the fields when I manually enter Find Mode on the layout.

            Could you clarify what you mean by "the specified field doesn't have to be on the current layout". Originally, I was just displaying the modified dates as a merge field so that I could format them with some text. When I realised you can't enter text into a merge field, I added ordinary, textbox fields as well. I'd like not to have to do that, but I can always hide them behind something else if they're necessary.

            Anyway, thanks again for the suggestions, but I think I've got most of your points covered.

            Alastair

            • 3. Re: Exporting Records Based on Dates
              philmodjunk

              It might be helpful to temporarily remove set error capture from your script and then run it.

              And the spaces on either side of the inequality operator might be an issue, you can try removing them.

              • 4. Re: Exporting Records Based on Dates
                AlastairMcInnes

                OK, I've tried that without success, Phil.

                I've also stripped out the FreezeWindow and GoTo Original Layout lines and unchecked the "No Dialog" options. This leaves me with this:

                #Run when the user presses the Retail Export button.
                Go to Layout [ “Browse Titles” (Titles) ]
                Enter Find Mode [ ]
                Set Field [ Editions::Retail; "Yes" ]
                Set Field [ Editions::ModifiedDate; ">" & Globals::LastRetailExport ]
                Duplicate Record/Request
                Set Field [ Titles::ModifiedDate; ">" & Globals::LastRetailExport ]
                Perform Find/Replace [ ]
                Show Custom Dialog [ Message: "Find worked"; Buttons: “OK”, “Cancel” ]
                Set Variable [ $Desktop; Value:Get ( DesktopPath ) ]
                Show Custom Dialog [ Message: $Desktop; Buttons: “OK”, “Cancel” ]
                Export Records [ Character Set: “Windows (ANSI)”; ... ]
                Insert Current Time [ Globals::LastRetailExport ]
                [ Select ]
                 
                The "Find Worked" message appears.
                The Desktop message shows "/c:/users/alastair/desktop/"  which is odd - not sure what the inital "/" is for. This could be part of the problem, though it wasn't in the original script. My intention is to save the file for export on the desktop in the absence of any other directory I can be sure exists.
                I've unchecked the option on the Export Records step to Specify Output File so I'd have expected the script to show an open file dialog.
                The LastExported field in Globals isn't being updated.
                Having taken out the FreezeWindow and layout changing lines, I can now see that the two date fields are filled in but the "Retail" field isn't showing either radio button.
                The "Perform Find" doesn't seem to have run as, when I press the "Find" button, 2 records are found (doing the whole process manually finds about 2000 records).
                So, I don't think I'm much further forward.
                I'm confused about the difference between "Duplicate Request" and "New Request". I got the "Duplicate Request" from the Filemaker website and it seems to work if you do the process manually.
                Any more ideas?
                Thanks again,
                Alastair
                • 5. Re: Exporting Records Based on Dates
                  philmodjunk
                  Perform Find/Replace [ ]
                  is the wrong script step.
                  It shoudl be Perform Find []
                  • 6. Re: Exporting Records Based on Dates
                    AlastairMcInnes

                    Oh bother! Thanks Phil - Find and Find/Replace are so often on the same dialog I didn't notice the difference. Kinda obvious now you've pointed it out though.

                    Can I pick your brains about the find requests, though?

                    If I double-click on the Perform Find step, it shows me two Find Actions which it says will be executed in order.

                    The first criteria is:
                    "Editions::ModifiedDate [>1/1/2012 00:00:00.*] AND Editions::Retail: [Yes]

                    The second criteria is:
                    "Titles::ModifiedDate [>1/1/2012 00:00:00.*] AND Editions::ModifiedDate [>1/1/2012 00:00:00.*] AND Editions::Retail: [Yes]"

                    Which leads me to two questions. Firstly, what are the asterisks on the end of the times for? Secondly what I want is to search for is:

                    Retail = "Yes" AND (Editions.Modified > 1/1/2012 00:00:00 OR Titles.Modifed > 1/1/2012 00:00:00)

                    but the system appears to be replacing the OR with an AND. I need to export the record if EITHER the Title or the Edition record has been altered.

                    Incidentally, the 1/1/2012 00:00:00 is just a base date/time that I've put in the system for the initial export.

                    Thanks
                    Alastair 

                    • 7. Re: Exporting Records Based on Dates
                      AlastairMcInnes

                      OK, I've been looking further at the find requests. There are 2115 records with the Retail flag set to yes. There are 2115 records exported so, whether I understand it or not. it seems to work.

                      However, if I run the same script immediately, it ought not to export any records because no records ought to satisfy the "Modified since last export" criteria.

                      I put an If [0] line into the script just above the Perform Find step with the End If at the end of the script. I also took out the FreezeWindow line so that I can see what's happened. Two things are odd - the Retail flag's radio button is not set to Yes - perhaps a script step can't set a radio button and, as I said, the search seems to be finding the correct records so I'm not too worried about that. The second odd thing is that, once the script stops, it leaves me on the Browse layout in Find Mode with the two modified date fields filled in as I'd expect from the script. When I press the Find button on the status area, it tells me there are no records which match my search. Which is what I'd expect. I don't understand, then, why the script carries right on and exports the records anyway.

                      I should add that I've also built in this line:

                      If [Get (FoundCount) > 0] just under the Perform Find step so it ought to skip the export steps and display a dialog if nothing is found. Instead, it happily exports the records that it oughtn't to have been able to find!

                      Why is different when the script presses the Find button (by executing the Perform Find) and when I press it manually on the layout?

                      Thanks again. If this is a separate issue for a separate post, let me know and I'll start a new thread.

                      Alastair

                      • 8. Re: Exporting Records Based on Dates
                        philmodjunk

                        Some things don't add up here.

                        Your scripts show scripted finds the way I prefer to set them up. They use set field steps to set up the criteria for the find. Yet your last post describes double clicking a find step in order to inspect the criteria specified for the find.

                        Normally, you would use one method or the other, not both and since you want a different timestamp to be specified each time the script is run, I would think that you want to use the method shown in your script examples.

                        Using the stored find criteria suggests that your script no longer refers to the data in the global field that marks the date/time of last export.

                        • 9. Re: Exporting Records Based on Dates
                          AlastairMcInnes

                          I didn't put the criteria in for the Perform Find step - I was vaguely assuming that the Script interpreter was creating them from the other steps I'd already entered.

                          I tried deleting them and the script now says it can't find any modified records, which is what I'd expect.

                          I changed a value from the Editions table and the record was then caught for export. When I changed a value from the Titles table, though, the records isn't being caught by my search function. Even if I stop the script before the Perform Find step (by putting the rest of the lines inside an If [0] block) the search doesn't work by manually pressing the Find button. Any ideas about combinging the search functions?

                          I'm unclear as to the difference between a New Request and a Duplicate Request.

                          Thanks, though, for the catch on the Perform Find step - no idea where the criteria came from.

                          Alastair

                          • 10. Re: Exporting Records Based on Dates
                            philmodjunk

                            If your script step has empty brackets: Perform Find [], then there are no criteria stored with the script step that are being used. FileMaker "remembers" the last criteria used in a find so if you double click this step, you will see the last criteria used--which gives you the option to save those criteria as part of the script step. Do not do that with this script.

                            You have two tables in this relationship:

                            itles::TitleID = Editions::TitleID

                            This code:

                            Set Field [ Editions::Retail; "Yes" ]
                            Set Field [ Editions::ModifiedDate; ">" & Globals::LastRetailExport ]
                            Duplicate Record/Request
                            Set Field [ Titles::ModifiedDate; ">" & Globals::LastRetailExport ]
                            Set Error Capture [ On ]
                            Perform Find [ ]

                            Finds all records in Titles where there is a related record in additions with Retail= "Yes" and which also has a modifiedDate greater than the date in LastRetailExport.

                            The second request does not modify the foundset produced at all because it uses the same criteria PLUS specifying that the modified date in Titles also must be greater than LastRetailExport. Since any such records are already match the criteria in the first request, this additional request does not find any additional records. That's why removing/disabling those steps does not change the results produced.

                            Duplicate Request does exactly what its name says. Just like duplicate record, it makes an exact copy of any criteria in the current request and puts it in a new request--which is why the second request here is not accomplishing anything for you.

                            New Request will create a new blank request so the criteria from the previous request will not be included in this new one. If you wanted to find all Titles records where they either have a related Editions record with "yes" and modification date greater than last export date OR the modification record in Titles is greater than last export date, you should use New Record/Request instead of duplicate.

                            Please also note that you are not finding records in Editions with this script. If you have multiple Editions records linked to a given Titles record, the title record will be included in the found set if any one of the related Editions records matches the criteria you specify for the editions record.