1 2 Previous Next 22 Replies Latest reply on Jan 25, 2014 9:38 AM by CarrieB.

    Insert data into field of related table for all in found set

    CarrieB.

      Title

      Insert data into field of related table for all in found set

      Post

           Wondering how I can run a find in Table A, then insert data into a field(s) in related Table B for all in the found set of Table A.

           Any advice is much appreciated!

        • 1. Re: Insert data into field of related table for all in found set
          philmodjunk

               Use Go To Related Records to pull up all related records in Table B on a layout for Table B for all records in your Found Set in Table A.

               Then use Replace Field contents to update the field of all those Table B records.

               And the question to ask yourself if you have to do this frequently is: Should this data actually be in a field in Table B or should it be in a related table linked in in such a manner that only one field of one record (or just a few records) need be updated?

          • 2. Re: Insert data into field of related table for all in found set
            CarrieB.

                 I'm sorry. I worded that post completely wrong. What I actually want to do is create a new record in Table B for all records in the found set from Table A.

            • 3. Re: Insert data into field of related table for all in found set
              philmodjunk

                   Why do  you need to do that? It seems a bit unusual.

                   You'd need a looping script that loops through all of your record in table A to create related records in Table B.

                   Freeze Window
                   Go to Record/Request/Page [first]
                   Loop
                     Set Variable [$ID ; value: TableA::PrimaryKey ]
                     Go to Layout [ "Table B" (Table B ) ]
                     New Record/Request
                     Set Field [ TableB::ForeignKey ; $ID ]
                     Go to Layout ["Table A" (TableA ) ]
                     Go To Record/Request/Page [ Next ; exit after last ]
                   End Loop

              • 4. Re: Insert data into field of related table for all in found set
                CarrieB.

                     I probably don't have it set up correctly. Basically, I'm running a contact management database. Table A holds all the information about the contact. Table B has a record for each time we mail the contact that includes a list # (Field A) and a date (Field B).

                     What I want to do is import a new list of records for Table A, then enter the list # and date for each record in Table B.

                     I will be importing new lists on a regular basis. They will all go into Table A, but each new list will have a unique list# for Table B.

                     Is there a better way to set this up?

                • 5. Re: Insert data into field of related table for all in found set
                  philmodjunk

                       Don't know what you mean by list #. I can guess that it's a field, but not the purpose that it serves in your database.

                       If,

                       

                            Table B has a record for each time we mail the contact that includes a list # (Field A) and a date (Field B).

                       Then you should not need to create new records in Table B until you actually mail a contact in Table A.

                        

                  • 6. Re: Insert data into field of related table for all in found set
                    CarrieB.

                         Yes, there are 2 fields in Table B. When I import a list into Table A, it's already been mailed, which is what I want to record in Table B. The next time I mail to any of those records, I will record a new record in Table B. So, there will be several records in Table B that relate to a record in Table A. Also, I will be importing multiple lists into Table A, each having unique data for Table B. There are also other times that I will need to run a search in Table A and create new records in Table B for that specific found set. Does this make more sense? I'm sorry I'm not very good at explaining things.

                    • 7. Re: Insert data into field of related table for all in found set
                      CarrieB.

                           When you say "Then you should not need to create new records in Table B until you actually mail a contact in Table A.", that is true, but I want to do it for an entire Found Set instead of doing it one record at a time.

                      • 8. Re: Insert data into field of related table for all in found set
                        philmodjunk
                             

                                  Yes, there are 2 fields in Table B. When I import a list into Table A, it's already been mailed, which is what I want to record in Table B.

                             Then why are you importing into Table A? Shouldn't you be importing this data directly into B?

                             If you are working with: Contacts---<Mail where Table A is contacts and Table B is Mail sent, you may even need to import into both tables, importing NEW contacts into the Contacts table and one new record for each person mailed into the Mail table.

                        • 9. Re: Insert data into field of related table for all in found set
                          CarrieB.

                               I'm sorry. I think I am making this sound much more confusing than it actually is. When I import a new list of contacts into Table A, I do also want to import their mailing information into Table B. I'm not quite sure how to do that at the same time as I only have one spreadsheet that contains data for the fields in Table A. I've only figured out how to manually input the information into Table B after the import (your script above worked well). Can I import data into 2 tables at the same time from the same import file? Maintaining the related records relationship?

                               Also, there are occasions where I will need to run a search on records already in the database and input unique mailing information for the found set into Table B.

                               Again, sorry for not explaining things well. I am a complete novice when it comes to database creation. I do appreciate all the assistance. This forum is a huge resource for me! :)

                          • 10. Re: Insert data into field of related table for all in found set
                            philmodjunk
                                 

                                      Can I import data into 2 tables at the same time from the same import file?

                                 No but you can import from the same import file more than once, selecting different columns of data to import into each table.

                                 

                                      Maintaining the related records relationship?

                                 That is the absolutely critical question to ask at this point and I'm glad to see you thinking in this direction. The answer is "maybe". It depends on the data in your source file and how you define relationships in your database. If you have a field in the source table that uniquely identifies each contact (a name is not sufficiently unique) such as an email address or other ID, then you can import that data into both tables to use to link your records. I would use that data as a temporary link. After importing into Table B, I'd use a script that uses that link to copy over an ID (auto-entered serial number in most cases) from Table A to a corresponding match field in Table B. And I'd use the relationship that matches by imported data only for this one task of using it to get the needed ID's from Table A. That way you can change this data in table A (such as when a contact get's a new email address) and not lose the link to records in Table B.

                                 

                                      Also, there are occasions where I will need to run a search on records already in the database and input unique mailing information for the found set into Table B.

                                 That is a whole new issue. It shouldn't directly affect the import methods that we are discussing, but may necessitate a different script for handling that specific task--it sounds like something that my original answer would handle. What do you mean by "Unique mailing information"? (at this point I don't even know if you are sending out emails or snail mail.) What is the purpose behind that task?

                                  

                            • 11. Re: Insert data into field of related table for all in found set
                              CarrieB.

                                   My tables are currently linked via an ID field that is an auto generated serial number.

                                   Yes, your script worked great for getting the correct information into Table B.

                                   Regarding the purpose behind my task ... This file is basically a master file for all of our prospecting. We purchase contact lists from a third party. We then mail and call the contacts on the list. Each new list I get has it's own unique identification number for our tracking purposes. This is what gets entered into Table B. There are instances where we may correct the contact information after it is uploaded to my database or place a contact on a "do not contact" status. Every time we get a new list from our third party, I want to upload it to my database and "compare" it against the information we already have stored. i.e. If the contact already exists in the database, then I don't want to import it again as a duplicate, but I do want to mark the one that is already there with the current list id for Table B. So, in that case, I would be creating new records in Table B for the Found Set of duplicate entries in Table A. Because we have many different variables for the lists we purchase, one individual contact will be on many different mailings lists.

                                   Does this make sense? :)

                              • 12. Re: Insert data into field of related table for all in found set
                                philmodjunk

                                     I's not clear to me how you use Table B. Exactly what info do you import into that table? How do you use that info? I understand your current description, but it's not clear what data is in A and what is in B and how you use the relationship between the two.

                                     I've assumed that every record in B represents a new instance of sending communication to a contact in A. But your last post has me reconsidering if that's actually the case.

                                     If my original assumptions were correct, then:

                                     

                                          There are instances where we may correct the contact information after it is uploaded to my database or place a contact on a "do not contact" status.

                                     Describes updates to data that should be stored in A, not B.

                                     In some cases, a "Matching" import can be used to update existing records with imported data. In other cases, you may have to import the data into a different table and use  a relationship to compare the values--which opens the possibility of a human mind doing some of the evaluation when comparing the data and deciding what to update.

                                • 13. Re: Insert data into field of related table for all in found set
                                  CarrieB.

                                       You are correct. Table A is the contact details: name, address, phone number, etc. And Table B represents each time they appear on a list. So, there is a list id field (Field A) and a date field (Field B) in Table B. We make updates to the records in Table A on a regular basis. We may update an address or makes notes or make note that they don't want to be contacted. So, let's say ABC Company got imported into my database on 1/15/2012. The related record in Table B now reflects "UniqueListID" in Field A and "1/15/2012" in Field B. I talk to ABC Company and make notes of our conversation and update their address in my database. On 1/24/14, ABC Company is on a new list of contacts I just imported into my database. The list id for this new list is "UniqueListID2". I don't want to keep the record I just imported for ABC Company because a record for ABC Company already exists and I don't want to update the existing record. I want to delete the record I just imported, but I want a new record in Table B for ABC Company that reflects "UniqueListID2".

                                  • 14. Re: Insert data into field of related table for all in found set
                                    philmodjunk
                                         

                                              I don't want to keep the record I just imported for ABC Company because a record for ABC Company already exists and I don't want to update the existing record. I want to delete the record I just imported,

                                         How do you determine that ABC Company is already in your database? What fields do you compare?

                                         It's possible to do two different types of imports to do what you describe but the details in both cases depend on your answer to my question.

                                         One method is to set up a field with the  "unique Values, validation only" options. This might be a field with an auto-enter calculation that combines data from several fields if you have to compare values in multiple fields to determine that a matching record already exists. With this setup, an Import records action will automatically omit records where the imported record matches this field.

                                         The other method is to set up an "update matching records in found set" import. First you show all records, then you do this import and the same field or fields that you'd use in the first method would be specified in this import as matching fields. This also omits duplicates but any other fields that you specify for the import will update the matching record in the table.

                                         Either way, there will be no record to delete, but it's up to you to decide which option works best for you. Sometimes you need to more human judgment involved. In those cases, you would import your contact data into a third table and use a relationship to match records in it against your current data in Table A. This allows a person to examine each record and decide what, if any updating need be done.

                                         

                                              but I want a new record in Table B for ABC Company that reflects "UniqueListID2".

                                         And that part should be possible with a simple add records import into Table B where you only specify the needed fields for your Table B table--including the data needed to match to the correct record in Table A.

                                    1 2 Previous Next