9 Replies Latest reply on Aug 12, 2010 11:15 AM by philmodjunk

    Script to export and replace a Filemaker file worked in 5.5 but won't work in FileMaker 10

    rebby_575

      Title

      Script to export and replace a Filemaker file worked in 5.5 but won't work in FileMaker 10

      Post

      I recently migrated to FileMaker 10 Pro Advanced from Filemaker Pro 5.5 (yes, I know: late to the party, and boy, what a party!). 

      The only major thing that broke that I haven't been able to figure out on my own are some scripts in my main file ("MASTER.fp7" for his discussion) designed to export a FileMaker file ("CLIENT.fp7" for this discussion), overwriting the previous "CLIENT.fp7" file of the same name. Several files including the "MASTER.fp7" file use the exported "CLIENT.fp7" file for other script steps, lookups, etc. 

      There is probably a better way to accomplish what I want, but there is no time at present to re-write my entire database. 

      In both FM 5.5 and FM 10, when creating the export script, if you specify a file name to export that is the same as an existing name, a dialog offers the choice to "replace" the file. 

      In FM 5.5, choosing "replace" in the script step meant that the script would always overwrite the file, as long as the file to be overwritten was closed. Not so in FM 10. 

      Instead, I get the message, "could not be created on this disk. use a different name, make more room on the kisk, unlock it or use a different disk." There are about 200GB available for a 400k file, and the file is not locked. 

      If I delete the "CLIENT.fp7" file before running the script, it still doesn't work in FM 10 unless I quit FileMaker and restart it.

      The exported "CLIENT.fp7" file is identified in the "External Data Sources" of my "MASTER.fp7" file because it is referenced on several layouts, and I don't know of any other way to do it. 

      Is there a solution? 

      I've tried script debugger, but there is no hint there of why it doesn't work. I'm not using Web publishing or Server features. 


      Grateful for help.  

        • 1. Re: Script to export and replace a Filemaker file worked in 5.5 but won't work in FileMaker 10
          philmodjunk

          I just ran a very simple test and I was able to use a script to export records and overwrite the existing file successfully. I suspect that the Client file is actually still open but hidden from view. Before running your script, pull down the Window Menu and check the Show Window submenu and see if it might still be open in the background. Remember that filemaker will reopen a referenced file if anything in the current layout refers to it, so you may need to navigate to a blank layout, close the file and then run your export script.

          Your description of this task strongly suggests that you need to re-examine the structure and function of your database. Exporting in this fashion shouldn't be necessary for the uses you describe and there are a number of simpler, safer approaches you could use, including simply maintaining the Client table in an updated format so that such a batch export need not ever be performed.

          • 2. Re: Script to export and replace a Filemaker file worked in 5.5 but won't work in FileMaker 10
            rebby_575

            Thank you for your answer. I think you have it right, Phil, but it's still not working for me. 

            I created a blank layout with nothing on it but three buttons, one to open the "CLIENT" file, one to close it, and one to export it. Unless I quit FileMaker and reopen the "Master" file, the "CLIENT" file remains in the "show window" list with the parentheses around it, which in the olden days indicated a hidden file. 

            If a "close file" script won't close a hidden file, then what will? This explanation tells what happens, but not how to close the window: http://help.filemaker.com/app/answers/detail/a_id/685/related/1/~/opening-filemaker-files-as-hidden

            I'm working on a ground-up redo, but design time is in short supply right now. (At the current rate, it should be finished in late 2017.) I use these export files to create weekly PDF snapshots of aging "30-60-90 day" accounts, so overwriting the exported data is not a problem. It just needs to work smoother. 

            • 3. Re: Script to export and replace a Filemaker file worked in 5.5 but won't work in FileMaker 10
              philmodjunk

              Are you saying

              Go to Layout [simple layout]
              Close File [HiddenFile]

              Doesn't close the file for you so that you can export a new copy over the top of it?

              Can you select the hidden file and close it manually?

              • 4. Re: Script to export and replace a Filemaker file worked in 5.5 but won't work in FileMaker 10
                rebby_575

                Thanks again, Phil. 

                Sorry to take so long getting back. 

                Yes, "close file" didn't work. Neither did manually closing the hidden file. Only quitting and reopening the main file first makes a difference.

                I think I know why. When I wrote the export script, instead of typing the path and file name directly, I clicked the "add file" button which makes the file an "official" external data source. Just entering the path and file name directly in the export script doesn't do that, so there is no barrier to overwriting it. 

                If I have time to test it further to see if that is correct, I'll post back, but it will be a while. 

                • 5. Re: Script to export and replace a Filemaker file worked in 5.5 but won't work in FileMaker 10
                  philmodjunk

                  I've had this happen to some of my converted files. The main file insists on re-opening the secondary file every time I close it. Usually, this is because some reference in the first file links to the second file and forces it back open, but I've even set up scripts that go to a completely blank layout and then use close file--only to still be able to find it in the Show Window submenu as a hidden window...

                  • 6. Re: Script to export and replace a Filemaker file worked in 5.5 but won't work in FileMaker 10
                    rebby_575

                    I'm in the middle of a redesign, trying to create the same function within my main file rather than separate from it, as you suggested, but I've run into some problems.


                    I need to display the totals of monthly charges and payments for each client in a horizontal fashion such as the simplified version below. It's easy enough with calc fields that test for a date range to determine applicable amounts for each period, and summary fields that display the totals of each calc field. 

                    ========
                    THIS
                    Client, Current, 30days, 60days
                    Phil, +100-100, +100-100, +200 


                    DISPLAYS AS
                    Client, Current, 30days, 60days
                    Phil, 0, 0, 200
                    =======


                    The major problem is getting the "aging" to work correctly. If a client has made payment greater than the oldest charge (june), it shouldn't be displayed regardless of the month it was paid, as in this example: 


                    =======
                    Similar to the example above, Phil paid his June bill in August, and added more charges in July and August, but I need it to display in aging form.
                    THIS
                    Client, Current, 30days, 60days
                    Phil, +100-200, +100, +200 


                    SHOULD DISPLAY AS
                    Client, Current, 30days, 60days
                    Phil, 100, 100, 0

                    ... because he has nothing in the 60 days column, but still owes 100 from 30 days, and has a current charge of 100
                    =======
                    Any ideas? It seems like comparing the total charges - payments to monthly totals would work, but I can't get my head around it. There are actually five periods to work with. 

                    • 7. Re: Script to export and replace a Filemaker file worked in 5.5 but won't work in FileMaker 10
                      philmodjunk

                      Can't go into details as I don't know the structure of your tables, but you can use inequality operators such as > and < to relate to ranges of values, or date ranges as I think you'd need in your case.

                      For all records more than 60 days old, you might define an unstored calculation, 60Days, that returns date as Get ( CurrentDate ) - 60

                      Then you can use it like this in a relationship:

                      ParentTable::PrimaryKey = 60DayChildTable::ForeignKey AND
                      ParentTable::60Days < 60DayChildTable::Date

                      Where 60DayChildTable is a table occurrence of ChildTable

                      That will match all records in ChildTable that have dates 60 days or older.

                      • 8. Re: Script to export and replace a Filemaker file worked in 5.5 but won't work in FileMaker 10
                        rebby_575

                        Here is a fairly straightforward calc that works for me to determine whether a payment or charge should should be part of the "60-90 days" period. I have similar calcs for each of the periods. "_aging date" is a global date field. "transaction date" is the date of the charge or payment. "credit.debit" is the amount of the charge or payment.

                        ( GetAsNumber(transaction date)≥_aging date -120) and (GetAsNumber(transaction date) ≤ _aging date -91) and (GetAsNumber(_aging include in aging) >0)

                        What I can't figure out is how to "age" it beyond what a simple summary of the numbers captured in the above calc (and its variations for each period).

                        The goal is to "subtract full or partial payments as applicable to a periods amount regardless of when the payment was made, and apply any overs or unders to the next nearest period" and display it.

                        I've tried dozens of calculations. No luck.

                        • 9. Re: Script to export and replace a Filemaker file worked in 5.5 but won't work in FileMaker 10
                          philmodjunk

                          GetasNumber isn't needed in your expression. Removing it should give you the same results as _aging date - 120 also returns a date and you can compare dates just like the integers they actually are.

                          The effectiveness of any such calculation depends on the structure of your data and I have no idea what that is. Presumably you have invoices in one table and payments in another? One options is to define a join table linking payments to invoices and as you log payments, you use the join table to apply portions of the payment to outstanding invoices. With the join table, invoices are then considered paid in full when all the related amounts in the join table equal the invoice total.

                          I'm really not sure how you are implementing the above calculation. Finding and grouping unpaid invoices can be as simple as generating a summary report that groups the unpaid invoice records first by client and then by age or you can use date range based relationships to link to each such category and use aggregate functions such as Sum() to compute the totals of each.