12 Replies Latest reply on May 7, 2017 5:27 PM by Philip_Jaffe

    Changing all records

    MarkBanin

      I would like a one off change to all my records if a particular condition exists within that record.

       

      For example, if a field within a record has content then a checkbox within that record will be filled with "Yes" - the same test to go through all records.

       

      What is the best way to accomplish this?

        • 1. Re: Changing all records
          philmodjunk

          It depends on what you are trying to do.

          Replace Field Contents can modify every record in a found set and a found set can be every record in a table.

          But maybe this field should be a global field instead. Or maybe it should be a field in a related table.

          • 2. Re: Changing all records
            siplus

            MarkBanin wrote:

             

            I would like a one off change to all my records if a particular condition exists within that record.

             

            For example, if a field within a record has content then a checkbox within that record will be filled with "Yes" - the same test to go through all records.

             

            What is the best way to accomplish this?

             

             

            do you maybe mean

             

            I would like a one off change to all my fields if a particular condition exists within that record.

             

            ?

            • 3. Re: Changing all records
              MarkBanin

              No, it's a change to one field (a checkbox), within each record, if another field within that record has content.

               

              I presume I could run a script with a loop that goes through each record and does the check (if statement) and changes the checkbox if necessary.

              • 4. Re: Changing all records
                MarkBanin

                I've tried using Replace Field contents and using a calculation, as the change is conditional.  But it didn't seem to do anything.

                • 5. Re: Changing all records
                  siplus

                  I'm afraid that you must better describe what you want  to happen.

                   

                  " would like a one off change to all my records if a particular condition exists within that record."

                   

                  does not mean anytihng we can code. Problematic item: the word "that".

                  • 6. Re: Changing all records
                    MarkBanin

                    This is a one off batch run.

                     

                    My database has some 600 records.

                     

                    Each record has multiple fields - however there are only two fields we are concerned with.

                    Field (A) is a checkbox

                    Field (B) is a text field

                    If Field (B) has content, then make Field (A) true

                     

                    And do the same thing for every record.

                     

                    Does that make sense?

                    • 7. Re: Changing all records
                      wintertj

                      Hi Mark, I'd suggest making a plain vanilla layout based on whatever table that the two fields (A and B) exist on. Go into layout mode of your new layout, and use the field picker to drag only your two fields of interest (A and B) to the layout, leaving all other fields off of the layout. Don't do any further formatting to the fields, the fact that you presumably have a layout that has field A formatted as a text box sounds like it may be causing some confusion. A plain vanilla layout will allow you to see the data in its default style of Edit Box, instead of Checkbox Set. This will help you visualize the data as it is stored on the table.

                       

                      Before beginning your script, determine what value your checkbox is represented by, by browsing the records on the database layout that are currently set to having the checkbox set. If non exist, create one on a layout that does have the checkbox formatting for field A. In the below example, I'll use the value "1".

                       

                      Now, create a simple script, that would look like this in pseudo code:

                       

                      Go to layout ["whatever your new layout name is" (table occurrence layout is based on)]
                      Show all records
                      Go to record/request/page [First]

                       

                      Loop

                      If [not IsEmpty (TABLE::Field B )]

                         Set Field [(TABLE::Field A; "1")]

                      End If

                      Go to record/request/page [Next ; Exit after last]

                      End Loop

                       

                      A lot of the syntax of the script will be filled in automatically by the script editor.

                       

                       

                      Once your script is done running, hop over to whatever layout has Field A formatted as a checkbox set, and observe that for any record who has a value in Field B, the Checkbox is now set in Field A.

                       

                      If this works, then you are done with the new layout and the new script.

                      • 8. Re: Changing all records
                        Philip_Jaffe

                        Replace field contents by calculation will do the same thing in one step.  If it's a one off, then go to your checkbox field.

                         

                        Replace field contents of the check box field with this calc:

                         

                        Not IsEmpty (Field B)

                         

                        That would place a "1" in the checkbox field for any record where Field B has content.

                         

                        If your checkbox field is using some other vale in its value list, say the word "Full" for instance, then Replace Field Contents with this calculation:

                         

                        Case ( Not IsEmpty ( Field B ) ; "Full" )

                        • 9. Re: Changing all records
                          keywords

                          Re: "If Field (B) has content, then make Field (A) true"

                           

                          If it really is that simple, have you considered making Field A a calculation:

                          If ( IsEmpty (Field B ) ; 0 ; 1 )

                          or, if you prefer:

                          If ( IsEmpty (Field B ) ; "" ; 1 )

                          • 10. Re: Changing all records
                            MarkBanin

                            Thank you, couldn't get the Not IsEmpty to work but I replaced it with  ≠ ""
                            and it worked great

                            • 11. Re: Changing all records
                              MarkBanin

                              Thanks for your suggestion, but this was needed as a batch run not in realtime.

                              • 12. Re: Changing all records
                                Philip_Jaffe

                                Hmmm.  not sure why not isempty didn't work for you.  But glad you got what you needed.