Foreign Key Display

[Alohajoe5]

So I've read through the users guide and I can't seem to figure out how to have a foreign key display the foreign keyed value. For example, I have a form which reads a table called "Channels", the last two columns of "Channels" are "Source" and "Destination' are foreign keys for the PID in a table called "Devices". How do I have nuBuilder recognize the relationship and display the value in "Devices" instead of simply displaying the numerical id indicated from Channels?

[Janusz]

Hi,
In my case when I need to combine data from few tables I create a view with phpmyadmin (on database level) and later from nuBuilder I am referering to that view (like for table).
It's quite convenient to prepare it in phpmyadmin because you can quickly test the code and see the results.
Normally you can place such code as well in the nubuilber SQL part of the form propertees.

https://www.w3schools.com/sql/sql_join.asp

[admin]

Alohajoe5,

On an Edit Form, you can use a Lookup Object to display data from another table.


Steven

[Alohajoe5]

So I went into "Channels" (the form I want to display data fk'd to another table/form), opened a record, and edited the form object list. I switched the existing Destination ID (the column I wanted to display data from a table called Devices) from a type input to a type lookup and under the "lookup" tab I selected the form I wanted to reference (Devices). I assigned the lookup random code and description and gave it a width of 55.

The result of this, however, is when I load the form "Channels", the column "Destination ID" still displays a number of the PID I want referenced in the table "Devices". Opening a record displays a blank lookup search area for the Destination ID object.

How do I edit this to display the referenced data automatically on the Table view of Channels and also in a record view?

Thanks

[Alohajoe5]

Let me clarify,

I have the FK displaying properly as a lookup when I load individual records, but I'm trying to get it to load in the table view.

Example: Load channels table, record #1 shows the FK PID for "Devices" under Channels table field "Destination". Click on record #1--lookup works properly and FK's to the Devices ID.

I'm trying to get the data from Devices to display in the Channels table view without "de-normalizing" the data and placing it in Channels instead of the FK'd primary id of Devices.

I hope that's not too confusing.

[admin]

Alohajoe5,

You said

I'm trying to get it to load in the table view.

Do you mean Browse Form?


Steven

[Alohajoe5]

Steven,

Yes. Sorry I used the wrong terminology.

[admin]

Alohajoe5,

You need to join the tables using the FK but not displaying the FK.

Here is a simple example.

I hope this makes sense.

a4.JPG

Steven

[Alohajoe5]

Steven,

Thanks. I'm trying to get this to work but I'm having some issues. How did you get the red dot to appear in "Sales Person" in your example? Is that where your FK is going to display or is going to display in "Sales Person ID" because that's where the join is? Do I need to add a column in nuBuilder for the display to appear in? I will post my code in a little bit.

[Alohajoe5]

Steven,

I've put the join statement in, but now I get a blank screen in the browse view. My two tables are Channels (table where the FK exits) and Devices (the referenced table). In Channels I have a column (SrcID) which is the PID of Devices. I want the table "AbbrevName" from Devices to show in the column of SrcID on Channels. My SQL on "Channels" looks like this:

Code: Select all

SELECT

Devices.*,
Channels.*

FROM

Devices
JOIN Channels ON Channels.SrcID = Devices.ID

This generates a blank screen. Note--I have tried flipping the Join statement around and that didn't solve the problem. Also, I will end up having one more column in Channels that I will FK to the same Devices table. This will be Channels.DestID which also FK's to Devices.ID