title: crystal reports group name formula using a record from a different line in table
body: <p>I am trying to create a group name formula using a record from a different line in the table than the line that the records contained in this group are linked to.</p>
  
  <p>First off, my data has the word <em>group</em> in it, so to avoid confusion I have italicized it to differentiate it from groups in Crystal Reports.</p>
  
  <p>My table that I am pulling the records for this group name from has:</p>
  
  <ul>
  <li>{GroupSection.Group} <em>Groups</em> of items in our inventory database</li>
  <li>{GroupSection.Section} Sections (these are like subgroups nested within each inventory <em>group</em>).</li>
  <li>{GroupSection.Description} The problems start here because the descriptions for {GroupSection.Group} and {GroupSection.Section} are both stored here. </li>
  </ul>
  
  <p>This is a sample of my table:</p>
  
  <pre><code>    {GroupSection.Group}    {GroupSection.Section}     {GroupSection.Description}
      3.00                      0.00                      PRECAST CONCRETE PRODUCT
      3.00                     50.00                      MISC PRECAST CONCRETE PRODUCT
      3.00                     99.00                      *Z* MISC PRECAST CONCRETE PRODUC
      4.00                      0.00                      CEMENT SUPPLIES
      4.00                     50.00                      MISC CEMENT SUPPLIES
      4.00                     99.00                      *Z* MISC CEMENT SUPPLIES
  </code></pre>
  
  <p>The first and fourth line in this table are descriptions for {GroupSection.Group} (they have a 0.00 in the {GroupSection.Section} line) and the rest are descriptions for {GroupSection.Section}. The actual data that is contained in this report is in a different table and has the same two fields as the first two in this table, but not the third field, hence the need to link to and use this table to make the descriptions of the group names. The other table has no records that link to the lines with 0.00. </p>
  
  <p>I want my Group Tree to look like this:</p>
  
  <pre><code>    3.  PRECAST CONCRETE PRODUCT
            50  MISC PRECAST CONCRETE
            99  *Z* MISC PRECAST CONCRETE
      4.  CEMENT SUPPLIES
            50  MISC CEMENT SUPPLIES
            99  *Z* MISC CEMENT SUPPLIES
  </code></pre>
  
  <p>This is the flawed formula I am using now in the Group Name formula for the top group:</p>
  
  <pre><code>ToText (left(Cstr({GroupSection.Group}),2))+ " " + ToText (If {GroupSection.Section} &lt;&gt; 0 then {GroupSection.Description} Else " ")
  </code></pre>
  
  <p>This is the Group Name formula for the inner nested group (working fine):</p>
  
  <pre><code>ToText (left(Cstr({GroupSection.Section}),2))+ " " + ToText ({GroupSection.Description})
  </code></pre>
  
  <p>This is what my Group Tree looks like now:</p>
  
  <pre><code>    3.  MISC PRECAST CONCRETE
            50  MISC PRECAST CONCRETE
            99  *Z* MISC PRECAST CONCRETE
      4.  MISC CEMENT SUPPLIES
            50  MISC CEMENT SUPPLIES
            99  *Z* MISC CEMENT SUPPLIES
  </code></pre>
  
  <p>As you can see, I need to get the name for the outer group from the first row in the table even though it's linking the records that are in the group to the second and third row in the table. Make sense?</p>
  
  <p>I have tried using previous() in the formula but it gives the following error: This function cannot be used because it must be evaluated later.</p>
  
  <p>Edit: I also just tried adding the same table again and linking it once to the <em>group</em> and once to the <em>section</em> and it worked for the group names, but now I have 1.9 million records, something really duplicated. So that won't work unless I can figure out how to fix the multiple records.</p>
  
  <p>The "." in the group name description is an unrelated problem. It's the best I can do to get 3.00 to display as 3 and 50.00 to display as 50.</p>
  
  <p>Thanks for any help!</p>
  
  <hr>
  
  <p>Edit Dec 30, 2013 for user @Promethean:</p>
  
  <p>Sorry I am new at SQL Command Tables. I took a command table from another report and did some changes to it. This is how far I got: </p>
  
  <pre><code>SELECT 
  "GroupSection"."Group",
  "GroupSection"."Section",
  "GroupSection"."Description"
  
   FROM   ("SpruceDotNet"."dbo"."InventoryCommon" "InventoryCommon" with (nolock)
  
  INNER JOIN 
  "SpruceDotNet"."dbo"."GroupSection" "GroupSection" with (nolock)
  ON "InventoryCommon"."Group"="GroupSection"."Group")
  
  WHERE
  "GroupSection"."Section"=0
  
  ORDER BY 
  "InventoryCommon"."Group"
  </code></pre>
  
  <p>Is there any chance you could use this info to modify your command table so a dummy like me can follow it? It looks like you are adding one of the fields twice, but I don't follow everything.</p>
  
  <p>And then how would you join the command table to the bigger table that contains the more detailed? I would have to use the right kind of join so I don't end up duplicating the records?</p>
  
  <p>Any help is greatly appreciated.</p>