Hello everyone,
I am working on a database for student results using NuBuilder, however, I am having a challenge with the lookup fields, and having searched the forum for a solution and not finding the solution I need, I decided to post this.
There are 3 lookup fields in my subform: one to look up ExamCode, another to look up Student MatricNo, and the last to look up the CourseCode. All are displaying correctly on the form. However, after saving, they only save one character to the database table, not the complete ExamCode, MatricNo and CourseCode. I've attached a screenshot of the form and the tables' structure and data.
I must have been missing something. It's a long time ago I'd written codes, nevertheless, with NuBuilder, I hope to create user-friendly forms for a start, before delving deeper. I will appreciate your assistance here to look into this. Screenshot attached.
Welcome to the nuBuilder Forums!
Register and log in to access exclusive forums and content available only to registered users.
Register and log in to access exclusive forums and content available only to registered users.
Lookup field not writing correctly to database
-
felixdiamond
- Posts: 1
- Joined: Wed May 24, 2023 8:07 pm
Lookup field not writing correctly to database
You do not have the required permissions to view the files attached to this post.
-
kev1n
- nuBuilder Team
- Posts: 4292
- Joined: Sun Oct 14, 2018 6:43 pm
- Has thanked: 71 times
- Been thanked: 444 times
- Contact:
Re: Lookup field not writing correctly to database
Hi,
The Lookup object works by storing the primary key of the foreign table in the database. This approach follows the principles of data normalisation, which is a fundamental concept in relational databases.
Normalisation involves organising data into multiple tables and establishing relationships between them using primary and foreign keys. By storing the primary key of the foreign table in the database, the lookup object maintains referential integrity and ensures that data remains consistent and accurate.
The primary key acts as the link between the main table and the foreign table. This method allows efficient retrieval and manipulation of data by establishing relationships between related entities. By following the principles of normalisation, nuBuilder provides a structured and efficient way of storing and managing data within a relational database.
When selecting data from the 'main' table, a join operation can be used to retrieve additional information such as descriptions or other relevant data from the associated foreign table.
If there is a requirement to store not only the primary key but also the value associated with the foreign table in the main table, it is possible to create an additional column for this purpose. It should be noted, however, that this approach is not typically recommended in normalised database design.
To do so, you can create an additional column in the main table to store the desired value. This column would act as a denormalised representation of the data from the foreign table. However, it is important to understand that this denormalisation introduces redundancy into the database schema, which can lead to data integrity issues and increased storage requirements.
To populate this additional column with the relevant value from the foreign table, you can create an "After Save" PHP script.. This script would execute after the data is saved in the main table and would be responsible for fetching the corresponding value from the foreign table based on the primary key and storing it in the additional column.
The Lookup object works by storing the primary key of the foreign table in the database. This approach follows the principles of data normalisation, which is a fundamental concept in relational databases.
Normalisation involves organising data into multiple tables and establishing relationships between them using primary and foreign keys. By storing the primary key of the foreign table in the database, the lookup object maintains referential integrity and ensures that data remains consistent and accurate.
The primary key acts as the link between the main table and the foreign table. This method allows efficient retrieval and manipulation of data by establishing relationships between related entities. By following the principles of normalisation, nuBuilder provides a structured and efficient way of storing and managing data within a relational database.
When selecting data from the 'main' table, a join operation can be used to retrieve additional information such as descriptions or other relevant data from the associated foreign table.
If there is a requirement to store not only the primary key but also the value associated with the foreign table in the main table, it is possible to create an additional column for this purpose. It should be noted, however, that this approach is not typically recommended in normalised database design.
To do so, you can create an additional column in the main table to store the desired value. This column would act as a denormalised representation of the data from the foreign table. However, it is important to understand that this denormalisation introduces redundancy into the database schema, which can lead to data integrity issues and increased storage requirements.
To populate this additional column with the relevant value from the foreign table, you can create an "After Save" PHP script.. This script would execute after the data is saved in the main table and would be responsible for fetching the corresponding value from the foreign table based on the primary key and storing it in the additional column.