Compare records and update

[Jason Congerton]

Hi

I have a table which stores measurements, the measurements have a corresponding letter assoiciated with them i.e A = 0.34 B = 0.35 etc.

Measurements are directly related to the productPart table. The productPart table stores part no info relating to the product and measurements.

One product main contain 1 or multiple part no's i.e

Product: UFC-SLOTTED
Part No: 21U, 22U, 23U, 24U

Each part number has it's own set of measurments i.e

Part No: 21U
Measurements: A=0.23, B=0.34, C=0.23

Part No: 22U
Measurements: A=0.23, B=0.34, C=0.23, D=0.45, E=0.46

As you can see the part numbers can use a varying amount of measurments, which is where my question comes.

Part No: 21U has 3 measurments; A,B,C
Part No: 22U has 5 measuremnts; A,B,C,D,E

I need to fill the gaps automatically to produce the following and place NA or a null value.

Part No: 21U
Measurements: A=0.23, B=0.34, C=0.23, D=NA, E=NA,

Part No: 22U
Measurements: A=0.23, B=0.34, C=0.23, D=0.45, E=0.46

Once the user has completed inserting the measurements for the part no, i will offer a button to finalise product insert.

I know i will need to loop around the the measurement table for each part no, but how do i know when to insert the correct title(i.e D) and the null value, what if statment will i use, i'm sure it must

be possible. Table structure below;

productPart
productPartID(uid), partNo, lamNo

measurement
mID(uid),mPartID(link to productPart), mTitle, measOne

Thanks


Jason



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328263
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Robert Harrison-6]

In your log where you output... add

        <cfif trim(a) is "">NA<cfelse>#A#</cfif>

If you want to write it just once instead of for each field, evaluate the
var name... A,B,C, etc.


Robert B. Harrison
Director of Interactive Services
Austin & Williams
125 Kennedy Drive, Suite 100
Hauppauge NY 11788
P : 631.231.6600 Ext. 119
F : 631.434.7022
http://www.austin-williams.com 

Great advertising can't be either/or.  It must be &.

Plug in to our blog: A&W Unplugged
http://www.austin-williams.com/unplugged



-----Original Message-----
From: Jason Congerton [mailto:jason@...]
Sent: Wednesday, November 11, 2009 10:22 AM
To: cf-talk
Subject: Compare records and update


Hi

I have a table which stores measurements, the measurements have a
corresponding letter assoiciated with them i.e A = 0.34 B = 0.35 etc.

Measurements are directly related to the productPart table. The productPart
table stores part no info relating to the product and measurements.

One product main contain 1 or multiple part no's i.e

Product: UFC-SLOTTED
Part No: 21U, 22U, 23U, 24U

Each part number has it's own set of measurments i.e

Part No: 21U
Measurements: A=0.23, B=0.34, C=0.23

Part No: 22U
Measurements: A=0.23, B=0.34, C=0.23, D=0.45, E=0.46

As you can see the part numbers can use a varying amount of measurments,
which is where my question comes.

Part No: 21U has 3 measurments; A,B,C
Part No: 22U has 5 measuremnts; A,B,C,D,E

I need to fill the gaps automatically to produce the following and place NA
or a null value.

Part No: 21U
Measurements: A=0.23, B=0.34, C=0.23, D=NA, E=NA,

Part No: 22U
Measurements: A=0.23, B=0.34, C=0.23, D=0.45, E=0.46

Once the user has completed inserting the measurements for the part no, i
will offer a button to finalise product insert.

I know i will need to loop around the the measurement table for each part
no, but how do i know when to insert the correct title(i.e D) and the null
value, what if statment will i use, i'm sure it must

be possible. Table structure below;

productPart
productPartID(uid), partNo, lamNo

measurement
mID(uid),mPartID(link to productPart), mTitle, measOne

Thanks


Jason





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328270
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Jason Congerton]

Hi Robert

Thanks for your reply, although I'm not sure if I understand your response. Looking at the code you supplied, will this not just display NA if "a" is empty, my problem is "a" might not be even created.

part no 23a has 3 measurements: a=0.32,b=0.34,c=0.45
part no 24a has 2 measurements: a=0.12,b=0.43

for part no 24a, I would need to display c=na, although c does not exist for this part no. My final output should resemble this

Part NO: A     B    C    D
23a      0.32  0.34 0.35 0.36
23a      0.32  0.34 NA   NA
23a      NA    0.34 0.12 0.34

If I have misunderstood you - sorry.

Jason

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328279
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Robert Harrison-6]

Then, in your loop use:

<cfif isdefined("myvar")>
        <cfif trim(myvar) is "">#myvar#<cfelse>NA</cfif>
<cfelse>
NA
</cfif>

OK?

Robert B. Harrison
Director of Interactive Services
Austin & Williams
125 Kennedy Drive, Suite 100
Hauppauge NY 11788
P : 631.231.6600 Ext. 119
F : 631.434.7022
http://www.austin-williams.com 

Great advertising can't be either/or.  It must be &.

Plug in to our blog: A&W Unplugged
http://www.austin-williams.com/unplugged

 

__________ Information from ESET Smart Security, version of virus signature
database 4597 (20091111) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328280
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Jason Congerton]

Thank you again for your response, sorry for time its taking for me to get my head around this!!

Have a look at this

http://tvpressings.jasoncongerton.co.uk/transteel-products/test.cfm?productid=2

all looks good, however no NA in boxes with no measurements. however this is only becauase the top two rows contains the top most amount of measurements, all the way to T

now look at this, i have placed the letters above the measurment to show they are out of align with the top headers.

http://tvpressings.jasoncongerton.co.uk/transteel-products/test.cfm?productid=29

I need the ooutput to align with the top headers ABC.

code below

Jason


<!---get the measurements and parts no's--->

<cfquery name="get_parts" datasource="#application.dsn#">
    SELECT p.lamNo,
    p.coreNo,
    p.pattern,
        p.productPartID,
        p.partNo,
        m.measOne,
        m.measTwo,
        m.mTitle
    FROM productPart p INNER JOIN
        measurement m ON p.productPartID = m.mPartID
      WHERE p.partProdID = <cfqueryparam value="#productID#" cfsqltype="cf_sql_integer">
    ORDER BY  p.order, p.lamNo, p.coreNo, p.pattern, m.mtitle
</cfquery>

<!---//get the letters for the top most row--->

<cfquery name="get_alpha" datasource="#application.dsn#">
SELECT tableLetter
FROM tableSort
WHERE tableLetter IN (SELECT mTitle
FROM measurement WHERE mPartID IN (SELECT productPartID
FROM productPart WHERE partProdID =  <cfqueryparam value="#productID#" cfsqltype="cf_sql_integer">))
ORDER BY tableLetter ASC
</cfquery>



<table>
<tr>
<td>
<cfif get_parts.lamNo NEQ "">LAM NO:</cfif><cfif get_parts.coreNo NEQ "">CORE NO:</cfif>
<cfif get_parts.pattern NEQ "">Pattern:</cfif></td>
<td><strong>PART NO:</strong></td>
<cfoutput query="get_alpha">
<td style="width:70px; font-family:Verdana, Geneva, sans-serif; font-size:10px; text-align:center;"><strong>#tableLetter#</strong></td>
</cfoutput>
</tr>  
<cfoutput query="get_parts" group="lamNo">
<td>#lamNo#</td>
<td><strong>#partNo#</strong></td>
<cfset ctr = 0 />
<cfoutput group="mTitle">
<td>#mTitle#<br />#decimalFormat(measOne)#<cfif measTwo GT 0> X #decimalFormat(measTwo)#</cfif></td>
<cfset ctr = ctr+1 />
</cfoutput>
<cfif ctr lt get_alpha.recordcount>
<cfloop from="#ctr#" to="#get_alpha.recordcount#" index="c">
<td> </td>
</cfloop>
</cfif>
 </tr>
</cfoutput>
   
</cfif>
   
   
</table>

         
     
        </tr>  
               

   
 
   
    <cfoutput query="get_parts" group="lamNo">
   
            <td style="width:80px; font-family:Verdana, Geneva, sans-serif; font-size:10px;"><strong>#lamNo#</strong></td>
            <td style="width:80px; font-family:Verdana, Geneva, sans-serif; font-size:10px;"><strong>#partNo#</strong></td>
            <cfset ctr = 0 />
            <cfoutput group="mTitle">
                <td style="width:70px; font-family:Verdana, Geneva, sans-serif; font-size:10px; text-align:center;">#mTitle#<br />#decimalFormat(measOne)#<cfif measTwo GT 0> X #decimalFormat(measTwo)#</cfif></td>
                <cfset ctr = ctr+1 />
            </cfoutput>
            <cfif ctr lt get_alpha.recordcount>
                <cfloop from="#ctr#" to="#get_alpha.recordcount#" index="c">
                    <td> </td>
                </cfloop>
            </cfif>
        </tr>
    </cfoutput>
   
    </cfif>
   
   
</table>


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328295
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Jason Congerton]

Hi

Just wondered if anyone had any ideas?

Jason

> Thank you again for your response, sorry for time its taking for me to
> get my head around this!!
>
> Have a look at this
>
> http://tvpressings.jasoncongerton.co.uk/transteel-products/test.
> cfm?productid=2
>
> all looks good, however no NA in boxes with no measurements. however
> this is only becauase the top two rows contains the top most amount of
> measurements, all the way to T
>
> now look at this, i have placed the letters above the measurment to
> show they are out of align with the top headers.
>
> http://tvpressings.jasoncongerton.co.uk/transteel-products/test.
> cfm?productid=29
>
> I need the ooutput to align with the top headers ABC.
>
> code below
>
> Jason
>
>
> <!---get the measurements and parts no's--->
>
> <cfquery name="get_parts" datasource="#application.dsn#">

   
> SELECT p.lamNo,
   
> p.coreNo,
   
> p.pattern,
       
> p.productPartID,
       
> p.partNo,
       
> m.measOne,
       
> m.measTwo,
       
> m.mTitle
   
> FROM productPart p INNER JOIN
       
> measurement m ON p.productPartID = m.mPartID
     
> WHERE p.partProdID = <cfqueryparam value="#productID#"
> cfsqltype="cf_sql_integer">
   

> ORDER BY  p.order, p.lamNo, p.coreNo, p.pattern, m.mtitle
> </cfquery>
>
> <!---//get the letters for the top most row--->
>
> <cfquery name="get_alpha" datasource="#application.dsn#">
> SELECT tableLetter
> FROM tableSort
> WHERE tableLetter IN (SELECT mTitle
> FROM measurement WHERE mPartID IN (SELECT productPartID
> FROM productPart WHERE partProdID =  <cfqueryparam value="#productID#"
> cfsqltype="cf_sql_integer">))
> ORDER BY tableLetter ASC
> </cfquery>
>
>
>
> <table>
> <tr>
> <td>
> <cfif get_parts.lamNo NEQ "">LAM NO:</cfif><cfif get_parts.coreNo NEQ
> "">CORE NO:</cfif>
> <cfif get_parts.pattern NEQ "">Pattern:</cfif></td>
> <td><strong>PART NO:</strong></td>
> <cfoutput query="get_alpha">
> <td style="width:70px; font-family:Verdana, Geneva, sans-serif;
> font-size:10px;
> text-align:center;"><strong>#tableLetter#</strong></td>
> </cfoutput>
> </tr>  
> <cfoutput query="get_parts" group="lamNo">
> <td>#lamNo#</td>
> <td><strong>#partNo#</strong></td>
> <cfset ctr = 0 />
> <cfoutput group="mTitle">
> <td>#mTitle#<br />#decimalFormat(measOne)#<cfif measTwo GT 0> X
> #decimalFormat(measTwo)#</cfif></td>
> <cfset ctr = ctr+1 />
> </cfoutput>
> <cfif ctr lt get_alpha.recordcount>
> <cfloop from="#ctr#" to="#get_alpha.recordcount#" index="c">
> <td> </td>
> </cfloop>
> </cfif>

 
> </tr>
> </cfoutput>
   
>
> </cfif>
   
>
   
>
> </table>
>
         
>
     
>
       
> </tr>  
>
>
   
>
 
>
   
>
   
> <cfoutput query="get_parts" group="lamNo">
   
>
           
> <td style="width:80px; font-family:Verdana, Geneva, sans-serif;
> font-size:10px;"><strong>#lamNo#</strong></td>
           
> <td style="width:80px; font-family:Verdana, Geneva, sans-serif;
> font-size:10px;"><strong>#partNo#</strong></td>
           
> <cfset ctr = 0 />
           
> <cfoutput group="mTitle">
               
> <td style="width:70px; font-family:Verdana, Geneva, sans-serif;
> font-size:10px; text-align:center;">#mTitle#<br
> />#decimalFormat(measOne)#<cfif measTwo GT 0> X
> #decimalFormat(measTwo)#</cfif></td>
               
> <cfset ctr = ctr+1 />
           
> </cfoutput>
           
> <cfif ctr lt get_alpha.recordcount>
               
> <cfloop from="#ctr#" to="#get_alpha.recordcount#" index="c">
                   
> <td> </td>
               
> </cfloop>
           
> </cfif>
       
> </tr>
   
> </cfoutput>
   
>
   
> </cfif>
   
>
   
>
> </table>


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328386
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4