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!
Welcome to the nuBuilder forums!
Please register and login to view forums and other content only available to registered users.
Please register and login to view forums and other content only available to registered users.
Subform data on browse screen
-
- Site Admin
- Posts: 2786
- Joined: Mon Jun 15, 2009 2:23 am
- nuBuilder Version: 4.5
- Been thanked: 3 times
Re: Subform data on browse screen
Richsurvey,
Do you mean seeing 'Balsa Wood' and 'Staple Gun' on the previous browse screen?
Steven
Do you mean seeing 'Balsa Wood' and 'Staple Gun' on the previous browse screen?
Steven
-
- Posts: 12
- Joined: Fri Sep 11, 2015 7:50 pm
Re: Subform data on browse screen
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!
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!
-
- Posts: 54
- Joined: Fri Oct 23, 2015 12:34 am
Re: Subform data on browse screen
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.
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.
-
- Posts: 12
- Joined: Fri Sep 11, 2015 7:50 pm
Re: Subform data on browse screen
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
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