More auto updating fields [Solved]

Archived from the Xataface Users forum.

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

---

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"

---

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 — 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 — Thu May 20, 2010 3:21 am

All sorted. I’d missed a bracket from your code (after the NO) ops

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