Skip to main content
J
jamie61880
Inspiring
April 8, 2011
Question

Combine 2 rows into 1 row

  • April 8, 2011
  • 1 reply
  • 4337 views

Hi,

I'm not sure if this is possible or not, but I have 2 different columns in a display page- 1 for PNR Initials and 1 for BOM  Initials, but when I insert everything into the database, I only have 1  column to handle both the PNR and BOM initials. The way I tell them  apart is with another column that is called Document_Type. If it says  "PNR Req", then I know to output it into the PNR Initials column. If it  says "BOM Req", then I know to outupt it into the BOM Initials column.

      Although the inserting works correctly, the display page does not put  them next to each other since the data is dynamic and it's outputting each column's initials into their own rows. For instance, I updated the PNR Initial to "VP"  and hit the update or insert button and it inserted the PNR Initial  into the database. Then I went back to the display page to insert the  BOM Initial to "AG". It inserted it correctly. But now when I go back to  the display page, there are 2 different rows with the first row that  has the drop down menu in the PNR Column and "AG" in the BOM column. But  then there is another row that has "VP" in the PNR column and the drop  down menu in the BOM column. Since I've updated both columns, it should  just show the PNR Initials under the PNR column once and the BOM Initials under the BOM column once, but all in the same row and not on 2 separate rows. How do I  get it so they show up on the same row? The 2 different rows are indicated by the APVID 5 and 9, but they both are linked to the same item number 5 in the Items table. Here's what my display page  looks like now:

APVID
APV_ItemID
Part_Number
PNRBOM
55aaa5Drop Down MenuAG
95aaa5VPDrop Down Menu

It should look like this:

APVID APV_ItemID
Part_Number
PNRBOM
5 and 95aaa5VPAG

The  APV_ItemID is linked to a column in another table. That table is the  ECO_Items table that has the ItemID column that has all the items. The  info. above for the APV_ItemID and Part number can always all be the  same, but the PNR and BOM columns have to display the correct initals in  each on 1 line. I tried the maxrows and the group function on the output query, but those did  not work. I've also tried some IF statements, but they do not work either. Any other ideas? Is this even possible?Thanks.

Andy

    This topic has been closed for replies.

    1 reply

    D
    Dan_Bracuk
    Inspiring
    April 8, 2011

    Sounds like a job for the group by attribute of the cfoutput tag.

    J
    jamie61880Author
    Inspiring
    April 8, 2011

    Dan,

         That's what I tried, but it only displays the first item row with the PNR Initials, but then the BOM initials have a drop down menu still in it and not the "AG" initials that are in the database already for that item. It basically deletes the 2nd row  or the 2nd item and only displays the first row of information. Do I need to run 2 different output queries with the group attribute? One for the PNR Initals column and one for the BOM initials column? I've tried this too, but the table gets really messed up and doesn't line up normal.

    Andy

    J
    jamie61880Author
    Inspiring
    April 20, 2011

    jamie61880 wrote:

    Is it because they are grouped or something?

    Yes, exactly.  Because that piece of your code is executed TWICE.  Once for each row of the returned record set.  So in one row there is a

    BOM value so it is output.  In the other row there is not a BOM value so the select control is output.

    To see this add this bit of code to your output:

    <cfif Document_Type EQ "BOM Req" and Approval_Initials is not "">
    <td align="center">#currentRow# - #Approval_Initials#</td>
    <cfelse>
    <td align="center">
    #currentRow# -
    <select name="BOM_Approval_Initials#ItemID#">
    <option value="">BOM Initials</option>
    <cfloop query="ShowInitials">
    <cfif Engineer_Initials EQ 1>
    <option value="#Initials#">#Initials#</option>
    </cfif>
    </cfloop>
    </select>
    </td>
    </cfif>
    <cfif Document_Type EQ "BOM Req" and Approval_Initials is not "">
    <td align="center">#Approval_Initials#</td>
    <cfelse>
    <td align="center">
    <select name="BOM_Approval_Initials#ItemID#">
    <option value="">BOM Initials</option>
    <cfloop query="ShowInitials">
    <cfif Engineer_Initials EQ 1>
    <option value="#Initials#">#Initials#</option>
    </cfif>
    </cfloop>
    </select>
    </td>
    </cfif>

    You need to adjust your code to inspect BOTH rows.  A couple of quick ideas that might help you out.

    Check out the valueList() function.  This function returns a list of ALL values in a column from a record list.  You could then examin that list to see if there are any initials or not.

    OR

    IF your record set is ALWAYS exactly two rows, you can examin both rows by adding a row identify to your query reference.

    That could look something like:

    <cfif Document_Type EQ "BOM Req"
      AND PNRBOMSearch.Approval_Initials[1] is not ""
      AND PNRBOMSearch.Approval_Initials[2] is not "">

    Ilssac,

           I have tried both the Current Row function and the identify to my query reference, but they do not do anything different, unless I'm not writing it out correctly. I did the CurrentRow function exactly what you have below, but that did not do anything except put numbers with a dash next to each drop down menu. Do I need to set CurrentRow first or something? How do you use the CurrentRow function? What does it do? Here's what I did for identifying to my query reference:

    <cfif Document_Type EQ "BOM Req" And PNRBOMSearch.Approval_Initials[1] is not ""
      AND PNRBOMSearch.Approval_Initials[2] is not "">
    <td align="center">#Approval_Initials#</td>

    <cfelseif Document_Type EQ "BOM Req" And PNRBOMSearch.Approval_Initials[1] is ""
    And PNRBOMSearch.Approval_Initials[2] is "">

    <td align="center">
    <select name="BOM_Approval_Initials#ItemID#">
    <option value="">BOM Initials</option>
    <cfloop query="ShowInitials">
    <cfif Engineer_Initials EQ 1>
    <option value="#Initials#">#Initials#</option>
    </cfif>
    </cfloop>
    </select>
    </td>

    <cfelse>
    <td align="center">
    <select name="BOM_Approval_Initials#ItemID#">
    <option value="">BOM Initials</option>
    <cfloop query="ShowInitials">
    <cfif Engineer_Initials EQ 1>
    <option value="#Initials#">#Initials#</option>
    </cfif>
    </cfloop>
    </select>
    </td>


    </cfif>

    This code still outputs both the drop down and the initials next to each other.

    I did find some code about using the Mod operator. It kind of worked, but only for my first record set and not the 2nd one. The 2nd one still displayed the drop down and the initials next to each other. Here's what I did with that below. I tried writing the same code with it eq 1 and that worked on the one that I have 1 row filled out, but then it didn't work for the ones where I had 2 rows filled out. I also tried writing another cfelseif statement with this code, but that didn't do anything.

    <cfif Document_Type EQ "BOM Req" and Approval_Initials is not "">
    <td align="center">#Approval_Initials#</td>

    <cfelseif (PNRBOMSearch.CurrentRow mod 2 eq 0 AND NOT PNRBOMSearch.CurrentRow eq
    PNRBOMSearch.RecordCount)>

    <td align="center">
    <select name="BOM_Approval_Initials#ItemID#">
    <option value="">BOM Initials</option>
    <cfloop query="ShowInitials">
    <cfif Engineer_Initials EQ 1>
    <option value="#Initials#">#Initials#</option>
    </cfif>
    </cfloop>
    </select>
    </td>

    <cfelseif Approval_Initials is "">
    <td align="center">
    <select name="BOM_Approval_Initials#ItemID#">
    <option value="">BOM Initials</option>
    <cfloop query="ShowInitials">
    <cfif Engineer_Initials EQ 1>
    <option value="#Initials#">#Initials#</option>
    </cfif>
    </cfloop>
    </select>
    </td>


    </cfif>

    Andy

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices