Displaying Lookup results in Browse Grid

Questions related to using nuBuilder Forte.

Displaying Lookup results in Browse Grid

Unread postby devjr » Thu Sep 23, 2021 9:35 pm

Hi, apologies for most probably a simple question - this is my first time using this software and I'm getting to grips with it.
I can use a Lookup fine to populate fields within a form. However I have struggled to find instructions or a guide to show me how to use the lookup function to populate a column that is shown on the browse table/grid, and I can't seem to figure it out within the system.
Please can you point me in the right direction? (the videos all seem to just guide you as to how to use lookups to populate forms).
Thanks,
Justin
devjr
 
Posts: 7
Joined: Thu Sep 23, 2021 9:29 pm
nuBuilder Version: 4.5

Re: Displaying Lookup results in Browse Grid

Unread postby kev1n » Thu Sep 23, 2021 9:57 pm

Hi Justin,

Can you show what you are trying to do perhaps with a screenshot? I think I don't quite understand.
kev1n
nuBuilder Team
 
Posts: 2062
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Displaying Lookup results in Browse Grid

Unread postby devjr » Thu Sep 23, 2021 10:23 pm

Sure. Thanks for the quick response.

I'm happy using the Lookup feature to populate a form, e.g.:
nuBuilder_1_2.jpg
nuBuilder_1_2.jpg (59.28 KiB) Viewed 260 times


However, I can't see how to (or a guide on how to) show the looked-up value in the browse grid. At the moment I can only show the key id in the column, not the "spring" value that is obtained in the above example. So my browse grid looks like this:
nuBuilder_1_1.jpg
nuBuilder_1_1.jpg (53.97 KiB) Viewed 260 times


Any attempts I've made to use the nuSetValue php function or creating new objects just results in failure of the browse grid (no records are displayed, error thrown in debug). So a guide showing how to progress from getting the lookup value in the form to showing it in the browse grid would be very welcome.

Thanks,
Justin
devjr
 
Posts: 7
Joined: Thu Sep 23, 2021 9:29 pm
nuBuilder Version: 4.5

Re: Displaying Lookup results in Browse Grid

Unread postby kev1n » Thu Sep 23, 2021 10:28 pm

You need to add a JOIN in your Browse SQL, see here:

viewtopic.php?f=19&t=11214&p=25066#p25068
kev1n
nuBuilder Team
 
Posts: 2062
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Displaying Lookup results in Browse Grid

Unread postby devjr » Thu Sep 23, 2021 10:38 pm

Thanks again for the quick response.

Actually that was my very first instinct to do that. However, when I added a join, it crashed the table query.

Code I'm using in the Browse tab - I am using MySQL:
Code: Select all
SELECT ls.*
FROM lesson_set ls
LEFT JOIN term ter ON ter.id = ls.term;


(note - I'm not even getting the columns over the join, just in case, but the join itself causes failure)

The error in the debug:
Code: Select all
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE `id` = '1'' at line 1
===SQL===========
UPDATE payer SET  WHERE `id` = '1';


Note that the default SQL works:
Code: Select all
SELECT ls.*
FROM lesson_set ls;
Last edited by devjr on Thu Sep 23, 2021 10:39 pm, edited 1 time in total.
devjr
 
Posts: 7
Joined: Thu Sep 23, 2021 9:29 pm
nuBuilder Version: 4.5

Re: Displaying Lookup results in Browse Grid

Unread postby kev1n » Thu Sep 23, 2021 10:39 pm

What if you remove the ; at the end?
kev1n
nuBuilder Team
 
Posts: 2062
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Displaying Lookup results in Browse Grid

Unread postby devjr » Thu Sep 23, 2021 10:41 pm

No difference. Also the no-join sql with a ";" at the end works ok (I edited my reply above to include this now)
devjr
 
Posts: 7
Joined: Thu Sep 23, 2021 9:29 pm
nuBuilder Version: 4.5

Re: Displaying Lookup results in Browse Grid

Unread postby kev1n » Thu Sep 23, 2021 10:53 pm

Could you show the table structures (e.g. from phpMyAdmin) of lesson_set and term and the columns you have chosen in the Browse?
Attachments
structure.png
structure.png (41.19 KiB) Viewed 255 times
kev1n
nuBuilder Team
 
Posts: 2062
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Displaying Lookup results in Browse Grid

Unread postby devjr » Thu Sep 23, 2021 11:02 pm

Actually I might not need to - I now have it working!
I guessed that a possible issue might be that the two tables have same-named fields.
I didn't think that this would be an issue as I was not selecting the fields in question and also using aliases in my sql. However, I've renamed the fields to be all unique and rebuilt the fast form and it now works.
So I suspect that this was the issue, although rebuilding the fast form may have helped too.
Thanks,
Justin
devjr
 
Posts: 7
Joined: Thu Sep 23, 2021 9:29 pm
nuBuilder Version: 4.5

Re: Displaying Lookup results in Browse Grid

Unread postby kev1n » Fri Sep 24, 2021 12:03 am

devjr wrote:I guessed that a possible issue might be that the two tables have same-named fields.


It was also my guess that this was the case. You can get around this by using a SELECT * FROM ( SELECT ....) construct.
kev1n
nuBuilder Team
 
Posts: 2062
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Next

Return to General

Who is online

Users browsing this forum: No registered users and 18 guests