9 Replies Latest reply on Mar 27, 2013 10:25 AM by philmodjunk

    Import with criteria script

    caterinap

      Title

      Import with criteria script

      Post

           Hi all!

           I am a new FM Pro 12 user. I would like to import data into a database with some criteria.

           Here are more details....

           I have a very simple one-table database. Two other users work on the database at the same time using their own database copies. Thus the column names are the same for eveybody. Every user fills empty fields and may update some fields with new values. It is possible that two users change or fill the same field.

           Once a month evrybody send me their database with the new values and I have to integrate it in the main database. Thus I am looking for a script for data import. I am looking to something that can allow to:

           - import all the new fields that have been filled

           - for numerical fields: update the values only if they are greater than the ones I have already in the main databse

           - for text fields:update the values only if the text lenght is greater than the one that is already in the main database

           Do you have any ideas?

           Thanks a lot!!

        • 1. Re: Import with criteria script
          philmodjunk

               It would be much better not to use separate copies of the database like this. If you were to host the database from one computer while the others open it as clients of the database, you elminate the entire issue you have here.

               To do what you describe, you'd need to do a two stage import. You'd use import records to import the data into a temporary table. Then you'd use a script or possibly some Replace Field  Contents operations to selectively update your main table with data from the temporary table.

               You may also want to investigate the third party data synch tools produced by 360Works and SeedCode to see if they can make this process work better for you.

          • 2. Re: Import with criteria script
            caterinap

                 @PhilModJunk : Thanks a lot for your quick reply! In the future I will work with the first option you propose. (I didn't build the database and when I reached the project the other users were already filling the database as I described).

                 However for the moment I have to do this update... Do you think that once I have imported the data into a temporary table I can update the main one using lookups? I am a really bad user and I couldn't do that with a script...

                 cheers

            • 3. Re: Import with criteria script
              philmodjunk

                   It's possible, but you can easily loose data when you do so--which is why one needs to go to a hosted single copy as soon as possible.

                   Consider this hypothetical scenario:

                   User 1 opens the record for John Smith and updates the office work phone number.

                   User 2 opens the record for John Smith and corrects a misspelled work in his street address.

                   If you import and process the data from User 1 first, the new office work number will not be found in the merged data as the unmodified copy of this data from User 2 will change it back to the original value.

                   If you import user 2's data first, then User 1, the misspelled word is not corrected for the same reaon.

                   This is where it may be impossible to perfectly merge your data back into a single source with out direct guidance from the people who made the changes in order for you to determine which modified values should be kept.

              • 4. Re: Import with criteria script
                caterinap

                     You are right, thank you! Can I ask you an advice on the script I made to do my import? When I run the script nothing happens to my table... Thanks a lot in advance!

                     Table1: the main table that has to be updated

                     Table2: the table that another user sends me

                     these two tables are in the same database and are linked by Field1

                     Script:

                     Show all records

                     Go to Layout ["table1" (MaBase)]

                     Got to Record/Request/Page [First]

                     Loop

                     Go to Field [table1::field2]

                     If [Lenght ( table1::field2 ) < Lenght (table2::field2 ) or IsEmpty ( table1::field2 )]

                       Replace Field Contents [No dialog; table2::field2]

                     End If

                     Go to Record/Request/Page [Next; Exit after last]

                     End Loop

                • 5. Re: Import with criteria script
                  philmodjunk

                       Did you import the records into table 2 before running this script?

                       Is there a relationship linking table 1 to table 2? (won't work without such a relationship)

                       If you have a relationship, what match fields did you specify for the relationship?

                       You can remove the Go to field step from the script as it is not needed.

                       You would not use Replace Field Contents in a loop. It performs its action on every record in your found set all in one go. And thus, you need to include the If step as part of the calculation inside the Replace field contents step.

                       Show all records
                       Go to Layout ["table1" (MaBase)]
                       Replace Field Contents [No dialog;  If (Length ( table1::field2 ) < Length (table2::field2 ) ; table2::field2 ; Table1::field2 ) ]

                       But remember that this script still won't work without a valid relationship between table1 and table2.

                  • 6. Re: Import with criteria script
                    caterinap

                         Thanks for your help!

                         Yes I imported the records into table2 before running the script.

                         The two tables are linked as shown in the attached image (champ=field). I tried the script you proposed but it is not working. Is it because the relationship is not good?

                         I made the loop because I need to run the script on all the fields (except field1 that is the reference field for both the tables and is never changed by the users). The following script is working well on field2 but not on field 3 and field 4 -  there is a problem somewhere in the loop..:

                         Show All Records

                         Go to Related Record [From table: "table2", Using layout: <Current Layout>]

                         Got to Record/Request/Page [First]

                         Loop

                         If [Lenght ( table1::field2 ) < Lenght (table2::field2 ) or IsEmpty ( table1::field2 )]

                           Set Field table1::field2; table2::field2]

                         End If

                         Go to Record/Request/Page [Next; Exit after last]

                         End Loop

                          

                    • 7. Re: Import with criteria script
                      philmodjunk

                           Replace field contents works on all records in your found set so it does not need a loop.

                           The following script is working well on field2 but not on field 3 and field 4

                           I don't see any set field steps in your script for modifying field 3 or field 4 with data from the other table.

                            

                      • 8. Re: Import with criteria script
                        caterinap

                             Ok, I am sorry I thought that the function "Go to Record/Request/Page [Next; Exit after last]' made the script work on all the other fields. That` the problem source :-)

                             However your solution using Replace field seems much better but how can I do it automatically on all the fields (field 3, field4...)? Thanks again for your help.

                        • 9. Re: Import with criteria script
                          philmodjunk

                               You use one Replace Field Contents step for each field.