Welcome to the nuBuilder forums!

Please register and login to view forums and other content only available to registered users.

Subform column alias

Locked
vario
Posts: 119
Joined: Mon Dec 05, 2011 12:23 pm
nuBuilder Version: 4.5
Location: Newton Abbot, UK

Subform column alias

Unread post by vario »

I am trying to migrate to v3.0 but have hit an issue with column aliases in subform queries.
In v2 I can alias a calculation or column formatting and display the result using the alias as the field name. In v3 this fails.
Some examples:

I format TIME column to get rid of seconds:

Code: Select all

time_format(pic_time,'%H:%i') as pic_time
- in v2 I get "10:23" but in v3 I get "10:23:00" - it ignores the column formatting!

I derive a value:

Code: Select all

odo_unit*(if(odo_end=0,odo_start,odo_end)-odo_start) as km
- in v2 I get "1,479" but in v3 I get nothing!

I simply alias a column:

Code: Select all

odo_start_date as mydate
- if I give field name as odo_start_date it still displays, but field name of mydate displays nothing.

Any suggestions would be welcome - does v3 ignore column aliases in subform?
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: Subform column alias

Unread post by admin »

vario,

There are 2 ways to do it as the Subform will only use fields straight from the subform table.
1. Use a Display field with SQL like this..

Code: Select all

SELECT time_format(ite_time,'%H:%i') 
FROM item
WHERE item_id = '#SUBFORM_RECORD_ID#'
or 2. Create a View and use this in the Subform.

Steven
vario
Posts: 119
Joined: Mon Dec 05, 2011 12:23 pm
nuBuilder Version: 4.5
Location: Newton Abbot, UK

Re: Subform column alias

Unread post by vario »

Steven,

Thanks for the help. I will go with solution 2!
admin
Site Admin
Posts: 2778
Joined: Mon Jun 15, 2009 2:23 am
nuBuilder Version: 4.5

Re: Subform column alias

Unread post by admin »

.
Locked