6 Replies Latest reply on Jul 9, 2015 12:39 PM by skashanchi

    How do I relate imported child records to their parent records?

    skashanchi

      I am sure this is not very complicated, but I have not done it before and do not want to risk screwing up existing data in my database.

       

      I have a situation where I have a Customers table.

      I have a second table called Trips that contains child records for customers.

      Users create records in the Trips table and assign them to a customer record in the Customers table.

      The records in Trips are related to the records in Customers using the Customer_ID field.

       

      But every week I also have to import trips created in another software into my FM solution and combine them with the records entered directly into the FM solution and then run reports on them. I would export the data from other software into an Excel sheet and then import into FM solution.

       

      So how do I "relate" the imported records into the Trips table to their parent records in the Customers table? I need to write a script that goes through the imported records using some common field and matches that to the records in the Customers field and then assign the Customer_ID field to the new Trips records.

       

      There is one a few common fields between my FM solution and the other software. The trips coming in from other software have Customer Last Name, Customer First Name and State ID. Technically it is possible that 2 customers could have same First and Last name, so I think I would want to use the State ID as it is just one field and can not be duplicated.


      So how do I step through imported data, use the State ID field as a lookup to find its matching parent record in the Customers table and then copy the parent record's Customer_ID to the imported record?


      Thank you.

        • 1. Re: How do I relate imported child records to their parent records?
          erolst

          skashanchi wrote:

          So how do I step through imported data, use the State ID field as a lookup to find its matching parent record in the Customers table and then copy the parent record's Customer_ID to the imported record?

           

          Solution à la quick'n'dirty allows you to this w/o a script:

           

          • create a relationship by the three mentioned fields, using a new TO of Customers called, say, Customers_byNameAndState

          • update the Trips::id_customer field with “Replace Field Contents” (RFC) and the simple formula

           

          Customers_byNameAndState::id

           

          Make sure to have a current backup handy – RFC cannot be undone!

           

          If you want to handle more complicated cases – namely

           

          • IsEmpty ( Customers_byNameAndState::id ) and

          • Count ( Customers_byNameAndState::id ) > 1


          you will have to (well, should) write a script** with a loop and Set Field[], and possibly use an additional field like “updateStatus” to denote customer is missing / multiple matching customers.


          **Or use several RFCs – but …

           

          Be aware that RFC is not multi-user friendly: if it encounters locked records, it will not update them, and the error message will not specify the affected (or un-affected, if you will) records. Writing a script would also allow you to trap for locking errors and e.g. keep a log.

          • 2. Re: How do I relate imported child records to their parent records?
            richardsrussell

            To emphasize and underline what erolst said:

            Be aware that [Replace Field Contents] is not multi-user friendly!


            I generally have an intermediate field that I import data into so I can check it over thoroly before running a 2nd RFC operation to move the contents of that field into their final resting place. Better to have something go wrong there than in the final data transfer.

            • 3. Re: How do I relate imported child records to their parent records?
              skashanchi

              Any import work will be done after hours, so I will be the only person in the database, so I think we are pretty safe there.

              • 4. Re: How do I relate imported child records to their parent records?
                skashanchi

                I think I understand the concept here, but I am still a little unclear as to the actual steps necessary.

                 

                1. I create another table occurrence of the Customer table.

                2. Create a relationship between the 3 fields: CustomerFirstName, CustomerLastName and StateID

                3. What exactly is the procedure for using the RFC command?

                I have imported my records and am looking at them in a list view. do I have to enter the target field and then use the RFC command? I have tried that and it doe snot seem to do anything.

                 

                Can you please expand on your answer a little bit.

                 

                Thank you.

                • 5. Re: How do I relate imported child records to their parent records?
                  erolst

                  skashanchi wrote:

                  1. I create another table occurrence of the Customer table.

                  Yes

                  2. Create a relationship between the 3 fields: CustomerFirstName, CustomerLastName and StateID

                  Yes

                  skashanchi wrote:

                  I have imported my records and am looking at them in a list view. do I have to enter the target field and then use the RFC command? I have tried that and it doe snot seem to do anything.

                   

                  Make sure you have the found set you want to update, put the cursor into the field to update, select RFC from the Records menu, in the dialog select “Calculated Result”, as result simply select the primary id field from the new Customer TO, confirm the calc, then click OK to confirm the RFC dialog proper and thus commence the RFC process (don't hit Enter - Cancel is the (safe) default).

                   

                  Don't forget to try this on a backup. (Mandatory Public Health & Safety Announcement)

                  • 6. Re: How do I relate imported child records to their parent records?
                    skashanchi

                    Thanks. It's working.

                     

                    But I have already hit the issue that you had mentioned above in that we are seeing Trip records for Customers that are not in the FM solution. So looks like I do need to write a script that will check for that.

                     

                    In fact I have to somehow isolate those problem trips, so we can look a the Customer Name and add it to the database. Not going to automate that part at this point. Just need to spit out the Trips hat could not be added.