Calculated Fields

[ADobkin]

I have a need for several relatively simple "expressions" in my project, mainly to alter how certain fields are displayed. For example, combining separate fields for First Name, Middle Name, Last Name, Suffix, etc. into a single Full Name field. For the most part, these expressions operate on data in a single table, so I should not need to use join statements for this purpose.

I found the following function, but I'm not sure how to use it:

Code: Select all

function field__%fieldname%(&$record)

If I define several of these functions per table, how do I get them to show up in the application? Specifically, I would like them in browse and list views, but potentially also in edit if it is possible for them to be dynamically displayed.

Also, what syntax should be used for conditional-type expressions, operators such as sub-strings, etc. Should this be specified with SQL or PHP code?

Thanks,
Alan

[shannah]

The

Code: Select all

field__xxx()

syntax indeed allows you to create calculated fields, however these fields will only be available via that API. They won't automatically be shown in list or browse view.

The best solution for this may look like a hack, but it works well.

1. Define the __sql__ directive in your fields.ini file and append some dummy columns on the end (no need to perform a join.. you can reuse existing columns in the same table).

e.g.

Code: Select all

__sql__ = "select *, id as fullName from people"


So we have appended a dummy column named fullName to our results.

We can now override the display of this column in our delegate class with

Code: Select all

function fillName__display(&$record){
    return $record->val('firstName').' '.$record->val('lastName');
}


We can also work with this column in our fields.ini file:

Code: Select all

[fullName]
    widget:label="Full Name"
    order=-1


Note that in this case we could have done this a little more naturally directly in the __sql__ directive:

Code: Select all

__sql__ = "select *, concat(firstName,' ',lastName) as fullName from people"


But you get the idea.

-Steve

[ADobkin]

Thanks! Yes, that does seem like a hack, and I never would've figured that one out on my own. It did work to display the field in the list view, but it still isn't showing up in the browse/details view. Is there something I may be missing here?

From your post, it seems like this method is instead of the field__fullName() function, so I removed that definition from my delegate class, although it didn't seem to make a difference when I left it in also.

[ADobkin]

Okay, I discovered this is a known bug with a patch to RecordView.php:

calculated field are missing in the detail view
http://www.xataface.com/forum/viewtopic.php?t=4082

[ADobkin]

I am still having a few problems with the formatting of these calculated fields:

1) What is the proper way to create a multi-line value? For example, I tried this:

Code: Select all

        function fullAddress__display(&$record){
                return $record->strval('address1')."\n".$record->strval('address2');
        }

However, the "\n" was converted to a space in the browse and list views. So, I tried '
' instead. That works for the browse view, but it is ignored in the list view, so the two lines are concatenated without even a space.

2) If I hide the original values used in the calculation (in other words, 'address1' and 'address2' in the example above, since they are redundant with 'fullAddress'), then the calculation field is blank in the list view. It seems that the source values must be displayed to generate the calculated field. This isn't a problem with the browse view however.

3) Is there any way to set the display order of the calculation fields in the list view, or will they always appear at the end?

Thanks,
Alan