Foreign Key Display

[Alohajoe5]

Steven,

As an update in my Channels Form, I added a Column with the Title "Abbreviation Name" and the Display "AbbrevName" (the field name from the table 'Devices'). I gave this a width of 250 and ordered last. This, with the join statement, still produces a blank browse view. Using this join statement in SQL via phpMyAdmin and via the command line does produce a table that displays the correct data. I'm confused with what I'm doing wrong with nuBuilder since this is a pretty simple join? Any help would be appreciated! Thanks.


Edit: The added column "Abbreviation Name", which I was thinking would display the data from the JOIN, does not appear on Channel's Form Object List as an input or a display.

[Janusz]

Hi,
Is it possible for you to attache your database?
I mean: make a copy of your database; and you can remove other forms not linked with the subject you are describing; replace sensitive data with some other data just for testing purpouses - just few records would be enougth.
Then it would be easier to help.

[Alohajoe5]

Hi,
Is it possible for you to attache your database?
I mean: make a copy of your database; and you can remove other forms not linked with the subject you are describing; replace sensitive data with some other data just for testing purpouses - just few records would be enougth.
Then it would be easier to help.

Janusz,

Our database lives on a closed network. It would be quite an undertaking to get it off that network and up here. I could, however, possibly recreate the applicable tables, perhaps, and fill them with some dummy records. I have to see if I have the software to do this on the network we have hooked up to the internet. I guess I'm confused because, like I said, the JOIN statement displays the column (and a few others) when run in phpMyAdmin/via command line--but inserting the JOIN statement in the Browse SQL section for nuBuilder causes all the records to disappear. Perhaps I'm missing something. I have tried creating an extra column in the Browse view where the results could populate, but I was unsure as to what the "Display" should be (i.e. Specify Table.Column or just Column name). I also tried creating a subform where I could specify a FK but this didn't seem to work.

[kev1n]

To verify the browse sql, enter this JavaScript in the developer console (F12):

Code: Select all

nuCurrentProperties().browse_sql

Then you can check if a valid sql is produced and why no records are returned.

browse_sql.png

[Alohajoe5]

Ok,

When I post the JavaScript in the developer the revealed sql query seems to be dropping the command to Select the columns from "Devices". It retains the command to select all columns from "Channels".

My sql browse code looks like this:

Code: Select all

SELECT
Devices.*,
Channels.*

FROM Devices

JOIN Channels on Channels.SrcID = Devices.ID

The query displayed when runing nuCurrentProperties().browse_sql is:

"SELECT (ALL COLUMNS FROM "Channels" NO COLUMNS FROM DEVICES--SEPARATED BY COMMAS)
FROM Devices
JOIN Channels on Channels.SrcID = Devices.ID
WHERE 1"

Again, when I manually run the query via the command line or in phpMyAdmin results are returned correctly.

[Janusz]

Hi
Please find enclosed a movie with a basic example of linking 3 tables (starting with new database)
So there is customers table, product table and options table.
For example Frank is purchasing car with metallic color.

https://drive.google.com/open?id=13uvtr ... RR6w01VClZ

[Alohajoe5]

So I resolved this issue the original way that admin suggested. I figured out the problem while trying to create a View like Janusz suggested. Here's what was happening:

Primary Key for Channels Table: ID
Primary Key for Devices Table: ID

This didn't cause a problem when executing a statement in sql, but when trying to create a view, phpMyAdmin complained of multiple columns named the same thing. Interesting even though they were on seperate tables. When I saw this, I edited the name of Channels Primary Key and...bam...nuBuilder displayed everything just like it was supposed to. I have now edited my naming convention for primary keys.

Thank you for all the help.

[Alohajoe5]

The last thing I'm trying to do now is to also display "Destination ID" (A column in Channels) as the foreign key data appearing in table Devices (displaying 'AbbrevName' in the same manner that we displayed the Source ID).

I've tried editing the code on the Channels SQL Browse Form to read:

Code: Select all

SELECT
Devices*,
Channels*
FROM
Devices
JOIN Channels on Channels.SrcID = Devices.ID AND
JOIN Channels.DestID = Devices.ID

I've added display columns in the form properties/browse section: Title: 'Source Device', Display: AbbrevName(from Devices) & Title 'Destination Device', Display: AbbrevName(Same Column from Devices)

This doesn't seem to work with both join statements. I've poked around and found some other "join two columns from one table to a single column in another table" questions that were answered on other forums and none of the other solutions seem to work. I've tried changing the JOINS to FULL OUTER JOIN/LEFT JOIN/RIGHT JOIN. LEFT displays data from Devices and nothing else, RIGHT displays data from Channels and nothing else. I have a few other things I was thinking about trying--such as aliasing the Channels table twice under different names and see if that works. I should be able to work through this aspect, but if anybody has any quick suggestions they would be appreciated.


Thank you to Admin, Janusz, and Kev1n for all the help.

[Janusz]

Hi,
Can you please put example of the tables with any data in it and what result you would like to get:

https://docs.google.com/spreadsheets/d/ ... sp=sharing

[Alohajoe5]

Janusz,

I'm trying to enter data in the table you posted but I keep getting permissions time-out errors. I'll keep trying. In the mean time I've found a SQL query that works in phpMyAdmin that excludes the "Join" and simply gets data defining where and what to get it reads (A, B, C, D replaced for actual field names):

Code: Select all

SELECT
t2.AAAA t2.BBBB t2.CCCC t2.DDDD, t2.EEEE, t2.FFFF, t2.GGGG, t2.HHHH, t2.IIII, t2.JJJJ, t2.KKKK, t2.SrcID, t2.DestID, t1.ID, t1.AbbrevName as SourceIDAbbrev, t3.AbbrevName as DestIDAbbrev

FROM
Devices as t1, Channels as t2, Devices as t3

WHERE t2.SrcID = t1.ID and t2.DestID = t3.ID

I added two columns in the Channels form to display "SourceIDAbbrev" and "DestIDAbbrev". This query works perfectly in phpMyAdmin but nuBuilder doesn't like it.

I'll keep working on getting some dummy data into the table you posted.