9 Replies Latest reply on Feb 8, 2012 10:58 AM by IdaChambers

    Script for finding missing data

    IdaChambers

      Title

      Script for finding missing data

      Post

      I have a script (written a very long time ago) that has stopped working.  Basically, I have a database with manuals and the manual holders have to return a transmittal which I log into the database (as date returned).  I need the script to find all active manuals and then only find the ones who have not returned their transmittal (no date in the field).  Can anyone suggest a simple script for this?

        • 1. Re: Script for finding missing data
          IdaChambers

          And I use FM Advanced 10 (and we have the server version).

          • 2. Re: Script for finding missing data
            philmodjunk

            to find records where a specific field is empty, enter find mode and put an = in the field, then perform the find.

            as a script:

            Go to layout [LayoutForYourTable]
            Enter Find mode []
            Set field [YourTable::YourDatefield ; "="]
            Set Error Capture [on]
            Perform Find []

            • 3. Re: Script for finding missing data
              IdaChambers

              How can I get it to find the correct field?  In the example I've uploaded, I want to find all the active manuals for manual code 9010 that have no Date Rec'd date in the Rev. # field 2/9/12 to 3/8/12.

              • 4. Re: Script for finding missing data
                philmodjunk

                Are these repeating fields?

                And you can have more than one record with manual code = 9010?

                If so, I don't think you can with this approach. I rarely use repeating fields due to their many limitations. I use a related table of records instead. If memory serves, specifying find criteria in one repetition finds records with matching criteria in any repetition.

                Even if not, there's the problem of putting the criteria in the correct repetition for each record, which might, at least in theory, be a different repetition on different records unless you can confirm that 2/9/12 to 3/8/12 will be in the same repetition for every record with this manual code.

                • 5. Re: Script for finding missing data
                  IdaChambers

                   Yes, in this example there are 4 records for the manual 9010.  As you can see, this manual is revised every month so that is why we have the repeating fields (so we can see and track manual currency).   The manual name, code, and repeating fields come from another database called Manuals2 (contains all the manuals we have), so the repeating fields in this database are the same for all 4 records.  I'm uploading the original script to see if it makes any sense to you.  The problem with the script is it won't find all the records that it should. Thank you for taking the time to look at my post, by the way.

                  • 6. Re: Script for finding missing data
                    philmodjunk

                    so that is why we have the repeating fields (so we can see and track manual currency).

                    Yet it can be done with a portal to a table of related records. Not only would this be a much more flexible design, it would make for a much simpler script here to find the records you need.

                    BTW, Why would you have 4 records with the same manual code?

                    Looking at your script it has a number of possible issues.

                    The first part where it enters find mode and then uses set field to set up find criteria, will fail if either "returned transmittals" or "CRL Heading" do not have global storage specified. (In find mode all fields except global fields are empty.)

                    The loop that omits records if quantity is not 1 can skip records, leaving records included that do not have a quantity of 1. This loop can be replaced by adding one more set field step to enter one in the quantity field before performing the find:

                    Set field [mark-off2::Quantity ; 1]
                    Perform Find []

                    This script calls a second script to determine the repetition number that contains the specified data to be checked. You haven't posted it here so I can't tell if it has any issues or not.

                    And one final observation:

                    the repeating fields in this database are the same for all 4 records.

                    If this is the case, you do not need the last loop in this script as either all the records with this manual code and Quantity = 1 will have a blank repetition in the specified column or none of them will. You can just perform this check on the first record and either omit all records--producing a found set of zero records or does nothing.

                    • 7. Re: Script for finding missing data
                      IdaChambers

                       We have 4 records for this manual code because it is also pulling information from our Holders db, so we have 4 different holders (individuals) with this manual code.

                      The one part of the database table that I don't understand is the Quantity and maybe that's where the problem is.  I'm uploading a picture of how it is defined.

                       

                       

                      • 8. Re: Script for finding missing data
                        philmodjunk

                        Not having any idea how your business works, nor how the database is designed, I really can't tell much. Putting a keyword such as "cancelled" in the status field will set the quantity to 0 and a complex set of nested functions is extracting a number, found just before the word " copies" from a field in a related table.

                        Your "find" script, if it worked correctly, would omit all records that are "cancelled", "for reference only" or for which the number in front of " copies" is not 1.

                        • 9. Re: Script for finding missing data
                          IdaChambers

                           Ok.  Thanks again for your time today.  I'm getting ready to leave for the day, so I'll give this some more thought tomorrow.