5 Replies Latest reply on Jul 9, 2014 1:43 PM by ErichBrandt

    Import new2 records only

    ErichBrandt

      Title

      Import new2 records only

      Post

           I have researched this and could not find a simple answer. Perhaps by design concept was incorrect.

           Here is my question..

           I have two tables that I am importing data to/from. My source table (Master_Service_Index) and my destination (Pricing_Template_Lines).

           I am wanting to import new records that are added to the (Master_Service_Index) to the (Pricing_Template_Lines) via a script.

           I have one script that imports all the records when a New template is created , this works fine. I find all records in the source table, import the records, find all the records that don't have a Template_ID, and perform a 'replace all'' with the current template ID (stored as a temp global). Works like it should.

           When I want to update a particular template with any new records that have been created in the Master_Service_Index, I need to run a separate update script that will find new records only, assign the pricing_template_ID.

           Since I have a unique repair ID in both tables, I thought I could simple do an import based on the Repair_ID match field and select the ADD NEW RECORDS option. This works fine but I have one major issue. When I perform the initial New template Import, it imports a default price, that can then be edited by the user. When I perform the Update script to import the new records, it sets the price field contents back to the source table default pricing for all the records for that template.

           I tried editing the mapping so that it would not import the Price field, but it still updated all the records with the source table pricing. If I tried using the adding new records only , I got duplicates (since it does not know which records are new).

            

           I hope this is not too confusing but basically, when receiving a order and assigning an order a customer ID (which in turn has a pricing template ID assigned to it)...when a repair id is entered, the pricing for that template and repair ID is looked up from the Pricing_template_Lines.  

           If I did not have an issue with the pricing being reverted back to the source table value, then I could use the update/ add new records option.

            

           Is there a simple option I can use, or am I going to have to use a work-around involving additional tables?

           Here are the tables and relevant fields.

           Master_Service_Index                       Pricing_Template_Lines

           N/A                            >                     Template_ID

           Repair_ID                  >                     Repair_ID

           Repair_Description    >                     N/A

           Repair_Cost_Default  >                    Repair_Cost   (Initial Import Script Only)

            

            

        • 1. Re: Import new2 records only
          ErichBrandt

               using FMP 12

          • 2. Re: Import new2 records only
            philmodjunk

                 The key to what you want to do is contained in your first post:

                 

                      I find all records in the source table, import the records,

                 If there is some field in your source table that identifies all newly added records as "new", you can perform a find that finds only those records for the specified template that are also marked as "new" and then only import those records.

            • 3. Re: Import new2 records only
              ErichBrandt

                   Well,

                   I thought about creating a field in the source table 'new'. During the record creating script it would set that field to 'new'. I could then import the new records only. The problem with this approach is that I will need to perform this script for several different templates. Once the records are imported I was planning on clearing the 'new'. If I do that then if I update a different template I will have nothing marked as new. I can't leave the field as new because if records are added to the source table later then I would get duplicates during the next import.

                    

              • 4. Re: Import new2 records only
                philmodjunk
                     

                          I can't leave the field as new because if records are added to the source table later then I would get duplicates during the next import.

                     After you find these records, Replace Field contents can change the value in this field for all records in the found set you just pulled up.

                     A simpler way would be to add a date field that auto-enters the creation date. You can then perform a find to find all records with a creation date later than the date of the last time that you imported this data.

                     You may find this thread of scripted find examples to be helpful: Scripted Find Examples

                • 5. Re: Import new2 records only
                  ErichBrandt

                       Great Idea with the date!! Solves my problem.  Thanks so much for the idea.