Auto-Populating with Data from Another Table

Discuss the database features
Post Reply
BrotherCRO
Posts: 2
Joined: Fri Aug 30, 2013 1:48 am

Auto-Populating with Data from Another Table

Post by BrotherCRO »

Greetings,

Here's the situation: I'm trying to create an employee sign-in form in OO Base that records the Employee ID (primary key for the table), last name, first name, department/position and an automatic timestamp. The thing is, I only want to have to input the employee ID with entry, and have the rest of the information auto-populate based on the data from a table containing the employees' information (which also uses the employee ID as its primary key). How do I configure the sign-in table to extract information from the employee data table based on the employee ID being entered? I'm still learning the basics of OO Base, so any input (the more idiot-proof, the better) is appreciated.
OpenOffice v. 4.0 on Win7
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Auto-Populating with Data from Another Table

Post by Arineckaig »

How do I configure the sign-in table to extract information from the employee data table based on the employee ID being entered?
Welcome to the forum.

If I am correct in assuming you wish a table to record each of several times that an employee may sign in, you will in effect need a one-to-many relationship between the employee table and a sign-in table. The latter table (the many side of the relationship) should have its own (possibly token) primary key field but also it should have a foreign key field that holds the relevant "Employee ID" value for referring or linking to the primary key field in the employee table (the one side of the relationship). Of course the sign-in table should also have a field to hold the value of the time-stamp each time the employee signs in: that field should not be in the employee table unless the database is to be designed to record the one and only time that an employee signs in.

AOO Base provides its form/sub-form facility for effective handling of such relationships. In the form document the main data form should be based on the one side or employee table and the sub-form on the many side or sign-in table. In the sub-form's list of properties under the data tab the linking fields for both forms should be their respective "Employee ID" fields. When correctly set-up selection of any particular employee in the main form will display whatever fields are required from the employee table, but it will also filter the records in the sub-form to show only those from the sign-in table that relate to that particular employee. The records in the sub-form can be edited, but more importantly the sub-form permits entry of new records with the relevant Employee ID field value pre-entered by default.

Please come back with questions if this explanation merely confuses or if you would like a simple demo file to see how it works.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Auto-Populating with Data from Another Table

Post by DACM »

 Edit: Just noticed Arineckaig's reply but I had already compiled this reply so it may or may not reflect that excellent advice... 
Simply use a List Box to copy the Employee_ID to a separate SIGN_IN table. You can always use Queries or Forms to reassemble the table-data for viewing, filtering or analysis purposes.

The SIGN_IN table will also need a TIMESTAMP field with the default set to CURRENT_TIMESTAMP using SQL:
  • Code: Select all

    ALTER TABLE "SIGN_IN" ALTER COLUMN "DATE_TIME" SET DEFAULT CURRENT_TIMESTAMP
    
    
The user selects an employee by name, and then presses a button to save the Employee_ID to the SIGN_IN table with automatic TIMESTAMP. You can optionally add a SubForm to display the Employee information upon saving the SIGN_IN record.

See Students5 - attendance tracking.odb for more detailed information.

Here's a quick demo:
Attachments
Sign_In.odb
Demo List Box record creation with TIMESTAMP
(39.58 KiB) Downloaded 700 times
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
fuzzyluzzi
Posts: 22
Joined: Wed Feb 26, 2014 12:37 pm

Re: Auto-Populating with Data from Another Table

Post by fuzzyluzzi »

Is it possible to make this have cascading list boxes. First to check for Dept, then Employees in that dept before clicking Sign In?
OpenOffice 4.0.1 on Windows 7
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Auto-Populating with Data from Another Table

Post by Arineckaig »

First to check for Dept, then Employees in that dept before clicking Sign In?
There is probably a one-to-many relationship between Dept and Employees. Thus it can again be handled in Base by using a data form for the one side (Dept) and a linked sub-form for the many side (Employees). Base will permit the inclusion of several generations of sub-forms in a single form document. Thus the sub-sub-form (sourced from the sign-in table) can still reflect whichever Dept and Employees records have been selected in the grandparent and parent data forms.

Cascading list boxes can equally be used if the perceived advantage justifies their more complex set-up without resort to macro programming. See this Example for a full and good description:
http://user.services.openoffice.org/en/ ... 00&t=42845
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Auto-Populating with Data from Another Table

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
tveinot
Posts: 3
Joined: Thu Mar 09, 2017 8:25 pm

Re: Auto-Populating with Data from Another Table

Post by tveinot »

Is there a way to make the related table info come up in the form before saving the record? I have 2 tables valve_exercise and valve_master_list. The form is for recording when a valve is exercised. So each exercise is related to the valve via the valve_no field. But when we go to a valve and type in it's number we would like to see the fields from the valve_master_list because that table stores the valve operation information.
OpenOffice 3.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Auto-Populating with Data from Another Table

Post by Villeroy »

You can add as many sub, subsub, subsubsub...forms as needed in order to display and/or edit all interrelated information from all forms.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
tveinot
Posts: 3
Joined: Thu Mar 09, 2017 8:25 pm

Re: Auto-Populating with Data from Another Table

Post by tveinot »

I understand that but until I "save" the entry it doesn't show me the related data. I would like to be able to enter the valve number then tab to the next field, on the "tab" have the information from the master_valve_list populate into the subform so that I know which way to turn the valve, if it is open or closed, how many turns, and when it was last operated, etc... Do I have to save the entry with just the valve number first or is there away to show the related data on insert?
OpenOffice 3.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Auto-Populating with Data from Another Table

Post by Villeroy »

Add a small main form (in the forms navigator directly under "Forms") with property "add new record only" where you can enter a new valve without moving away from the the other form's record. Then go back to that form, refresh the list box of valves and pick the new one.

download/file.php?id=2879 has a form with movies and genres where you can add a missing genre before assigning it to movies.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
tveinot
Posts: 3
Joined: Thu Mar 09, 2017 8:25 pm

Re: Auto-Populating with Data from Another Table

Post by tveinot »

I will give it a try.
Thanks
OpenOffice 3.1 on Windows Vista
Thibault87
Posts: 2
Joined: Wed May 11, 2022 4:16 am

Re: Auto-Populating with Data from Another Table

Post by Thibault87 »

Hello everyone,
I'm sorry to start this topic again, but I didn't find it for the latest versions of openoffice.
Is it possible on the latest openoffice version (4.1.11) ?
Thank you
OpenOffice 4.1.11 on Windows 11 :D
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Auto-Populating with Data from Another Table

Post by UnklDonald418 »

OO Base has not materially changed since 2008, so Yes what was done in the post from 2013 should still work in 2022.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Thibault87
Posts: 2
Joined: Wed May 11, 2022 4:16 am

Re: Auto-Populating with Data from Another Table

Post by Thibault87 »

Thank you very much, I gonna try this afternoon
OpenOffice 4.1.11 on Windows 11 :D
Post Reply