cfif help

[JohnShell]

On user input form there are 4 rows and 4 columns. Each row and column has the same input.
Col 1 is "Begin Date"; Col 2 is "End Date"; Col 3 is "Type Hours" and this is option list with 4 selections - 1. Comp; 2. Credit; 3. OT; and, 4. Rel Comp.
Col 4 is "Number of Hours."

User selections may or may not include Rel Comp. But, if Rel Comp selected then user must also enter two more dates in two other input boxes - Rel Comp Begin Date and Rel Comp End Date.

OK - here's an example. A user selects the following:
Row 1 - Begin and End dates; Type Hours = Credit; # Hours = 2
Row 2 - Begin and End dates; Type Hours = Comp; # Hours = 1
Row 3 - Begin and End dates; Type Hours = Rel Comp; # Hours = 4
Row 4 - Begin and End dates; Type Hours = OT; # Hours = 2.

Rel Comp Begin and End Dates are needed only for Row 3 entry. However, in my database those dates are being posted to the other three selections. I want those other three selections to show NULL for Rel Comp Begin and End Dates and for the Rel Comp entry the Rel Comp Begin and End Dates should show the user's entered dates.

Hope this makes sense. Anyway, I need an IF Statement to accomplish this and I do not know how to do this. I'm guessing that I need the IF Statement before the INSERT statement but how do I word it?
Any suggestions?

The code I have is

<cfloop from="1" to="4" index="i" step="1">
	<cfif IsDate(evaluate("Form.StartDate" & i))>
    <cfquery name="qInsertRequest" datasource="RAWH">
Insert into RAWH 
       (EmpPIN,
		Empname,
    	dateRequested,
		startdate,
       	stopdate,
       	TypeHrs,
		NoOfHours,
        projects,
        justification,
        RCbeginDate,
		RCendDate,
        RCLvSlip,
        reqDecision,
        denialReason)
VALUES (
	  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.EmpPIN#" null="#IIF(Len(Trim(Form.EmpPIN)), DE('no'), DE('yes'))#" />,

	  <cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.EmpName#" null="#IIF(Len(Trim(Form.EmpName)), DE('no'), DE('yes'))#" />,	 
       
       getdate(),
       
       <cfqueryparam cfsqltype="cf_sql_timestamp" value="#Evaluate("Form.startdate" & i)#" />,
	  
       <cfqueryparam cfsqltype="cf_sql_timestamp" value="#Evaluate("Form.stopdate" & i)#" />,

	   <cfqueryparam cfsqltype="cf_sql_varchar" value="#Evaluate("Form.TypeHrs" & i)#" />,
		
       <cfqueryparam cfsqltype="cf_sql_decimal" scale="2" value="#Evaluate("Form.NoOfHours" & i)#" />,
              
       <cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.projects#" null="#IIF(Len(Trim(Form.projects)), DE('no'), DE('yes'))#" />,     
          
        <cfqueryparam cfsqltype="cf_sql_varchar" value="#Form.justification#" null="#IIF(Len(Trim(Form.justification)), DE('no'), DE('yes'))#" />,    
       
       <cfqueryparam cfsqltype="cf_sql_timestamp" value="#Form.RCbeginDate#" null="#IIF(Len(Trim(Form.RCbeginDate)), DE('no'), DE('yes'))#" />,    
       
       <cfqueryparam cfsqltype="cf_sql_timestamp" value="#Form.RCendDate#" null="#IIF(Len(Trim(Form.RCendDate)), DE('no'), DE('yes'))#" />,       
       
       <cfqueryparam cfsqltype="cf_sql_char" value="#Form.RCLvSlip#" null="#IIF(Len(Trim(Form.RCLvSlip)), DE('no'), DE('yes'))#" />,
       
        null, 
        null)
         
      </cfquery>
	</cfif>
</cfloop>

Thanks,
John

[Gjslick]

Hey John. It's not a matter of needing an if statement, it's just a matter of what you put as the "value" of those cfqueryparam's. You currently have them set to just #form.RCbeginDate# and #form.RCendDate#. But doing this means that there is only one form element for each of those dates, regardless of how many data rows you have. It's not like the others, where you have #evaluate("form.startdate" & i)#, which means that you have a form element for the startdate of each row of the data.

Because that might not have made sense, think of it this way: what if a user selected "Rel Comp" for all 4 rows? There would need to be 4 separate RCbeginDate and RCendDate fields. Basically, in this case, they would need to be named RCbeginDate1, RCbeginDate2, RCbeginDate3, RCbeginDate4, and RCendDate1, RCendDate2, RCendDate3, RCendDate4.

So you basically need to set up your form to name them that way, and put them into your database like the others with the evaluate.

Hope that helps,
Greg

[JohnShell]

Greg,

Thanks for your input. Your reply makes sense, on the surface. However, that is not the business logic for this form. The "Rel Comp" type hours requires that a requester indicate when they intend on using that category of additional work hours. That is why there is an RCbeginDate and RCendDate. One input for both RCbegin/RCendDates is all that is required. Hence, only one input for each of those. Therefore, at this time, I do not need multiple RCbeginDate and RCendDate.
What I do need is to not annotate the database with RCbeginDate and RCendDate when a user requests "Rel Comp" and another (up to 3 more) types of additional hours.
The database table has the following columns:
ID | EmpPIN | Empname | dateRequested | startdate | stopdate | TypeHrs | NoOfHours | projects | justification | RCbeginDate | RCendDate | and several other columns.

When a user selects from TypeHrs either "Comp", "Credit Hrs", or "Overtime" then there is no need to complete RCbeginDate or RCendDate and those fields in the table are null. If a user selects only "Rel Comp" then he is required to complete the RCbeginDate and RCendDate inputs and the fields in the table reflect the selected dates rather than nulls.
However, if a user selects any one of the other three values ("Comp", "Credit Hrs", or "Overtime") AND "Rel Comp" and the form is submitted then currently the database table shows data for the RCbeginDate and RCendDate fields for the "Rel Comp" category and the other categories. I need to have the other categories' RCbeginDate and RCendDate fields remain nulls. That is why I need an IF statement. Hope this makes sense.

Thanks again,
John

BTW - where in NJ? My wife is originally from Lawrenceville - just north of Trenton.