Reference a different table

[saultpastor]

This is for a small fire department: I have a table that has all the employee contact info and a form to edit it. I have another form (and a corresponding table) that the guys use to sign in on training night. I want them to be able to edit the 'sign_in' table by entering their 'emp_id' from the 'employee' table, in the first textbox and have it automatically fill in the name field. I know this must be a simple task. I have limited experience in both php and mysql, but I'm stumped. I must not be using nuBuilder right. I have been trying an sql query using JOIN in the default box for the 'name' field, but no joy.

Thanks

Craig

[admin]

Craig,

Have you watched any video tutorial.. https://www.youtube.com/watch?v=xLY3L1K ... AJK7Eetc4R

(Video 6 might be of help)

Steven

[saultpastor]

Steven thanks for taking the time to respond, sorry I am so new to this. I will try to find my answers. I didn't finish the video tutorials I found them to move too quickly and I am not fluent enough in php and sql, to follow at that pace.
I have watched lesson 6 repeatedly, and I get more from it each time.

Craig

[mark.reynolds]

Hi Craig!

I had the same problem. Easy to solve though - just press the pause button, or roll back a bit, to repeat stuff you miss.

I've been backwards and forwards heaps! Is great

I didn't finish the video tutorials I found them to move too quickly

[saultpastor]

Thanks Mark,

I have gone back over the videos. it a steep learning curve for me for some reason. I have used linux on the desktop for years, even owned a web design company, in the early days before php, css, etc. not a lot of experience with php, javascript, or MySQL up to this point though.

I've been able to set it up to use lookup to select a record from another table and post it to the new table. But it only posts the primary key, I can't seem to get it to post from the name column that corresponds to that primary key.

[mark.reynolds]

Hi Craig,

Yes, I'm a bit like you too. Good with the server backend - things Linux, mysql & php, but not so much on the browser side - JavaScript or css, etc.

I've also found the learning process to be steep, mainly because the nuBuilderPro documentation seems to be minimal, and a combination of old and new versions.

Anyway, I find testing sql queries with the phpMyAdmin tool to be invaluable when you're learning and developing.

In your case, for this issue, I suspect what you're trying to do is have the 'Real Name' of a person show in the browse list, rather than the ugly and pointless person index.

I've done this already, so I can tell you what has worked for me. I'll explain it in text, rather than screen shots or videos, so let's see if this makes sense to you ...

In the form general tab, you should see an SQL query, on RHS box. Usually, something simple is the default. "SELECT * FROM projects"
You might want to have the table ordered by one column, so add something like this "SELECT * FROM projects ORDER By project_id_txt" and your browse table will now be ordered by projects_id_txt content.

Now, you want to get the 'Real Name' data from another table (lets call it people), that has nothing to do with the original table (projects in my example)
So you use the magical powers of SQL to JOIN the 2 tables together, linking them by the 2 fields which have the common data between them.
In my example above, add "INNER JOIN people ON technical_contact = people_id".
The final query looking like this.
"SELECT * FROM projects INNER JOIN people ON technical_contact = people_id ORDER By project_id_txt"
In this query, technical_contact is a field of projects table, and people_id is a field of people table.

If you test this in phpMyAdmin, you will see that the result is the combination of both tables, with all fields being available to use now.

Then, there are 2 more things to do.
1. On the 'browse' tab, you need to add a column, referencing the new 'Real Name' field from the people table.
2. You need to make sure there is a matching 'object' in the objects list, which defines the field as text, with a width etc.

Took me a while to work out the need for the 'objects', so look at other objects too, and make sure you have one for your new browse field.

The other 'trick' I've figured out (or seen?) is to 'hide' a column you don't want to see in browse table, by setting it's width to 0 (zero). You can also delete it from browse objects, but depending on your sql query, this sometimes doesn't work.

Hope that helps!

Mark

Thanks Mark,

I have gone back over the videos. it a steep learning curve for me for some reason. I have used linux on the desktop for years, even owned a web design company, in the early days before php, css, etc. not a lot of experience with php, javascript, or MySQL up to this point though.

I've been able to set it up to use lookup to select a record from another table and post it to the new table. But it only posts the primary key, I can't seem to get it to post from the name column that corresponds to that primary key.

[saultpastor]

Mark,
Thanks for taking the time, I’m starting to understand. The JOIN command did the trick, I saw it in the documentation and on the video, but I wasn't getting the purpose for each of the tables, thanks for explaining.

I really like nuBuilder, it hasn't been easy, but I see the potential. The more pieces of the puzzle that I put together, the more I understand the documentation. When starting from zero, it is hard to sort it out, when even the terminology is foreign.

Thanks
Craig

[mark.reynolds]

Hi Craig!

No problem. Glad I could help somewhat.

Yes, the nuBuilderPro potential is huge. I've found the Fast Form a huge benefit, as it allows a rapid prototype, without needing to know how it achieves things. Once you need to dig deeper, things do start to get more complicated. Looking forward to Fast Reports too, as that is what I have to do next!

Also looking forward to more 'graphical' type interfaces, like the Drag Objects feature. For example, it would be great to have a Drag Columns to adjust column widths in browse forms. Or a graphical edit option for browse forms, where you tick or add which columns to add or delete, from whatever is actually available.

It will take more 'Fast' options, and more graphical elements, to make it really compete with Access. But I really can see it getting there.

I suspect Steve is the primary/only developer of the underlying code though. Until more people can get onboard with the Open Source software development, it might be a slow road.

regards
Mark

Mark,
Thanks for taking the time, I’m starting to understand. The JOIN command did the trick, I saw it in the documentation and on the video, but I wasn't getting the purpose for each of the tables, thanks for explaining.

I really like nuBuilder, it hasn't been easy, but I see the potential. The more pieces of the puzzle that I put together, the more I understand the documentation. When starting from zero, it is hard to sort it out, when even the terminology is foreign.

Thanks
Craig

[admin]

.