0

When viewing an individual record, I'm using a calculated field from a sql query to display as a read only field in my form. However I'm unable to get it to display (no error, just blank). Curious, do I need a custom form to display this?

/forms/myform.xml

        <field
            name="current_age"
            type="text"
            readonly="true"
            label="DOB_AGE"
            description="DOB_AGE_DESC"
            size="40"
            default="0"
            />

fields/myfield.php

    protected function getOptions()
{
    $db    = JFactory::getDBO();
    $query = $db->getQuery(true);
    $query->select('d.*,(YEAR(CURDATE())-YEAR(d.dancer_birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(d.dancer_birthdate,5)) AS d.current_age,c.title as category');
    $query->from('#__registration_dancers as d');
    $query->leftJoin('#__categories as c on d.catid=c.id');
    // Retrieve only published items
    $query->where('d.published = 1');
    $db->setQuery((string) $query);
    $messages = $db->loadObjectList();
    $options  = array();

    if ($messages)
    {
        foreach ($messages as $message)
        {
            $options[] = JHtml::_('select.option', $message->dancer_id, $message->dancer_name .
                                  ($message->catid ? ' (' . $message->category . ')' : ''), $message->dancer_birthdate,
                                $message->studio_id, $message->dateadded, $message->datemodified, 
                                  $message->current_age, $message->published);
        }
    }
0

Yes, as far as I know, SQL can only be used with SQL form field and this generates dropdown list. So to just show some value from DB, you have to create custom field.

Old answer

Your need to add the field path to myform.xml parent <fieldset> to load your custom fields. This is only needed if you haven't added your custom fieldpath in PHP.

<fieldset addfieldpath="/administrator/components/<component name>/models/fields">

To add field path with PHP, so you don't need to add it in every form XML, use (for example in your component main file):

JForm::addFieldPath(JPATH_COMPONENT . '/models/fields');

And in myform.xml set type as your new form field.

    <field
        name="current_age"
        type="myfield" <----- custom field type
        readonly="true"
        label="DOB_AGE"
        description="DOB_AGE_DESC"
        size="40"
        default="0"
        />

And your field class should be named JFormFieldmyfield. Just to make sure you have correct class name.

Further reading Joomla! Docs: Creating a custom form field type.

  • Thanks for the reply but this isn't what I was asking. I know how to create custom fields but I'm trying to avoid doing that in this case because all I'm doing is displaying a database field that I calculated in the query itself. What I'm trying to answer is whether a custom field is required when referencing database field aliases: you can see in my query, "current_age" doesn't exist as an actual field - it was calculated in the query: (YEAR(CURDATE())-YEAR(d.dancer_birthdate)) - (RIGHT(CURDATE(),5)<RIGHT(d.dancer_birthdate,5)) AS d.current_age, – Mhaddy Jan 15 '16 at 11:28
  • I edited my answer. – Rene Korss Jan 15 '16 at 11:34
  • You are persistent but your edited answer still does not address my question. I am no trying to use the sql form field (I recognize this only created a drop down field)- the sql I posted was from my fields/myform.phpfile, which I believe, retrieves the content to display in the form so I'm not sure why an alias here wouldn't work... Third time's the charm? – Mhaddy Jan 15 '16 at 22:06
  • Okay. I think I get it now :) Did you set myfield as your field type? In question it is text, which is wrong. – Rene Korss Jan 16 '16 at 8:22
  • If I'm trying to reference a database field alias - not a custom field, what should it be? When I'm referencing a standard database field, it can be just "text". – Mhaddy Jan 16 '16 at 13:13
0

From all of my research and testing, I don't think this is possible. I ended up solving my quandary with a custom field instead of an alias.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.