Subform data on browse screen

[Richsurvey]

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!

[admin]

Richsurvey,

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

Steven

[Richsurvey]

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!

[mobilemcclintic]

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.

[Richsurvey]

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

[admin]

.