8 Replies Latest reply on Jun 14, 2011 9:37 AM by mgores

    import issues

    mgores

      Title

      import issues

      Post

      I am trying to create a file that will be shared over the web for customers to monitor the status of their jobs.  Since the web shared file cannot access the tracking table in our main file, I am trying to set up an import script that will keep a duplicate local table in the web shared file.  I selected Update matching records in found set with add remaining data as new records.  The issue is that whenever the status is updated in the main file, rather that updating the record in the local file a new record is created.  Is "update existing" the correct way to do this rather than "update matching"?

        • 1. Re: import issues
          philmodjunk

          When you use "update existing" you also have to specify the match fields by clicking the arrow between them to change the arrow into an =. If the match fields don't actually match, you'll get a new record instead with the options you've indicated that you are using. You'll need to check those match fields and see why the reocrds don't actually match.

          • 2. Re: import issues
            mgores

            I have it set to update matching and have the trackID field set as the match records based on this field.  When I select update existing I can't select a match field.

            • 3. Re: import issues
              philmodjunk

              I'd use update matching, if possibile. Update existing requires, I believe, that the order of the records in your found set exactly match order of the rows in your table of import data. Update matching allows you to match data by a common value or values and this is much less likely to update the wrong record in your table. In both cases, make sure that the found set containes the appropriate records before you start importing.

              • 4. Re: import issues
                mgores

                That is what I thought.  However, when the status of a job is changed, another record is created with all the same data except for the new status.  There ends up being multple records for jobs as their status changes.  Am starting to think it might be easier to have it delete all records, then do a clean import.

                • 5. Re: import issues
                  philmodjunk

                  That is an option to consider. On the other hand, if you included the status as one of the match fields (you can use more than one pair of fields as match fields), you can set this up so that a status change adds a new record where an unchanged status updates the existing record.

                  • 6. Re: import issues
                    mgores

                    I was shooting for having the status field update without creating a new record.  When the customer logs into the web file, they see a portal showing the details of each job we are doing or have done for them.  New records should only be created when a new record is created in the master file, i.e. a new TrackID

                    As it had been testing out so far I end up with multiple records with the same TrackID but have different status.  TrackID is the only match field I have selected, thought it the logical choice since all jobs have a unique value.

                    • 7. Re: import issues
                      philmodjunk

                      Ok, misread this statement: "when the status of a job is changed, another record is created with all the same data except for the new status" as what you wanted to see happen as opposed to a report of what was actually happening.

                      That's a strange result here. If the record is present in the current found set and if you specify the trackID as the Match field here, this should not happen, the existing record should update with new records created only if the TrackID is does not match a record in the current found set.

                      The only way I can see where this is possible is if the records where you are getting additional records instead of udpates is not in the current found set. Make sure to use Show All records to pull all your records into the current found set before doing this import.

                      • 8. Re: import issues
                        mgores

                        I think that was the simple step I needed,  put show all records step in front of the import step.  Was doing different finds testing other features and was probably getting the duplicates from records not being shown.  Will let it run awhile and see if that fixes it.

                        Yes.  That did it.  Thanks Phil.