Which request method?

Archived from the Xataface Users forum.

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

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?

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?

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.

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

shannah — Wed Oct 06, 2010 10:25 am

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

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?

shannah — Thu Oct 21, 2010 9:42 am

PHP for if value is not null:

Code: Select all
if ( isset($value) ){    ... }

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?

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 = "..."; }

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

shannah — Thu Oct 21, 2010 4:29 pm

Why won’t it work?

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.ContractSupplier1DayRatem.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 — 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.

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 — 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

shannah — Thu Oct 21, 2010 4:29 pm

Why won’t it work?

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.ContractSupplier1DayRatem.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 — 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.

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

jhenry — Sat Nov 13, 2010 8:38 am

Paul,

I use an if/then statement with an isset nested:

Code: Select all
`$nsq3 = $record->val(‘NoSupplierQuote3’);

if (isset($nsq3))  //Check to see if NoSupplierQuote3 is NULL
                 {

                 perform calculations * NoSupplierQuote3
                 }

else
                {

                perform calculations without NoSupplierQuote3

                }`

Hope this is what you are looking for.

Jason