5 Replies Latest reply on May 1, 2014 1:19 PM by FentonJones

    Prevent Importing to a Field that already has data

    fmpro_novice

      Title

      Prevent Importing to a Field that already has data

      Post

           How can I make a Field change to: (Target cannot receive data) if there is data already in that field when I am importing?

           I am using FM12 Pro

        • 1. Re: Prevent Importing to a Field that already has data
          philmodjunk

               Can you describe what you want to do in more detail? You may need to import into another table and then use a script to move the data into your target table.

          • 2. Re: Prevent Importing to a Field that already has data
            fmpro_novice

                 I have a list of students that earn points each week.

                 Each week is a field; Week 1, Week 2, Week 3, etc. At the end of the week, the students points are exported from another file and into this points file.

                 We use field mapping to import. I don't want someone to accidentally import Week 2's point into Week 1 if they forget to change the field mapping.

                 So, if  Week 1 already has points, I would like that field to be "Target Cannot Receive Data" when the user is looking at the Import Field Mapping dialog box. That way they will automatically look at the next field for mapping. Once Week 2's points have been added, then when importing Week 3, Week(s) 1 and 2 will be shown as "Target Cannot Receive Data".

            • 3. Re: Prevent Importing to a Field that already has data
              philmodjunk
                   

                        I have a list of students that earn points each week.

                   

                        Each week is a field; Week 1, Week 2, Week 3,

                   I would design the database so that each "week" is a record in a related table linked to a table of students. That can be much more flexible a way of working with such data and importing would always add a new record to such a table--eliminating the issue that you have to deal with here.

                   

                        At the end of the week, the students points are exported from another file and into this points file. We use field mapping to import. I don't want someone to accidentally import Week 2's point into Week 1 if they forget to change the field mapping.

                   If you choose to stay with the current design, I'd set up a script that does the import and that then has a different Import Records script step for each week field to which you might map the imported data. The script can check either the current date or the data in the fields to determine which Import Records step to use to import the data. (It's never a good idea for routine imports to require manually mapping the fields--too much chance of a user error scrambling your data on you.)

              • 4. Re: Prevent Importing to a Field that already has data
                fmpro_novice

                     Thank you. I'm going to try your first option.

                • 5. Re: Prevent Importing to a Field that already has data
                  FentonJones

                       Charlie, I just thought I'd add a picture of one option of the Import step, which could be added to a script. This is how you'd link to a person, using their ID. You can add any other field as a link also, either as a positive or negative match; it supports: =, ≠, ≥, ≤, etc., similar to a relationship. So, if there is any field in the data you're importing from, this can tell it whether to import it or not, or whether to create a new record to import into, or whether to add data to existing fields, in the current Found Set. (It does not change the fields used a <=> match, as they are matched). The below image just shows the basic: