Browse form with dynamically built columns

Questions related to using nuBuilder Forte.

Browse form with dynamically built columns

Unread postby absalom » Tue Apr 20, 2021 6:05 pm

While trying to make my way with nubuilder for two or three weeks now, I don't feel comfortable with building forms on some SQL requests: as long as I try to display subsets of existing columns, everything seems to work fine. But I don't really understand why I can't display more sophisticated requests including GROUP BY clause together with SUM, etc.
For instance, if I want to display the sum of all invoices by year, I could write a request like:

Code: Select all
SELECT year(date) as y, SUM(price) as total
  FROM invoices GROUP BY y ORDER BY y DESC


which works fine on phpMyAdmin, but as soon as I try to build a Browse form, with columns y and total, I get en empty table.
absalom
 
Posts: 25
Joined: Sat Apr 03, 2021 11:20 pm
nuBuilder Version: 4.5

Re: Browse form with dynamically built columns

Unread postby kev1n » Tue Apr 20, 2021 6:22 pm

Hi,

Use a SELECT FROM ... SELECT construct like this (assuming that invoices_id is your PK):

Code: Select all
SELECT y,
       total
FROM   (
SELECT invoices_id,
               Year(date) AS y,
               Sum(price) AS total
        FROM   invoices
        GROUP  BY y, invoices_id
        ORDER  BY y DESC
) T


Then use y and total in the Display columns.
kev1n
nuBuilder Team
 
Posts: 1704
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5


Return to General

Who is online

Users browsing this forum: No registered users and 11 guests