More auto updating fields [Solved]

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

More auto updating fields [Solved]

Postby cantlep » Tue May 18, 2010 2:07 pm

Hiya,

Can you help with another auto update field requirement?!

In my "ContractData" table I have various columns. One of them it titled "Contract Status" and another is "Contract Active"

What I'd like to have is the Contract Active column say "YES" or "NO" depending on the following:

If "Contract Status = ACCEPTED" and today's date is greater than or equal to "Contract Start Date" and is less then "Contract End Date" then Contract Active = YES, else Contract Active = No.

Is something like that possible..perhaps with a rendercell call in ContractData.php ?

Thanks once again

Paul
Last edited by cantlep on Thu May 20, 2010 2:37 pm, edited 1 time in total.
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: More auto updating fields

Postby shannah » Tue May 18, 2010 2:15 pm

Yes. The best way is probably to do a grafted field.
e.g.

Code: Select all
__sql__ = "select t.*, if(t.Contract_Status='Approved' and t.Contract_Start_Date<=NOW() and t.Contract_End_Date<=NOW(),'YES','NO') as Contract_Active from mytable t"
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: More auto updating fields

Postby cantlep » Wed May 19, 2010 1:55 am

Thanks Steve,

I have this in ContractData/fields.ini

Code: Select all
[ContractActive]
widget:label = "Active Contract?"
__sql__ = "select t.*, if(t.ContractStatus='Accepted' and t.ContractStartDate<=NOW() and t.ContractEndDate>=NOW(),'YES','NO' as ContractActive from ContractData t"


I know I've missed something (probably obvious). I'm also struggling to work out when __sql__ code needs to be in fields.ini or a table class in table.php I seem to have a combination of the two.

e.g. The contract length one you did for me
Code: Select all
function __sql__(){
        return "select t.*, datediff(t.ContractEndDate,t.ContractStartDate) as ContractLength from ContractData t";
}

resides in ContractData.php

Is that where I should be putting that first bit of code to auto update with YES or NO? I know when a table class is used, that column name doesn't need to be in the DB as it's created on the fly. What about when in fields.ini

Confused - :?

Can you help?

Cheers

Paul
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: More auto updating fields

Postby cantlep » Wed May 19, 2010 6:38 am

OK, read more documentation and now get the differences. I've moved all my grafted fields from fields.ini into table delegate class and removed those columns from the DB. Life is good.

Still need some assistance with the ContractActive thing though.

Cheers
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am

Re: More auto updating fields

Postby cantlep » Thu May 20, 2010 3:21 am

All sorted. I'd missed a bracket from your code (after the NO) :oops

I've now got this in ContractData.php

Code: Select all
function __sql__(){
        return "select t.*, datediff(t.ContractEndDate,t.ContractStartDate) as ContractLength, if(t.ContractStatus='Accepted' and t.ContractStartDate<=NOW() and t.ContractEndDate>=NOW(),'YES','NO') as ContractActive from ContractData t";
}


Works like a charm. Thanks for all your help, Steve

Paul
cantlep
 
Posts: 172
Joined: Fri Mar 05, 2010 2:14 am


Return to Xataface Users

Who is online

Users browsing this forum: No registered users and 23 guests

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