SwitchBoard with Filter and Button

Discuss the database features

SwitchBoard with Filter and Button

Postby DreamerArgentino » Tue Jun 12, 2018 4:21 am

Hello everyone!

I'm trying to make a kind of Switchboard, but with this feature:

- The "Initial-Switchboard" Form contains a ListBox and a Button:
The idea is that: From the ListBox, you select a Department for only one time, which can be "AAA", "BBB", etc. (Next time, you can not choose another)
Once the Department has been chosen, by pressing the button, the chosen Department is saved in the "ID-Depts" field of the "TBL-SelectedTasks" table.

... and that, the next time the "Initial-Switchboard" form is opened, the last option chosen is already preselected.

See the attachment for more clarity

I hope you can help me.
Thank you!
Dreamer.-
Attachments
TASKS-000 + SwitchBoard.odb
(28.85 KiB) Downloaded 7 times
OpenOffice 4.1.5 On Windows 10
DreamerArgentino
 
Posts: 11
Joined: Sun May 20, 2018 12:02 am

Re: SwitchBoard with Filter and Button

Postby UnklDonald418 » Tue Jun 12, 2018 6:39 pm

Once the Department has been chosen, by pressing the button, the chosen Department is saved in the "ID-Depts" field of the "TBL-SelectedTasks" table.

Since "TBL-SelectedTasks" has multiple rows which one(s) do you want updated with the chosen "ID-Depts" value?
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the subject line
Apache OpenOffice 4.1.5 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 796
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SwitchBoard with Filter and Button

Postby DreamerArgentino » Wed Jun 13, 2018 2:53 pm

UnklDonald418 wrote:
Once the Department has been chosen, by pressing the button, the chosen Department is saved in the "ID-Depts" field of the "TBL-SelectedTasks" table.

Since "TBL-SelectedTasks" has multiple rows which one(s) do you want updated with the chosen "ID-Depts" value?


Hi, thanks for reply!

Yes it's correct. They should all be updated with that same data ... I really do not know how to solve it. The idea is that once the user selects the department, he can only load in the selection made.

Regards!
DreamerArg.-*
OpenOffice 4.1.5 On Windows 10
DreamerArgentino
 
Posts: 11
Joined: Sun May 20, 2018 12:02 am

Re: SwitchBoard with Filter and Button

Postby UnklDonald418 » Wed Jun 13, 2018 7:57 pm

They should all be updated with that same data

If all the Tasks for all the Students in TBL-SelectedTasks have the same "ID-Depts" value then there is no point in having that field in TBL-SelectedTasks.
It would be better to create a new table to hold the selected value. A table that always has a single row is often referred to as a filter table. You could create one by executing the following in Tools>SQL
Code: Select all   Expand viewCollapse view
CREATE TABLE "SelectedDept" (
"ID" BOOLEAN NOT NULL PRIMARY KEY,
"ID-Dept" Integer
);

After successfully executing the command, select View>Refresh Tables to update your database.

Of course you will need to adjust your forms to make use of the table.
Initial-Switchboard would need to have the data source changed to the SelectedDept table. The Combo Box needs be changed to a ListBox. In order to save data selected on a form there must be a control to hold the primary key. The control can have its Visible property set to No, if you don't want to display it. You would also need either a push button control with the Action property set to Save record, or maybe modify your macro to save the record before opening FRM-Students.

To display the selection on FRM-Students one option would be to have another form on the same level as MainForm that would use the SelectedDept table as its data source. Again a ListBox would be needed to display the stored value. To make the List Box read only set the Allow additions, Allow modifications and Allow deletions properties to No on the Form Properties dialog.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the subject line
Apache OpenOffice 4.1.5 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 796
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SwitchBoard with Filter and Button

Postby DreamerArgentino » Fri Jun 15, 2018 3:30 pm

UnklDonald418 wrote:
They should all be updated with that same data

If all the Tasks for all the Students in TBL-SelectedTasks have the same "ID-Depts" value then there is no point in having that field in TBL-SelectedTasks.
It would be better to create a new table to hold the selected value. A table that always has a single row is often referred to as a filter table. You could create one by executing the following in Tools>SQL
Code: Select all   Expand viewCollapse view
CREATE TABLE "SelectedDept" (
"ID" BOOLEAN NOT NULL PRIMARY KEY,
"ID-Dept" Integer
);

After successfully executing the command, select View>Refresh Tables to update your database.

Of course you will need to adjust your forms to make use of the table.
Initial-Switchboard would need to have the data source changed to the SelectedDept table. The Combo Box needs be changed to a ListBox. In order to save data selected on a form there must be a control to hold the primary key. The control can have its Visible property set to No, if you don't want to display it. You would also need either a push button control with the Action property set to Save record, or maybe modify your macro to save the record before opening FRM-Students.

To display the selection on FRM-Students one option would be to have another form on the same level as MainForm that would use the SelectedDept table as its data source. Again a ListBox would be needed to display the stored value. To make the List Box read only set the Allow additions, Allow modifications and Allow deletions properties to No on the Form Properties dialog.



Hello, thanks for answering!

I've tried, but it does not work ... obviously the lack of knowledge is playing against me ... :crazy:
OpenOffice 4.1.5 On Windows 10
DreamerArgentino
 
Posts: 11
Joined: Sun May 20, 2018 12:02 am

Re: SwitchBoard with Filter and Button

Postby UnklDonald418 » Fri Jun 15, 2018 4:55 pm

it does not work

What does not work?

Try this.
TASKS-000 + SwitchBoard-Modified.odb
(45.25 KiB) Downloaded 4 times

Use Initial-Switchboard01 it opens FRM-Students01.
I modified the macro to automatically save the selection before opening FRM-Students01
Look it over and then you if there is anything you don't understand you can ask questions.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the subject line
Apache OpenOffice 4.1.5 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 796
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SwitchBoard with Filter and Button

Postby DreamerArgentino » Fri Jun 15, 2018 5:41 pm

"What does not work?

Obviously what does not work is my brain haha!
There it works perfect and it's almost what I was looking for!
Thank you very much!
Dreamer.-
OpenOffice 4.1.5 On Windows 10
DreamerArgentino
 
Posts: 11
Joined: Sun May 20, 2018 12:02 am

Re: SwitchBoard with Filter and Button

Postby UnklDonald418 » Fri Jun 15, 2018 8:26 pm

it's almost what I was looking for!

I may be wrong but it would seem to me that what you need is the ability for each student to select a department. In that case "ID-Dept" would be in the Students table.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the subject line
Apache OpenOffice 4.1.5 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 796
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SwitchBoard with Filter and Button

Postby Villeroy » Fri Jun 15, 2018 10:32 pm

or may be each student belongs to more than one department?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SwitchBoard with Filter and Button

Postby DreamerArgentino » Mon Jun 18, 2018 2:02 pm

UnklDonald418 wrote:
it's almost what I was looking for!

I may be wrong but it would seem to me that what you need is the ability for each student to select a department. In that case "ID-Dept" would be in the Students table.



Hello!!

No, it's exactly what I was looking for, but another thing I would need, is that once the Department's selection is made, the ListBox will be blocked in that selection for the next time. preventing the user from changing departments. That is, it can be used only once.

I appreciate your help!
Thank you!!
OpenOffice 4.1.5 On Windows 10
DreamerArgentino
 
Posts: 11
Joined: Sun May 20, 2018 12:02 am

Re: SwitchBoard with Filter and Button

Postby Villeroy » Mon Jun 18, 2018 2:13 pm

Create one database copy per department, each copy with its own department entry and no edit form. Of course, you can edit this in the table.

For a better solution you need a stand-alone database (not the embedded type) with log-in credentials and permissions for each department and for database admins.

For a single database to be accessed by all users/departments simultaniously you need to set up a database server, a program that serves its database(s) to multiple computers on a local network
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25365
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SwitchBoard with Filter and Button

Postby UnklDonald418 » Mon Jun 18, 2018 8:49 pm

You could try this
First I added a new macro
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

Sub ToggleReadOnlyProperty (oEvent as object)

Dim oControl

oControl = oEvent.Source.getByName("Cuadro de texto 1")
If oControl.CurrentValue = "" then
     oControl.setPropertyValue("ReadOnly", FALSE)
Else
     oControl.setPropertyValue("ReadOnly", TRUE)
End If

End Sub


Next edit TBL-Students and add an Integer field "ID-Dept". You can use the GUI or I did it by going to Tools>SQL and executing
Code: Select all   Expand viewCollapse view
ALTER TABLE "TBL-Students" ADD COLUMN "ID-Dept" INTEGER;

Create a copy of FRM-Students02
Open the new form in Edit/Design mode.
Using the Form Navigator select both the listbox control "Cuadro de texto 1" and its label "Choosen Departament"
Drag them onto MainForm
Select "FormSelDept" which should now have no controls and delete it.
Again, select "Cuadro de texto 1" and open its Properties: List Box dialog
On the Data tab make sure the Data Field is ID-Dept. This List Content should remain unchanged.
On the General tab set the Line Count property to 10 (or some other value > 1)
On the Form Navigator select MainForm to open the Form Properties dialog
On the Events tab set After Record change to execute the ToggleReadOnlyProperty macro.
Exit the Design mode and try the form.
As you step through the list of Students, if the Choosen Department list box is empty you should be able to select a department from the list.
For students where a department has already been chosen it should display the department grayed out and thus not allow any changes.
The form Initial Switchboard is no longer needed, because everything can be done on the new form.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the subject line
Apache OpenOffice 4.1.5 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 796
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA


Return to Base

Who is online

Users browsing this forum: No registered users and 10 guests