Which request method?

A place for users and developers of the Xataface to discuss and receive support.

Which request method?

Postby cantlep » Mon Sep 27, 2010 3:45 am

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
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Which request method?

Postby shannah » Mon Sep 27, 2010 9:58 am

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?
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Which request method?

Postby cantlep » Mon Sep 27, 2010 2:10 pm

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?
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Which request method?

Postby shannah » Mon Sep 27, 2010 4:36 pm

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.
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Which request method?

Postby cantlep » Tue Sep 28, 2010 12:55 pm

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
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Which request method?

Postby shannah » Wed Oct 06, 2010 10:25 am

shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Which request method?

Postby cantlep » Wed Oct 20, 2010 3:32 am

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?
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Which request method?

Postby shannah » Thu Oct 21, 2010 9:42 am

PHP for if value is not null:
Code: Select all
if ( isset($value) ){
   ...
}
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Which request method?

Postby cantlep » Thu Oct 21, 2010 10:06 am

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?
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Which request method?

Postby shannah » Thu Oct 21, 2010 10:46 am

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 = "...";
}
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Which request method?

Postby cantlep » Thu Oct 21, 2010 4:12 pm

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
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Which request method?

Postby shannah » Thu Oct 21, 2010 4:29 pm

Why won't it work?
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Which request method?

Postby cantlep » Sun Oct 24, 2010 12:27 pm

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.
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: Which request method?

Postby shannah » Sun Oct 24, 2010 1:00 pm

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.
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Which request method?

Postby cantlep » Sun Oct 24, 2010 2:09 pm

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 :-(
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Next

Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 39 guests

Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved