10 Replies Latest reply on Nov 11, 2015 5:33 PM by user19752

    How find records with fields whose values are no longer in a changed value list.

    eliotros

      Here's what I tried.  I set the fields to Always Validate using the new Value Lists.   I created a script with one step: Commit Record.  And I trigger that script with OnRecordLoad.  I expected that as I stepped thru the records (using Ctrl-downArrow), I'd get an error message when a record had invalid data (so I could revise it).  I get no messages.

       

      What am I missing?  I thought I read somewhere that Committing a record triggered data validation.  Are fields only validated if they were changed?  If so, can I set each field equal to itself using SetField?  Or pre-pend an "x" and then delete it?  This doesn't seem very elegant.  Is there a better way?  Thanks!    (FMP Pro Adv 13.0v5)

        • 1. Re: How find records with fields whose values are no longer in a changed value list.
          Mike_Mitchell

          Record commit only fires if the record has been changed. Just scrolling to a record won't cause it to fire.

           

          Yes, you can use a script to reset a field equal to itself. But that causes other issues. For example, if you have a modification timestamp field, that will update. Any auto-enters based on the field will also fire. Basically, you're modifying data for the sole purpose of trying to find invalid data. Why not just perform a Find to search for any records that don't meet the new validation criteria and fix them?

          • 2. Re: How find records with fields whose values are no longer in a changed value list.
            eliotros

            Thanks, Mike.  I believe what you are suggesting is this:

            Find field1=removedField1Val1.  Then Correct those records.

            Find field1=removedField1Val2.  Then Correct those records.

            ...  (continue or each Field1 discontinued value)

            Find field2=removedField2Vall1.  Then Correct those records.

            ...  (continue or each Field2 discontinued value)

            ...  (continue for each field)

             

            I guess I was hoping there might be an easier way.  Partly b/c I'm uncertain of the Table's history.  I could get backups of the file to look at the Value Lists at various points in time, but there may have been more than 1 or 2 changes to these on dates unknown.  (Hmm, presumably list additions almost solely, so maybe just the previous version will suffice.)  With 3 or 4 fields, each with perhaps 5-10 discontinued values (I'm not sure!), this process would take some time.

             

            I created no other "On..." triggers, so to my knowledge, my only corruption would be in the Modification Date.  Yes?

            • 3. Re: How find records with fields whose values are no longer in a changed value list.
              alquimby

              To answer the question in your subject line, what about going into Find mode, then in the field in question use Insert > From Index. You will be able to see all values, including the ones you want replaced. Find them one at a time, select a value you want and use Records > Replace Field Contents.

               

              Al Quimby

              • 4. Re: How find records with fields whose values are no longer in a changed value list.
                Mike_Mitchell

                If you want to automate something, use:

                 

                ValueListItems ( "" ; nameOfValueList )

                 

                to build a list of values from the list. Loop over that list in a script, creating a new Find request for each one. The found set will be all records that do have a value in the "legal" list. Find the omitted records to find the ones that don't (just click on the little dial icon next to the found count).

                • 5. Re: How find records with fields whose values are no longer in a changed value list.
                  user19752

                  To remove values not in value list,

                  Replace Field Contents [ FilterValues ( "ValueListName" ; theField ) ]

                   

                  If you need to hold auto enter values in other fields, do the avobe in duplicated file, then import the field from it without auto enter option.

                  • 6. Re: How find records with fields whose values are no longer in a changed value list.
                    Mike_Mitchell

                    Good option! Except it needs to be:

                     

                    Replace Field Contents [ FilterValues ( theField ; ValueListItems ( "" ; "ValueListName" )) ]

                     

                    You need to fetch the actual values in the list, and the values to be preserved are the second parameter of FilterValues.

                     

                    But a good option if you don't need to error capture, and you don't want to review each record first (or perhaps after you've reviewed the records you've found).

                    • 7. Re: How find records with fields whose values are no longer in a changed value list.
                      eliotros

                      Thanks Al and Mike.  Correct me if I'm wrong, but your suggestion, Mike, seems workable if the fields were "radio buttons" so to speak.  But with checkboxes, if a field contained both good and "bad" (ie, discontinued) values, the record would be found, and thus not appear among the Omitted.

                       

                      I can imagine vaguely (given that my FMP scripting thus far has been somewhat basic) creating a NEW table with 2 fields: value and status.  Then Script-1 would use the function and a loop to populate it with ALL the values in the field.  Script-2 would then (somehow?) identify all the good (or bad) values and set their "status."  Then I'd still need somehow to get the bad values into a Find request.    ... or (better?) set up (on the layout for the New Table) a Portal showing related records using value as the key.

                       

                      Maybe someone more knowledgable than I could make this work (perhaps even using an array [instead of a new table] internal to a single script), but unless this is simpler than I imagine, I think I'd be better off using Al's approach.  Thanks again to both of you.

                      • 8. Re: How find records with fields whose values are no longer in a changed value list.
                        Mike_Mitchell

                        Um ... well, mentioning that your values were checkboxes - multiple values per field - might have improved the answers. Especially considering you were asking how to find records, not necessarily automate the cleanup ...  

                         

                        But since that's the case, you need to be very careful with Replace. You can inadvertently blow away good data with the bad if you bungle the calculation.

                         

                        I suggest you make a copy of the database, perform your Replace, and then import the records back into the table. Use the FilterValues calculation I provided above.

                        • 9. Re: How find records with fields whose values are no longer in a changed value list.
                          eliotros

                          I hadn't seen these posts by 19752 and Mike when I responded below.  Cool function!  But yes, I'll probably need to examine each record to decide ad hoc what new values to set. (Sorry I didn't mention the checkboxes sooner.)  Thanks! 

                          • 10. Re: How find records with fields whose values are no longer in a changed value list.
                            user19752

                            Thanks for quick correcting, I had damaged since I noticed it after while but I already leaved from my computer, no access to correct it. You helped me much.

                             

                            I wrote value list first, it mean "sort the values as in the list, not clicked order". This is users option to select which is better for them.