1 2 Previous Next 29 Replies Latest reply on Sep 17, 2012 2:24 PM by Terri

    Insert value in field for all found records

    Terri

      Title

      Insert value in field for all found records

      Post

           I'm trying to create a script that will perform the following (right now my script will only update the first record in the found set):

           Find all records with value of Y in field called Duplicate

           Insert the value of 1 in the field called Flag for all records found

           Thanks!

        • 1. Re: Insert value in field for all found records
          philmodjunk

               Replace Field Contents [no dialog; YourTable::Flag ; 1]

               will put a 1 in the flag field for all records in your current found set.

          • 2. Re: Insert value in field for all found records
            philmodjunk

                 What kind of field is "flag"? Might it be a calculation field instead of a number field?

            • 3. Re: Insert value in field for all found records
              Terri

                   Sorry, I tried to delete my response, I did have the wrong field selected.

                    

                    

              • 4. Re: Insert value in field for all found records
                philmodjunk

                     And you did, I was just "too fast" for you. wink

                • 5. Re: Insert value in field for all found records
                  Terri

                       I need to expand on my first question a little. I thought I had it. I want the script to:

                       1. Find records that have a Y in the duplicate field.

                       2. Place at 1 in the flag field.

                       3. Go to the first record that has a 1 in the flag field.

                       4. Copy the Style_Color field.

                       5. Do a find for all records that have the same Style_Color.

                       6. Replace the flag field with a 2.

                       7. Continue with all records that have a 1 in the flag field.

                       The purpose is to find all records that are a duplicate and also find its "twin". Once the script runs I would do a search for all records with 2 in the flag field. Please help. The script steps below return an error of:

                        

                  • 6. Re: Insert value in field for all found records
                    philmodjunk

                         Your post seems to cut off prematurely. Did you try to upload a screen shot? Sometimes those are delayed in appearing so it may still appear after I post this or you may have uploaded the wrong file type.

                         I don't think your script will work the way you want without modification.

                         How does the duplicate field get a value?

                         (This is all starting to sound very familiar--weren't we discussing a very different approach to this earlier?)

                         Step 4 can be: Set Variable [$Style_Color ; YourTable::Style_Color ]

                         Step 5 can be:

                         Enter Find Mode []
                         Set Field [YourTable::Style_Color ; $Style_Color]
                         Perform Find []

                         Step 6 is where you may want to rethink this. Do you want all the records found marked with a 2 or all but the original record with these values?

                         Step 7 can be:

                         Enter Find mode []
                         Set Field [Yourtable::Duplicate ; "y" ]
                         Set Error Capture [on]
                         Perform Find[]

                         I'm suggesting that you use the duplicate field here so that you don't refind the original record that might still be marked with "1".

                         If this is the project I was helping someone with earlier, this mix of original and new, but duplicated records and the resulting need to identify which are are which is why I suggested using a temp table for the new data and importing from the temp table with an Import matching type of import as a second step in the process after reviewing the records with modified data for changes.

                          

                    • 7. Re: Insert value in field for all found records
                      Terri

                           Yes, that was me. Right now I'm using the self-join relationship only because I was further along with that. Either way, whether I use the self-join or the temp table I will need to compare the records (the original and the new, modified record) side by side before I import using the matching type. That is really what I'm trying to accomplish here. Sorry my screen shot did not appear. These are the steps of my script (forgive any typos - i typed it in):

                           Go to Layout ["Workorders" (Workorders)]
                           Perform Find [Restore]
                           Replace Field Contents [No dialog; Workorders::Flag; 1]
                           Loop
                             Go to Record/Request/Page[First}
                             Copy [Select; Workorders::Style_Color]
                             Enter Find Mode[]
                             Paste [Select; Workorders::Style_Color]
                             Perform Find []
                             Replace Field Contents [No Dialog;Workorders::Flag; 2]
                           End Loop
                            
                           I get an error message that says invalid search criteria (can't be exact it is still running). Admittedly it needs to search through 90,000 records so if you have any time saving advice that would be great too.
                            
                           Thanks!
                      • 8. Re: Insert value in field for all found records
                        philmodjunk

                             Yet it leaves "duplicate" records in the same table and this complicates a number of issues. Keeping the records separate for comparison until you are ready to update them by merging them into the existing records avoids this whole issue.

                             I'd need to see what criteria you are using in your find.

                             and where you are at in the script when you get this error.

                             I don't recommend that you use copy and paste in this manner either. Using Set Variable to "copy" the data and Set field to "paste" the data is a better option for a number of reasons--not the least of which that if you copy some data to the clipboard before you run this script, it will remain unchanged if you use the set variable/set field pair of script steps.

                             Before I get in too deep here, what are the 1 and the 2 supposed to represent? Is one supposed to identify the original version of the work order and 2 the copy with changed information?

                        • 9. Re: Insert value in field for all found records
                          Terri

                               You have a good point about the duplicate records in the same table. It is making me nervous. I'm going to start over and use your approach. Because of that I'll not complicate this issue further by answering all your questions except one: the 1 was supposed to represent the duplicate record, then when its "mate" is found, then they both change to 2. Simple, I know, but it was the best I could come up with on my own :)

                               I'm sure I'll be back. Let me know if you want me to start a new, fresh post to get things back on track.

                          • 10. Re: Insert value in field for all found records
                            philmodjunk

                                 Also take a look at this zipped archive of a demo file and a csv file for import: https://dl.dropbox.com/u/78737945/ImportCompareDemo.zip

                                 It's an FMP12 demo file.

                                 Extract the contents to a new folder before you open the database file in the new folder.

                            • 11. Re: Insert value in field for all found records
                              Terri

                                   Thanks for the example, that will help when I get to making all these import steps a script, right now I'm still doing it manually until I get the hang of it.

                                   Ok, I have it set up now so the import goes into a new table, duplicate records return a 1 in the Compare field and new records return a blank in the Compare field (works great!). Prior to importing the new records into the main table I need to compare the duplicates (otherwise they change to a 1 at import). Here is the script I have: 

                                   Perform Find [Restore] //returns records with 1 in the Compare field//
                                   Loop
                                     Go to Record/Request/Page [First]
                                     Set Variable [$Style_Color; Value:WorkorderTemp::Style_Color]
                                     Enter Find Mode []
                                     Set Field [Workorders::Style_Color; $Style_Color]
                                     Perform Find []
                                     Replace Field Contents [No Dialog; Workorders::Duplicates; 2]
                                   End Loop
                                    
                                   My train of thought is that all records that are a duplicate will now have a 2 in the duplicates field. When I search for that and then sort by the Style_Color field they will be on top of each other so I can compare (conditional formatting to come). 
                                    
                                   How does it look? It takes forever to run because it is comparing 209 new records against 90,000 current records so I haven't actually done it yet. Is there any other option that would make the process go more quickly?
                              • 12. Re: Insert value in field for all found records
                                philmodjunk

                                     Why use a script at all?

                                     My demo compares matching WO records without using any scripts except those that import data. It uses a relationship based matchup between records and conditional formatting (and also a calculation field) identifies which records differe and which fields within the pair of records are different--all without any scripting.

                                • 13. Re: Insert value in field for all found records
                                  Terri

                                       Sorry, I didn't look closely enough (it was the end of the day). I thought it was just flagging changed records.

                                       Questions about your script:
                                       1. Does this account for records that are new in the import? To me it is only looking at if they change. 
                                       2. I don't understand the If portion of the script. Can you explain what those steps are doing? To me it looks like it is importing all the records from the import file regardless of whether they are new (blank) or updated (1). 
                                       3. I also need additional steps to actually bring those records together (the old and the updated) so they can be reviewed.
                                        
                                       Differences in your technique versus mine - my layout will be in table form because I am trying to replicate a process we currently have in excel. So I need it to look like that. I understand the theory to go work order by work order and look at the comparison field but our users won't work that way. So I'm not using the comparison field to review the 62 fields next to each other. I need it to look like excel with all the fields across the top and the matching records in row 1 and 2 (for example) and our users will look at the difference and then make adjustments (in other fields) as needed. I hope that makes sense.
                                        
                                       Also, after thinking about this further, I need one additional thing. I need the new records to be indicated with something in the flag field (rather than blank) so they can be quickly identified and analyzed. That should just be a change on the compare calculation I think, but please confirm.
                                        
                                       Thanks, as always, for your patience and help.
                                  • 14. Re: Insert value in field for all found records
                                    philmodjunk

                                         There are two import scripts.

                                         One imports to a temp table so that you can compare the records to identify changes. This import imports all records from the CSV file whether they be for new records or are changes to existing records. While I chose to modify the default form view layout in order to compare records for changes, I could have also used a table view or list view layout to display the fields in adjacent columns with the same conditional formatting highlighting any changed values.

                                         A second import does an update and add import to move the data from the temp table to the Workorder table to merge in the changes and to add any new records. The "workflow" concept behind this is that you would import to the temp table to review your existing work orders to see what has been changed. Then, when you finish the review process, you update your Work orders with the second import. The second import script finds only those records that have been changed and they are imported from thet temp to work order table. This step is not strictly necessary. If the record has not been changed, then the "update matching" that has been setup won't change them. The only purpose to this part of the script is to speed up the process by not importing records that do not need to be imported.

                                         The main draw back that I can see to this is that new records will not appear in WorkOrders until you perform the second import script. It would, however, be very simple to set up a different script that imports all new Work Orders--all Work Orders that do not already have a matching record in the WorkOrders table so that you can see both new and changed records on the same layout.

                                    1 2 Previous Next