Displaying Lookup results in Browse Grid

[devjr]

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

[kev1n]

Hi Justin,

Can you show what you are trying to do perhaps with a screenshot? I think I don't quite understand.

[devjr]

Sure. Thanks for the quick response.

I'm happy using the Lookup feature to populate a form, e.g.:

nuBuilder_1_2.jpg

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

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

[kev1n]

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

https://forums.nubuilder.cloud/viewtopic. ... 066#p25068

[devjr]

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;

[kev1n]

What if you remove the ; at the end?

[devjr]

No difference. Also the no-join sql with a ";" at the end works ok (I edited my reply above to include this now)

[kev1n]

Could you show the table structures (e.g. from phpMyAdmin) of lesson_set and term and the columns you have chosen in the Browse?

[devjr]

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

[kev1n]

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.