7 Replies Latest reply on Jan 13, 2013 2:04 PM by Sorbsbuster

    Help with script please



      Help with script please


           Hello, I am using FileMaker Pro 12 on Windows XP.  I am having some difficulty trying to make my script work as shown in the screen-shot below.

           I am trying to perform a find such that the following happens:

           1.  It goes to layout: Find Emergency Contacts which come from the Emergency Contacts table;

           2.  Enters Find Mode;

           3.  Set Field - Personnel::Service that equals "Army";

           4.  Set Field - EmergencyContacts::RelatedToEmployeeID for all RelatedToEmployeeIDs that are less than 8510000

           5.  Perform the Find.

           6.  The field type of RelatedToEmployeeID is: Text.  That is because some of the RelatedToEmployeeIDs have alpha-numeric characters in them.

           7.  I am unable to get Step 4 (shown above) to work properly.  Step 3 (shown above) works fine.

           I do not know how to script FileMaker to find RelatedToEmployeeIDs that have a value less than 8510000.

           Could some one please be kind enough to help me get my script to work properly?

           Any assistance would be greatly appreciated.

           Kind regards,

           Chris smiley


        • 1. Re: Help with script please

               It may not be possible as FileMaker uses "Sort into alphabetical order" rules for determining what value will be less than "8510000". By those rules, "20" > "100000000" is a true statement because "2" comes after "1" in the character map's order.

               What kind of alphanumeric values are found in this field?

               Do you need to compare just the numeric portion of this ID to 8510000?

               There are several fixes I can think of but they depend on the format of the text and numbers used in this field.

               If you just need to compare the numeric portion of the ID, you can add a calculation field that extracts only the number portion of this value and then you can specify this search criterion in that field instead of this one. This calculationfield can simply be set to number as its return type and reference the RelatedTOEmployeeID field as its sole calculation term. This will work best if these values have text in one part and numeric digits in the other such as: 1234ABC or ABC1234. If there is text in the middle of numeric digits: 12ABC34, then this is a more complex issue to resolve.

               In other cases, introducing some leading zeroes so that all ID codes are the same number of characters can make a text based value comparison consitent with numeric based comparisons. ("00020" is NOT greather than "10000")

               And it's possible that examing your data may reveal that you can get the same results with completely different criteria specied in a different field or group of fields. Presumably, 8510000 has some significance to you. If you can figure out a different way to identify the same group of records...

          • 2. Re: Help with script please

                 Hello PhilModJunk, thank you for you reply - much appreciated.

                 To clarify:

                 1.  The EmergencyContacts::RelatedToEmployeeID field is a text field.

                 2.  The Personnel::Services field has five differnt values: Army, Navy, RAAF, Civilians and Contrcators.

                 3.  Due to the limitations of exporting a very large quantity of Army Emergency Contact data to Microsoft Excel 2003, I have had to split this data up into two separate spreadsheets.  This is because the quantity of exported data exceeds the number of rows in the spreadsheet.

                 4.  As I have already added the script step as follows: Set Field [Personnel::Service; "Army"] ---- (at script step number 5 as shown in my original post screen-shot), the script only shows Army personnel.

                 5.  This is good, as all Army personnel have a EmployeeID that is exactly seven characters in length and each Army EmployeeID starts with the number 8.

                 6.  There are no alpha-numerics in any of the Army EmployeeIDs.

                 7.  The alpha-numeric EmployeeIDs are only to be found for Civilian and Contractor EmployeeIDs only ---- so I do not need to worry about these at all.

                 8.  The Army EmployeeID number of 8510000 is significant as it is the half-way point of all the currently available Army EmployeeID numbers.

                 9.  I do not want to add any leading zeros in my EmployeeID codes.

                 Could you please advise on a calculation field that would suit my situation?

                 Kind regards,

                 Chris smiley

            • 3. Re: Help with script please

                   If the only reason you are limiting the Find is to keep the number of records less than the max number of rows Excel can handle, you could find the full set (with no limitation) and then Go to Record Number [the limit of Excel] and omit from there down.  (Or omit from the top to that record.)

                   What you do with the omitted records depends on what you do now with the balance of the records that are over the limit.

              • 4. Re: Help with script please

                     Hello Sorbsbuster, thank you for your reply - much appreciated.

                     The reason I have to split the number if Army Emergency Contacts records up into two spreadsheets, is that the total number of Army Emergency Contacts records exceeds 65,536.  This number: 65,536 is also the maximum number of rows in a Microsoft Excel 2003 spreadsheet.

                     I have approximately 120,000 Army Emergency Contacts records to be exported from my FileMaker database to a Microsoft Excel 2003 spreadsheet.

                     I think your suggestion sounds great, however, I am not sure how to implement it into my script.

                     Could you please provide further assistance?

                     Kind regards,

                     Chris smiley

                • 5. Re: Help with script please

                       I would drop the tricky ‘less than’ Find request step, and just find all the Army personnel.  Then I would add the script steps:

                       #Check if there are more than 65000 records

                       If Get ( FoundCount ) > 65000

                       # Found count is more than 65000

                       Go To Record [By calculation ; 65000 ]
                       Omit Multiple [9999999999999 ; no dialogue]

                       Export Records [ Emergency Contacts Set 1.xlx ]

                       (No re-do the original find again here)

                       Go To Record [First ]
                       Omit Multiple [64999 ; no dialogue]

                       Export Records [ Emergency Contacts Set 2.xlx ]


                       # Found count is less than 65000

                       Export Records [ Emergency Contacts Set.xlx ]

                       End If

                       (You will have a small exposure in a multi-user environment to the scenario of a user creating (or deleting) a record in the instant between the first export and the second.  You could check for that by checking the found count after the first find, and again after the second find, and checking that they match.)

                  • 6. Re: Help with script please

                         Hello Sorbsbuster, thanks again for your excellent help - greatly appreciated.

                         When I run the script, it will be done when all users are off-line (logged-out).

                         The script I am trying to create exports all records out of my database and saves them in spreadsheets, so that later on I can import them back.

                         Kind regards,

                         Chris smiley

                    • 7. Re: Help with script please

                           Why not just export them as text, or Filemaker format, and avoid the record-count limitation?