3 Replies Latest reply on Oct 21, 2013 10:56 AM by philmodjunk

    Importing Records based on two conditions



      Importing Records based on two conditions


           Let's say I want to import a csv file.  And the list in question has a phone column.

           On my Leads Table, I have an Area_code and Phone field.  I also have another table called AreaCodes that has a list of area codes that I currently have customers in.  This has already been set because I've only purchased licenses in these areas.

           So, when I import this lead list, I want it to pull the first three digits in the Phone field, and put that in the Area Code field.  I also want to make sure that this area code exists in the AreaCode table, and if not, just skip it.

           I'm able to do the first part by making the Area Code field on Leads to be a calculation field with this:

           Left ( Phone ; 3 )

           I don't know what to add though to check and make sure the result also exists in the AreaCodes table.  I tried to make this Area Code field be validated by a value list that I created between Leads::Area_code and AreaCodes::Areas.   This doesn't do anything for some reason.

           Any suggestions?   Thanks!

        • 1. Re: Importing Records based on two conditions

               If the CSV import will happen repeatedly, I would set up an ImportLeads database.

               That will allow you to parse the phone number, test for area code and delete unneeded records before importing into your database.

          • 2. Re: Importing Records based on two conditions

                 Ok,  how would I check whether The area code exist?  I can't figure out how to do that and delete if it doesn't.

            • 3. Re: Importing Records based on two conditions

                   Hmmm, I'm not sure you need that extra table.

                   If you set up your area code field as a number or text field with an auto-enter calculation that auto-enters the left 3 characters, you can enable auto-enter options during import and this field will automatically get the desired data.

                   This would then make it possible to set a validation field option on the same field that rejects data entry if there is no related records in the area code table. If you specify "validate always", I think your import would automatically filter out and omit those records where the area code does not match to a record in the area code table.

                   You'll need to try that on a copy of your file and see if it works as I think that it will.