Which request method?

[cantlep]

Hiya,

Which request method (if one is required) would be suitable for the following scenario? (Hopefully I can explain this OK).

I have a table with 10 fields that are not mandatory. If a user fills out 9 of them, then the 10th field should not be populated. If the 10th field is populated then the other 9 should not be. I need something (I guess in Applicationdelegate.php or the table delegate class) that says if fieldnumber10 is filled out, then, do something, else, do nothing.

In this particular case, I'd want something like:

if fieldnumber10 has a value
then
fieldnumber10 * fieldnumberx from a different row

What would be the best method for such a task?

Cheers

[shannah]

Are you talking about validation on the new record or edit record forms? Or are you developing a custom action that take 10 parameters? Or something else?

[cantlep]

Hi Steve, It's a normal edit form (no custom actions)...and what I'd like to do is I guess a kind of validation. Once the "save" button is pressed, if fields 1-9 are completed then just save the record as normal. However, if only field10 is populated then I need it to multiply whatever is in that field with another field in the same table (this will always be constant - i.e. it will always be multiplied by the same field).

Have I explained that OK?

[shannah]

An easy way would be to just put a custom validator on the 10th field. You can access all of the values of every field from the validator for any particular field.

[cantlep]

Thanks Steve, sorry but I'm not sure what you mean. I've checked the validators pages on the wiki and I can't see something that would be suitable...Can you point me in the right direction?

Thanks

[shannah]

See http://xataface.com/wiki/fieldname__validate

[cantlep]

Cheers Steve, Could you offer some more assistance?

I need something like this:

Code: Select all

function NoSupplierQuote3__validate(&$record,$value) {
if ( $value != '' ) {
select c.*, (c.ContractSupplier1NightRate * m.NightCons)
+ (c.ContractSupplier1AddRate1 * m.AddCons1)
+ (c.ContractSupplier1AddRate2 * m.AddCons2)
+ (c.ContractSupplier1AddRate3 * m.AddCons3)
+ (c.ContractSupplier1AddRate4 * m.AddCons4)
+ (c.ContractSupplier1AddRate5 * m.AddCons5)
+ (c.ContractSupplier1AddRate6 * m.AddCons6)
+ (c.ContractSupplier1AddRate7 * m.AddCons7)
+ (c.ContractSupplier1AddRate8 * m.AddCons8)
* $value)
AS ContractSupplier3TotalCostEst
FROM ContractData c
left join MPANData_ContractData mc ON c.ContractDataID = mc.ContractDataID
}
return false;
}


The problem is $value is not valid for that SQL statement...how do I grab that value and make it so?

I need to say if $value is not null then do all those sums and add $value to it.

Now I'm not even sure if this validate is required at all. I have a list of SUMS that all work fine. I just want to say if NoSupplierQuote3 is null, then do the sums as normal (which work OK), if NoSupplierQuote3 is not null then use NoSupplierQuote3 within the sum to generate the TOTAL.

Am I making sense?

[shannah]

PHP for if value is not null:

Code: Select all

if ( isset($value) ){
   ...
}


[cantlep]

Cheers Steve, but $value is only valid for the PHP and not for the SQL statement. i.e. I mean the SQL statement won't know what $value is...will it?

[shannah]

I would try to keep as much if/else control flow in PHP. When you start making SQL queries with a lot of that stuff it gets complicated.


E.g.

Code: Select all

if ( isset($value) ){
    $sql = "....";
} else {
    $sql = "...";
}


[cantlep]

Thanks again Steve, but that still won't work. I need whatever $value is to be part of the calculations. In this instance, whatever is entered into the field called NoSupplierQuote3 needs to be able to be entered as part of the SQL query...Is that possible?

Cheers

[shannah]

Why won't it work?

[cantlep]

Cheers Steve, I'd confused myself from constantly looking at this: It still doesn't work..Currently I get a permission denied error. However, I'm sure that's down to how I've written stuff in the table delegate class.

First off, I have a load of SQL that does a load of calculations (which works fine). I shall list it below - Apologies for the length.

Code: Select all

function __sql__(){
        return "select c.*, (c.ContractSupplier1DayRate*m.DayCons) AS Supplier1DayCostEst
, (c.ContractSupplier1NightRate * m.NightCons) AS Supplier1NightCostEst
, (c.ContractSupplier1AddRate1 * m.AddCons1) AS Supplier1AddRate1CostEst
, (c.ContractSupplier1AddRate2 * m.AddCons2) AS Supplier1AddRate2CostEst
, (c.ContractSupplier1AddRate3 * m.AddCons3) AS Supplier1AddRate3CostEst
, (c.ContractSupplier1AddRate4 * m.AddCons4) AS Supplier1AddRate4CostEst
, (c.ContractSupplier1AddRate5 * m.AddCons5) AS Supplier1AddRate5CostEst
, (c.ContractSupplier1AddRate6 * m.AddCons6) AS Supplier1AddRate6CostEst
, (c.ContractSupplier1AddRate7 * m.AddCons7) AS Supplier1AddRate7CostEst
, (c.ContractSupplier1AddRate8 * m.AddCons8) AS Supplier1AddRate8CostEst
, (c.ContractSupplier1DayRate * m.DayCons)
+ (c.ContractSupplier1NightRate * m.NightCons)
+ (c.ContractSupplier1AddRate1 * m.AddCons1)
+ (c.ContractSupplier1AddRate2 * m.AddCons2)
+ (c.ContractSupplier1AddRate3 * m.AddCons3)
+ (c.ContractSupplier1AddRate4 * m.AddCons4)
+ (c.ContractSupplier1AddRate5 * m.AddCons5)
+ (c.ContractSupplier1AddRate6 * m.AddCons6)
+ (c.ContractSupplier1AddRate7 * m.AddCons7)
+ (c.ContractSupplier1AddRate8 * m.AddCons8) AS Supplier1TotalCostEst
, (c.ContractSupplier2DayRate*m.DayCons) AS Supplier2DayCostEst
, (c.ContractSupplier2NightRate * m.NightCons) AS Supplier2NightCostEst
, (c.ContractSupplier2AddRate1 * m.AddCons1) AS Supplier2AddRate1CostEst
, (c.ContractSupplier2AddRate2 * m.AddCons1) AS Supplier2AddRate2CostEst
, (c.ContractSupplier2AddRate3 * m.AddCons1) AS Supplier2AddRate3CostEst
, (c.ContractSupplier2AddRate4 * m.AddCons1) AS Supplier2AddRate4CostEst
, (c.ContractSupplier2AddRate5 * m.AddCons1) AS Supplier2AddRate5CostEst
, (c.ContractSupplier2AddRate6 * m.AddCons1) AS Supplier2AddRate6CostEst
, (c.ContractSupplier2AddRate7 * m.AddCons1) AS Supplier2AddRate7CostEst
, (c.ContractSupplier2AddRate8 * m.AddCons1) AS Supplier2AddRate8CostEst
, (c.ContractSupplier2DayRate * m.DayCons)
+ ifnull((c.ContractSupplier2NightRate * m.NightCons),0)
+ (c.ContractSupplier2AddRate1 * m.AddCons1)
+ (c.ContractSupplier2AddRate2 * m.AddCons2)
+ (c.ContractSupplier2AddRate3 * m.AddCons3)
+ (c.ContractSupplier2AddRate4 * m.AddCons4)
+ (c.ContractSupplier2AddRate5 * m.AddCons5)
+ (c.ContractSupplier2AddRate6 * m.AddCons6)
+ (c.ContractSupplier2AddRate7 * m.AddCons7)
+ (c.ContractSupplier2AddRate8 * m.AddCons8) AS Supplier2TotalCostEst
, (c.ContractSupplier3DayRate*m.DayCons) AS Supplier3DayCostEst
, (c.ContractSupplier3NightRate * m.NightCons) AS Supplier3NightCostEst
, (c.ContractSupplier3AddRate1 * m.AddCons1) AS Supplier3AddRate1CostEst
, (c.ContractSupplier3AddRate2 * m.AddCons1) AS Supplier3AddRate2CostEst
, (c.ContractSupplier3AddRate3 * m.AddCons1) AS Supplier3AddRate3CostEst
, (c.ContractSupplier3AddRate4 * m.AddCons1) AS Supplier3AddRate4CostEst
, (c.ContractSupplier3AddRate5 * m.AddCons1) AS Supplier3AddRate5CostEst
, (c.ContractSupplier3AddRate6 * m.AddCons1) AS Supplier3AddRate6CostEst
, (c.ContractSupplier3AddRate7 * m.AddCons1) AS Supplier3AddRate7CostEst
, (c.ContractSupplier3AddRate8 * m.AddCons1) AS Supplier3AddRate8CostEst
, (c.ContractSupplier3DayRate * m.DayCons)
+ (c.ContractSupplier3NightRate * m.NightCons)
+ (c.ContractSupplier3AddRate1 * m.AddCons1)
+ (c.ContractSupplier3AddRate2 * m.AddCons2)
+ (c.ContractSupplier3AddRate3 * m.AddCons3)
+ (c.ContractSupplier3AddRate4 * m.AddCons4)
+ (c.ContractSupplier3AddRate5 * m.AddCons5)
+ (c.ContractSupplier3AddRate6 * m.AddCons6)
+ (c.ContractSupplier3AddRate7 * m.AddCons7)
+ (c.ContractSupplier3AddRate8 * m.AddCons8) AS Supplier3TotalCostEst
, datediff(c.ContractEndDate,c.ContractStartDate) as ContractLength, if(c.ContractStatus='Accepted' and c.ContractStartDate<=NOW() and c.ContractEndDate>=NOW(),'YES','NO') as ContractActive
FROM ContractData c
left join MPANData_ContractData mc ON c.ContractDataID = mc.ContractDataID
left join MPANData m ON mc.MPANCore = m.MPANCore";
}


Now what I need to say is if the field "NoSupplierQuote3" contains a value, then multiply that value by a stuff (some of which is used above) (shown below)

Code: Select all

function NoSupplierQuote3__validate(&$record,$value) {
if ( isset ($value) ) {
$sql = "select c.*, (c.ContractSupplier1NightRate * m.NightCons)
+ (c.ContractSupplier1AddRate1 * m.AddCons1)
+ (c.ContractSupplier1AddRate2 * m.AddCons2)
+ (c.ContractSupplier1AddRate3 * m.AddCons3)
+ (c.ContractSupplier1AddRate4 * m.AddCons4)
+ (c.ContractSupplier1AddRate5 * m.AddCons5)
+ (c.ContractSupplier1AddRate6 * m.AddCons6)
+ (c.ContractSupplier1AddRate7 * m.AddCons7)
+ (c.ContractSupplier1AddRate8 * m.AddCons8)
* $value)
AS ContractSupplier3TotalCostEst
FROM ContractData c
left join MPANData_ContractData mc ON c.ContractDataID = mc.ContractDataID";
}
}


else just run the code that I've detailed first.

Sorry if I keep asking for help on this but I can't quite get my head round it. I figure it should be really simple and perhaps I'm over complicating it

If NoSupplierQuote3 has a value then use it (as per the second code block), else ignore the field completely and run the stuff in the first code block.

Is it possible?

Thanks so much for the time you spend helping me.

[shannah]

So you're running a transformation on multiple rows all with the one query, but each row may need a different transformation depending on a particular value.

Perhaps check out the ifnull() or if() mysql functions.... Your query will start to get a little messy, but you should be able to achieve what you want.

[cantlep]

Yes, I guess I am. I must be missing something I think...as far as I can tell, logic-wise, this shouldn't be all that difficult