7 Replies Latest reply on Aug 17, 2012 8:55 AM by philmodjunk

    How to validate unique values on import?



      How to validate unique values on import?


      Has anyone written an import script that involves a value list? What I mean is I have value lists coming from tables and on import I am having data imported into these tables. However, there is already data there and when the user imports the data they are likely entering in values that already exist. The only way I could find to validate a field to be unique is in the validation settings for the field iteself but then when you run the script a validation warning pops up and stops the script (and could potentially pop up many times). 


      Is there a way to give an automated response to this warning message, or bypass it completely? Potentially "Set(CurrentMessageChoice)" could work if it was possible? This does not seem to be recognized as a function.

      OR is there another way to validate that a newly entered field is unique that I am not familiar with? 


        • 1. Re: How to validate unique values on import?

          The validation warning doesn't pop up. Instead, the data from that duplicate value is not imported and you get a report telling you that this is the case. (make sure that you specify "validate always")

          You can also use an Update matching values import with the add new values option to import your data.

          • 2. Re: How to validate unique values on import?



            I think I forgot to mention that the data is being imported into new records (so I can't use an update matching values on import). I do see that I could do a sepereate import perhaps but I was really hoping to run this part "behind the scenes" so the user wouldn't have to do anything.


            The script for importing new mineral records and then taking the data from the mineral name value list/authority file and copying it to the table the value list is coming from looks like this: 


            Go to Layout ["Mineral Material' (Mineral Table)]

            Show Custom Dialog

            If [Get(LastMessageChoice)=1]

            Import Records []

            Go to Record/Request/page [First]


            Set Variable [$Y; Value:Mineral Table::Mineral Name]

            Go to Layout ["Mineral Name Entry Screen" (LIST Mineral Name Info Table)]

            Show All Records

            New Record/Request

            Set Field [LIST Mineral Name Info Table::Authority File for Mineral Name List; $Y]

            Go to Layout ["Mineral Table" (Mineral Table)]

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

            End Loop

            End If


            It continues but this is that part I am working on to validate the variable being copied over. This works to copy the variable over, but if there is already a "copper" entry for example then it won't recognize this and now there will be be two "copper" entries. This wouldn't matter so much if the value list only selected unique fields from that table but it doesn't, it takes them all. So I am trying to validate the field when it is populated. I know you can, as I said before, select under validation for it to be unique but then the warning, or report as you said, stops the script. I was thinking maybe there is a way to say "Set 'allow this duplicate value?' = No". 


            Any Ideas?

            • 3. Re: How to validate unique values on import?

              Use the first method instead.

              Simply add the unique values, validate always option and import your data. Duplicates will be automatically excluded.

              I don't see any need for the script to loop through any records here.

              Hmmm, come to think of it, that update matching method, with the add new records if no match option, might also still work, but I've never tried that in this exact situation...

              • 4. Re: How to validate unique values on import?



                Duplicates are excluded that way, however, unique entries are not added. I need a script (or trigger of some sort) that says "if mineral name is unique, add it to the mineral list table" without any pop-up error messages. I attached a few print screens that show what I have tried, I think what is failing is the part that says "if mineral name is unique" I tried using a find and got an error that popped up each time saying "no records match this find" over and over again. So then I tried a script using the result of that error but to no avail. Your suggestings make it sound so easy, what am I missing?


                • 5. Re: How to validate unique values on import?

                  Guess it only uploaded one.. here is number 2

                  • 6. Re: How to validate unique values on import?

                    and here is number 3

                    • 7. Re: How to validate unique values on import?

                      Duplicates are excluded that way, however, unique entries are not added.

                      I've described two different approaches--one that I know works and one I think will work, but haven't tried and both should add new unique entries.

                      To which method are you referring?

                      To recap:

                      Method 1:

                      Import with the unique values/validate always setting omitting duplications

                      Method 2: (Untested in this type of situation)

                      Import using the matching values import and also selecting the "add new" option so that records that fail to match are added as new records.

                      Note that Method 1 discards all subsequent duplicates and Method 2, in theory, merges data from the duplicate entries with data from the last duplicate to be imported overwriting data from earlier.