3 Replies Latest reply on Apr 1, 2009 12:37 PM by philmodjunk

    copy fields in to new table.

    bb69

      Title

      copy fields in to new table.

      Post

      Hello,

       

      I have a script that copies information from one table to another.  I preform a search and then copy the values I want in the first table to the second table.  Now on the first table, most of the items are the same except for a few of them.  In the script below, the set variables with either 8 or 10 are the different projects.  

      What I want to do on the second table is check if the OEM number exists, then paste the missing fields from the first table.  If it does not exist, paste  to a new record.   I get 20 records when I do my search on the first table and I want it to be 10 records on the second table because every 2 records have the same OEM number.  What I am getting is the first 10 records appear like I want them to, showing both projects with the same OEM number, but I get 10 more records with just the second project.  It looks like this:

       

      <!--   [if gte mso 9]&amp;gt;&amp;lt;xml&amp;gt; &amp;lt;w:WordDocument&amp;gt;   &amp;lt;w:View&amp;gt;Normal&amp;lt;/w:View&amp;gt;   &amp;lt;w:Zoom&amp;gt;0&amp;lt;/w:Zoom&amp;gt;   &amp;lt;w:PunctuationKerning/&amp;gt;   &amp;lt;w:ValidateAgainstSchemas/&amp;gt;   &amp;lt;w:SaveIfXMLInvalid&amp;gt;false&amp;lt;/w:SaveIfXMLInvalid&amp;gt;   &amp;lt;w:IgnoreMixedContent&amp;gt;false&amp;lt;/w:IgnoreMixedContent&amp;gt;   &amp;lt;w:AlwaysShowPlaceholderText&amp;gt;false&amp;lt;/w:AlwaysShowPlaceholderText&amp;gt;   &amp;lt;w:Compatibility&amp;gt;    &amp;lt;w:BreakWrappedTables/&amp;gt;    &amp;lt;w:SnapToGridInCell/&amp;gt;    &amp;lt;w:WrapTextWithPunct/&amp;gt;    &amp;lt;w:UseAsianBreakRules/&amp;gt;    &amp;lt;w:DontGrowAutofit/&amp;gt;   &amp;lt;/w:Compatibility&amp;gt;   &amp;lt;w:BrowserLevel&amp;gt;MicrosoftInternetExplorer4&amp;lt;/w:BrowserLevel&amp;gt; &amp;lt;/w:WordDocument&amp;gt; &amp;lt;/xml&amp;gt;&amp;lt;![endif]   --><!--   [if gte mso 9]&amp;gt;&amp;lt;xml&amp;gt; &amp;lt;w:LatentStyles DefLockedState=&amp;quot;false&amp;quot; LatentStyleCount=&amp;quot;156&amp;quot;&amp;gt; &amp;lt;/w:LatentStyles&amp;gt; &amp;lt;/xml&amp;gt;&amp;lt;![endif]   --><!--   /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&amp;quot;&amp;quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&amp;quot;Times New Roman&amp;quot;;      mso-fareast-font-family:&amp;quot;Times New Roman&amp;quot;;} @page Section1      {size:612.0pt 792.0pt;      margin:72.0pt 90.0pt 72.0pt 90.0pt;      mso-header-margin:36.0pt;      mso-footer-margin:36.0pt;      mso-paper-source:0;} div.Section1      {page:Section1;}    --><!--   [if gte mso 10]&amp;gt; &amp;lt;style&amp;gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&amp;quot;Table Normal&amp;quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&amp;quot;&amp;quot;;      mso-padding-alt:0cm 5.4pt 0cm 5.4pt;      mso-para-margin:0cm;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&amp;quot;Times New Roman&amp;quot;;      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} table.MsoTableGrid      {mso-style-name:&amp;quot;Table Grid&amp;quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      border:solid windowtext 1.0pt;      mso-border-alt:solid windowtext .5pt;      mso-padding-alt:0cm 5.4pt 0cm 5.4pt;      mso-border-insideh:.5pt solid windowtext;      mso-border-insidev:.5pt solid windowtext;      mso-para-margin:0cm;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&amp;quot;Times New Roman&amp;quot;;      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} &amp;lt;/style&amp;gt; &amp;lt;![endif]   -->

      Oem number    Project 1   Project 2   Price / unit project 1   Price / unit project 2

      1234              One          Two           $1                           $2

      1234                             Two                                         $2

       

       I would like to just have the first OEM number and not both.

      the script is in the reply, can anyone help me with it?

       

      <!--   [if gte mso 9]&amp;gt;&amp;lt;xml&amp;gt; &amp;lt;w:WordDocument&amp;gt;   &amp;lt;w:View&amp;gt;Normal&amp;lt;/w:View&amp;gt;   &amp;lt;w:Zoom&amp;gt;0&amp;lt;/w:Zoom&amp;gt;   &amp;lt;w:PunctuationKerning/&amp;gt;   &amp;lt;w:ValidateAgainstSchemas/&amp;gt;   &amp;lt;w:SaveIfXMLInvalid&amp;gt;false&amp;lt;/w:SaveIfXMLInvalid&amp;gt;   &amp;lt;w:IgnoreMixedContent&amp;gt;false&amp;lt;/w:IgnoreMixedContent&amp;gt;   &amp;lt;w:AlwaysShowPlaceholderText&amp;gt;false&amp;lt;/w:AlwaysShowPlaceholderText&amp;gt;   &amp;lt;w:Compatibility&amp;gt;    &amp;lt;w:BreakWrappedTables/&amp;gt;    &amp;lt;w:SnapToGridInCell/&amp;gt;    &amp;lt;w:WrapTextWithPunct/&amp;gt;    &amp;lt;w:UseAsianBreakRules/&amp;gt;    &amp;lt;w:DontGrowAutofit/&amp;gt;   &amp;lt;/w:Compatibility&amp;gt;   &amp;lt;w:BrowserLevel&amp;gt;MicrosoftInternetExplorer4&amp;lt;/w:BrowserLevel&amp;gt; &amp;lt;/w:WordDocument&amp;gt; &amp;lt;/xml&amp;gt;&amp;lt;![endif]   --><!--   [if gte mso 9]&amp;gt;&amp;lt;xml&amp;gt; &amp;lt;w:LatentStyles DefLockedState=&amp;quot;false&amp;quot; LatentStyleCount=&amp;quot;156&amp;quot;&amp;gt; &amp;lt;/w:LatentStyles&amp;gt; &amp;lt;/xml&amp;gt;&amp;lt;![endif]   --><!--   [if !mso]&amp;gt;&amp;lt;div classid=&amp;quot;clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D&amp;quot; id=ieooui&amp;gt;&amp;lt;/div&amp;gt; &amp;lt;style&amp;gt; st1\:*{behavior:url(#ieooui) } &amp;lt;/style&amp;gt; &amp;lt;![endif]   --><!--   /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&amp;quot;&amp;quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&amp;quot;Times New Roman&amp;quot;;      mso-fareast-font-family:&amp;quot;Times New Roman&amp;quot;;} @page Section1      {size:612.0pt 792.0pt;      margin:72.0pt 90.0pt 72.0pt 90.0pt;      mso-header-margin:36.0pt;      mso-footer-margin:36.0pt;      mso-paper-source:0;} div.Section1      {page:Section1;}    --><!--   [if gte mso 10]&amp;gt; &amp;lt;style&amp;gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&amp;quot;Table Normal&amp;quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&amp;quot;&amp;quot;;      mso-padding-alt:0cm 5.4pt 0cm 5.4pt;      mso-para-margin:0cm;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&amp;quot;Times New Roman&amp;quot;;      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} &amp;lt;/style&amp;gt; &amp;lt;![endif]   -->

      Brian

        • 1. Re: copy fields in to new table.
          bb69
            

          The script:

          Enter Browse Mode

          Go to Layout [ “Consolidated FATS Reports” (Consolidated FATS Reports) ]

          Delete All Records

          [ No dialog ]

          Go to Layout [ “Data Entry” (Copy of FatSim) ]

          Show All Records

          Perform Find [ Specified Find Requests: Find Records; Criteria: Copy of FatSim::store spare project 10: “>100”

          Find Records; Criteria: Copy of FatSim::store spare project 8: “>100” ]

          [ Restore ]

          Go to Record/Request/Page

          [ First ]

          Loop

          Set Variable [ $projectfats; Value:Copy of FatSim:: Project8 ]

          Set Variable [ $projectfats2; Value:Copy of FatSim:: Project10 ]

          Set Variable [ $oemnumber; Value:Copy of FatSim:: OEM ]

          Set Variable [ $oemnumber2; Value:Copy of FatSim:: oem 2 ]

          Set Variable [ $stockid; Value:Copy of FatSim:: ID Stock ]

          Set Variable [ $description; Value:Copy of FatSim:: Description ]

          Set Variable [ $pricefats; Value:Copy of FatSim:: Prix unitaire moyen 8 ]

          Set Variable [ $pricefats2; Value:Copy of FatSim:: Prix unitairemoyen 10 ]

          Set Variable [ $sparefats; Value:Copy of FatSim:: store spare project 8 ]

          Set Variable [ $sparefats2; Value:Copy of FatSim:: store spare project 10 ]

          Go to Layout [ “Consolidated FATS Reports” (Consolidated FATS Reports) ]

          New Record/Request

          Go to Record/Request/Page

          [ First ]

          Loop

          Set Variable [ $recordnumber; Value:Get ( RecordNumber ) ]

           If [ Consolidated FATS Reports::${OEM} = $oemnumber ]

              If [ IsEmpty ( Consolidated FATS Reports:: Project FATS ) ]

              Set Field [ Consolidated FATS Reports:: Project FATS; $projectfats ]

              Set Field [ Consolidated FATS Reports:: Price per unit of item Fats; $pricefats ]

              Set Field [ Consolidated FATS Reports:: store spares Fats; $sparefats ]

           Else If [ IsEmpty ( Consolidated FATS Reports:: Project FATS2 ) ]

              Set Field [ Consolidated FATS Reports:: Project FATS2; $projectfats2 ]

              Set Field [ Consolidated FATS Reports:: Price per unit of item Fats 2; $pricefats2 ]

              Set Field [ Consolidated FATS Reports:: store spares Fats 2; $sparefats2 ]

           End If

           Else If [ IsEmpty ( Consolidated FATS Reports:: ${OEM} ) ]

              Go to Record/Request/Page

              [ Last ]

              Set Field [ Consolidated FATS Reports:: Project FATS; $projectfats ]

              Set Field [ Consolidated FATS Reports:: Project FATS2; $projectfats2 ]

              Set Field [ Consolidated FATS Reports:: OEM; $oemnumber ]

              Set Field [ Consolidated FATS Reports:: OEM2; $oemnumber2 ]

              Set Field [ Consolidated FATS Reports:: Stock ID; $stockid ]

              Set Field [ Consolidated FATS Reports:: Description; $description ]

              Set Field [ Consolidated FATS Reports:: Price per unit of item Fats; $pricefats ]

              Set Field [ Consolidated FATS Reports:: Price per unit of item Fats 2; $pricefats2 ]

              Set Field [ Consolidated FATS Reports:: store spares Fats; $sparefats ]

              Set Field [ Consolidated FATS Reports:: store spares Fats 2; $sparefats2 ]

              Go to Record/Request/Page [ $recordnumber ]

              [ No dialog ]

            End If

          Go to Record/Request/Page

          [ Next; Exit after last ]

          End Loop

          Go to Layout [ “Data Entry” (Copy of FatSim) ]

          Go to Record/Request/Page

          [ Next; Exit after last ]

          End Loop

          Go to Layout [ “Consolidated FATS Reports” (Consolidated FATS Reports) ]

           

           

           

           

           

          • 2. Re: copy fields in to new table.
            bb69
              

            Hello,

             

            I found it on my own.

             

            After I do my find I sort the records by OEM number in the first table.  I then only create a new record in the second table if  the OEM is not the same in the second table as it is in the first.  Otherwise I paste the fields to the current record if the OEM number is the same.

             

            <!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:WordDocument&gt;   &lt;w:View&gt;Normal&lt;/w:View&gt;   &lt;w:Zoom&gt;0&lt;/w:Zoom&gt;   &lt;w:PunctuationKerning/&gt;   &lt;w:ValidateAgainstSchemas/&gt;   &lt;w:SaveIfXMLInvalid&gt;false&lt;/w:SaveIfXMLInvalid&gt;   &lt;w:IgnoreMixedContent&gt;false&lt;/w:IgnoreMixedContent&gt;   &lt;w:AlwaysShowPlaceholderText&gt;false&lt;/w:AlwaysShowPlaceholderText&gt;   &lt;w:Compatibility&gt;    &lt;w:BreakWrappedTables/&gt;    &lt;w:SnapToGridInCell/&gt;    &lt;w:WrapTextWithPunct/&gt;    &lt;w:UseAsianBreakRules/&gt;    &lt;w:DontGrowAutofit/&gt;   &lt;/w:Compatibility&gt;   &lt;w:BrowserLevel&gt;MicrosoftInternetExplorer4&lt;/w:BrowserLevel&gt; &lt;/w:WordDocument&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if gte mso 9]&gt;&lt;xml&gt; &lt;w:LatentStyles DefLockedState=&quot;false&quot; LatentStyleCount=&quot;156&quot;&gt; &lt;/w:LatentStyles&gt; &lt;/xml&gt;&lt;![endif]  --><!--  [if !mso]&gt;&lt;object classid=&quot;clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D&quot; id=ieooui&gt;&lt;/object&gt; &lt;style&gt; st1\:*{behavior:url(#ieooui) } &lt;/style&gt; &lt;![endif]  --><!--  /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal      {mso-style-parent:&quot;&quot;;      margin:0cm;      margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:12.0pt;      font-family:&quot;Times New Roman&quot;;      mso-fareast-font-family:&quot;Times New Roman&quot;;} @page Section1      {size:612.0pt 792.0pt;      margin:72.0pt 90.0pt 72.0pt 90.0pt;      mso-header-margin:36.0pt;      mso-footer-margin:36.0pt;      mso-paper-source:0;} div.Section1      {page:Section1;}   --><!--  [if gte mso 10]&gt; &lt;style&gt; /* Style Definitions */ table.MsoNormalTable      {mso-style-name:&quot;Table Normal&quot;;      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:&quot;&quot;;      mso-padding-alt:0cm 5.4pt 0cm 5.4pt;      mso-para-margin:0cm;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:&quot;Times New Roman&quot;;      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} &lt;/style&gt; &lt;![endif]  -->


            • 3. Re: copy fields in to new table.
              philmodjunk
                 Just a note: it's possible to accept your own post as a solution and thus close the thread.