Displaying Value instead of ID

[Fundi]

Hi
I have a dropdown on my form and save a foreign key in my table (such as in the customer table I save the job_id). Now on the browse/search screen I like to display not the job_id but the what the job_id actually stands for. Is there a way to do this? Or is one limited with the browse fields to the underlying table?

Fundi

[admin]

If you look at the sampledebtors db, on the invoice Form you will find this sql statement for the browse.

(a query joining more than 2 tables so values can be used from each)

Code: Select all

SELECT * FROM transaction
INNER JOIN customer 
ON transaction.tra_customer_id = customer.customer_id 
LEFT JOIN zzsys_list 
ON transaction.tra_posted = zzsys_list.sli_option  
WHERE (sli_name = 'answer') 
AND (tra_type = 'I' OR tra_type = 'C')
ORDER BY tra_number

(the circled info comes from the transaction table, the customer name from the customer table)

browse1.PNG (27.02 KiB) Viewed 11014 times

Steven

[Fundi]

Hi Steven

I got the point. Your example even goes to the system lists. My list are actual tables so it is a bit easier. (I know one should not use infinite lists for look ups. but in my case the list range from 5 - 100 records and the user must be able to add an entry if one is needed.

So he is my code

Code: Select all

SELECT job, date, status, firstname, sur_comp_name
FROM Tbl_Job
INNER JOIN Tbl_Customer
ON Tbl_Job.customer_id = Tbl_Customer.id
INNER JOIN Tbl_Status
On Tbl_Job.status_id = Tbl_Status.id
order by job, date

Thanks for answering. I am getting more and more into this nuBuilder
Fundi

[admin]

(I know one should not use infinite lists for look ups. but in my case the list range from 5 - 100 records and the user must be able to add an entry if one is needed.
Fundi

You Should use lookups for infinite lists.

(if that is what you meant)

Steven

[Fundi]

indeed. That is what I wanted to say. but see my other post for why I did not use the Look-up feature.
http://forums.nubuilder.cloud/viewtopic.php?f=4&t=5489

Fundi

[colin-t]

I had the same question as the original post but the solution (which I'd figured out myself before checking the boards) doesn't seem to work for me -- I get an error when the site tries to load the browse form. I've put this:

Code: Select all

SELECT * FROM subclients
INNER JOIN clients
ON subclients.client_id = clients.client_id

in the "SQL" field of the form's "All" tab (I've noticed that "INNER JOIN" isn't highlighting the way all the other SQL words do). Has something changed since this thread, or am I just overlooking something? Is the "ORDER BY" I see in both your codes necessary? (Sorry, I'm new to databases and working on something that really shouldn't be my first project). I've double- and triple-checked my table & field names and they seem to be correct.

[massiws]

colin-t,
'INNER JOIN' and 'LEFT JOIN' words are never highlighted, you are right, but they work fine.
'ORDER BY' clause isn.t necessary: can be used if you need to sort the results by a column (or more).

In order to have more information about your error, try to copy the SQL sentence in phpmyadmin: do you get a message from MySQL?
Are the data you get in phpmyadmin what you need to build the form in nuBuilder?

[colin-t]

massiws,

Thanks for the quick response!

I ran it through phpmyadmin and got no error message. The data it returned looked good at first until I realized that I should be using a left join (and allowing null in the foreign key) because one of the subclients is a "Default" that isn't linked to a specific client. I made those changes and it looks good in phpmyadmin but still produces an error in NuBuilder.

It occured to me that NuBuilder might be having a problem because I'm not using table prefixes on my field names, but changing that didn't make a difference either.

[massiws]

colin-t,
if the SQL is ok and you get an error before browse screen appear, then:

• control all values in the form > browse tab: maybe you have a wrong name in Display and/or Sort fields;
• if you have a field name that could be referred to two different tables, then then you must precede the field name with the table name;
• if you inserted some code in Custom code > Before browse, maybe there is an error in your code.

Also, I suggest reading the nuBuilder naming conventions in wiki pages: having two fields with the same name (client_id) on two different tables (subclients and clients) could break some queries.

Anyway, this seems not related with the object of this tread: if you need more help, please open a new tread with a more appropriate title.

Hope this helps,
Max

[colin-t]

Thanks, Max! I renamed my fields in accordance with the naming conventions and that stopped the error -- but the browse form still showed the ID instead of the value of the foreign key. After a little experimenting I realized that on the browse tab in the Display field I needed to replace the name of the foreign key field with the name of the field I wanted from the other table (in my case, replacing "sc_client_id" with "cl_name").

I had read the naming conventions before but wasn't following them because of this post by Steven that I misread as saying they are "just a suggestion". I see now that he wasn't referring to the general need for prefixes (I probably misread because I didn't want to use prefixes )

Thank you again for your help!