Foreign Key Display

Questions related to using nuBuilder Forte.

Foreign Key Display

Postby Alohajoe5 » Sat May 18, 2019 2:02 am

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?
Alohajoe5
 
Posts: 55
Joined: Tue Apr 16, 2019 9:02 pm

Re: Foreign Key Display

Postby Janusz » Sat May 18, 2019 3:10 pm

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
Janusz
 
Posts: 220
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: Foreign Key Display

Postby admin » Mon May 20, 2019 7:49 am

Alohajoe5,

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


Steven
admin
Site Admin
 
Posts: 3108
Joined: Mon Jun 15, 2009 9:53 am

Re: Foreign Key Display

Postby Alohajoe5 » Mon May 20, 2019 10:33 pm

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
 
Posts: 55
Joined: Tue Apr 16, 2019 9:02 pm

Re: Foreign Key Display

Postby Alohajoe5 » Tue May 21, 2019 6:21 am

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.
Alohajoe5
 
Posts: 55
Joined: Tue Apr 16, 2019 9:02 pm

Re: Foreign Key Display

Postby admin » Thu May 23, 2019 8:48 am

Alohajoe5,

You said

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


Do you mean Browse Form?


Steven
admin
Site Admin
 
Posts: 3108
Joined: Mon Jun 15, 2009 9:53 am

Re: Foreign Key Display

Postby Alohajoe5 » Thu May 23, 2019 9:52 pm

Steven,

Yes. Sorry I used the wrong terminology.
Alohajoe5
 
Posts: 55
Joined: Tue Apr 16, 2019 9:02 pm

Re: Foreign Key Display

Postby admin » Fri May 24, 2019 9:10 am

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
a4.JPG (63.24 KiB) Viewed 457 times



Steven
admin
Site Admin
 
Posts: 3108
Joined: Mon Jun 15, 2009 9:53 am

Re: Foreign Key Display

Postby Alohajoe5 » Wed May 29, 2019 12:07 am

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
 
Posts: 55
Joined: Tue Apr 16, 2019 9:02 pm

Re: Foreign Key Display

Postby Alohajoe5 » Wed May 29, 2019 2:49 am

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
Alohajoe5
 
Posts: 55
Joined: Tue Apr 16, 2019 9:02 pm

Next

Return to General