nuBuilder Forum

Hi,

What is the best way to view a subform data on the browse screen?

For example in the NubuilderPro demo -> User Home -> Invoices -> number 10: I would like to see the names (Descriptions) of items in the "itemsf" subform visible in invoice browse screen.

I'm sure this is possible and hopefully someone has found a solution for this already.

Thanks!

Richsurvey,

Do you mean seeing 'Balsa Wood' and 'Staple Gun' on the previous browse screen?

Steven

Hi,

Yes, that is correct. So, on the browse screen there could be a title called "Description" and will have all the items listed for that record, so for this example it would be: "Balsa Wood", "Staple Gun", "White A4 Paper" and "Paper Cups"

Thanks!

I didn't try it, but maybe have the sql along the lines of:

select *, group_oncat(distinct description ORDER ASC SEPARATOR ', ') from invoice inner join invoice_items on invoice.invoice_id = invoice_items.invoice_pk

The item description would have to be added to the browse screen as well.
GROUP_ONCAT takes the multiple values of description and puts them side by side in the same field with the separator between.
Keep in mind my mysql isn't perfect and I haven't looked at the demo lately, so the fields and tables are named wrong.
If I'm way off, I have no problem with this post being deleted.

Thank You mobilemcclintic! That worked. I changed the SQL statement a bit for it to work, but your answer led me to the right path. For anyone interested here is what I did (in relation to the demo form)

In the Invoices form properties in the "General" tab at SQL statement section I have:

SELECT * FROM zzz_invoice
INNER JOIN zzz_item
ON zzz_invoice_id = zite_zzz_invoice_id
GROUP BY zzz_invoice_id; <---Group by statement is very important

And then on the "Browse" tab I inserted the following column:

GROUP_CONCAT(zite_description SEPARATOR ", ") <-- for both Display and Sort. Title and everything else can be anything.

The line breaks (<br>) is not working for me, but if anyone knows a solution please let me know.

Also, if the SQL statements I used above is incorrect or is not best practice please chime in (it helps everyone)

Thanks

.