Subform data on browse screen

Subform data on browse screen

Postby Richsurvey » Wed Apr 13, 2016 4:51 am

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
 
Posts: 12
Joined: Sat Sep 12, 2015 3:20 am

Re: Subform data on browse screen

Postby admin » Fri Apr 15, 2016 10:02 am

Richsurvey,

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

Steven
admin
Site Admin
 
Posts: 3234
Joined: Mon Jun 15, 2009 9:53 am

Re: Subform data on browse screen

Postby Richsurvey » Fri Apr 15, 2016 11:46 pm

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!
Richsurvey
 
Posts: 12
Joined: Sat Sep 12, 2015 3:20 am

Re: Subform data on browse screen

Postby mobilemcclintic » Sat Apr 16, 2016 3:00 pm

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.
mobilemcclintic
 
Posts: 54
Joined: Fri Oct 23, 2015 8:04 am

Re: Subform data on browse screen

Postby Richsurvey » Tue Apr 19, 2016 7:03 am

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
Richsurvey
 
Posts: 12
Joined: Sat Sep 12, 2015 3:20 am

Re: Subform data on browse screen

Postby admin » Thu May 19, 2016 10:34 am

.
admin
Site Admin
 
Posts: 3234
Joined: Mon Jun 15, 2009 9:53 am


Return to General