[Solved] SwitchBoard with Filter and Button

Discuss the database features
Post Reply
DreamerArgentino
Posts: 17
Joined: Sun May 20, 2018 12:02 am

[Solved] SwitchBoard with Filter and Button

Post by DreamerArgentino »

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 264 times
Last edited by DreamerArgentino on Thu Jun 21, 2018 2:41 pm, edited 1 time in total.
OpenOffice 4.1.5 On Windows 10
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SwitchBoard with Filter and Button

Post by UnklDonald418 »

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 beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
DreamerArgentino
Posts: 17
Joined: Sun May 20, 2018 12:02 am

Re: SwitchBoard with Filter and Button

Post by DreamerArgentino »

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
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SwitchBoard with Filter and Button

Post by UnklDonald418 »

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

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 beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
DreamerArgentino
Posts: 17
Joined: Sun May 20, 2018 12:02 am

Re: SwitchBoard with Filter and Button

Post by DreamerArgentino »

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

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
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SwitchBoard with Filter and Button

Post by UnklDonald418 »

it does not work

What does not work?

Try this.
TASKS-000 + SwitchBoard-Modified.odb
(45.25 KiB) Downloaded 269 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 beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
DreamerArgentino
Posts: 17
Joined: Sun May 20, 2018 12:02 am

Re: SwitchBoard with Filter and Button

Post by DreamerArgentino »

"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
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SwitchBoard with Filter and Button

Post by UnklDonald418 »

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 beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SwitchBoard with Filter and Button

Post by Villeroy »

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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
DreamerArgentino
Posts: 17
Joined: Sun May 20, 2018 12:02 am

Re: SwitchBoard with Filter and Button

Post by DreamerArgentino »

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
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SwitchBoard with Filter and Button

Post by Villeroy »

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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SwitchBoard with Filter and Button

Post by UnklDonald418 »

You could try this
First I added a new macro

Code: Select all

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

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 beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
DreamerArgentino
Posts: 17
Joined: Sun May 20, 2018 12:02 am

Re: SwitchBoard with Filter and Button

Post by DreamerArgentino »

UnklDonald418 wrote:You could try this
First I added a new macro

Code: Select all

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

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.
Hello! thanks for answering!

I followed everything to the letter, but the "listbox" is disabled ... something will have done wrong?

Anyway, resuming the previous method, with the SwitchBoard; Could it be like this ?: That "Initial-Switchboard01" opens only once automatically, and the subsequent times that the database is opened, go directly to "FRM-Students01" already with the last chosen Department ...

Is there any way of doing this?

Thank you very much for your help!
Attachments
TASKS-0002 SwitchBoard-Modified.odb
(55.58 KiB) Downloaded 267 times
OpenOffice 4.1.5 On Windows 10
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SwitchBoard with Filter and Button

Post by UnklDonald418 »

I downloaded TASKS-0002 SwitchBoard-Modified.odb and FRM-Students02 works fine for me.
If the Choosen Department field is blank I can select a department using the list box.
If the Choosen Department field displays a department the the list box is disabled.

I originally tried to make the first approach work but it got too complicated so I abandoned it in favor of FRM-Students02.
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
DreamerArgentino
Posts: 17
Joined: Sun May 20, 2018 12:02 am

Re: SwitchBoard with Filter and Button

Post by DreamerArgentino »

UnklDonald418 wrote:I downloaded TASKS-0002 SwitchBoard-Modified.odb and FRM-Students02 works fine for me.
If the Choosen Department field is blank I can select a department using the list box.
If the Choosen Department field displays a department the the list box is disabled.

I originally tried to make the first approach work but it got too complicated so I abandoned it in favor of FRM-Students02.

Hi, I already test it on two different computers and none of them work for me!
"Choosen Department field" It does not allow the selection (it does not display the list of options) and it is always blank! :crazy: :crazy: :crazy:

Apologies for the inconveniences !!
OpenOffice 4.1.5 On Windows 10
UnklDonald418
Volunteer
Posts: 1540
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: SwitchBoard with Filter and Button

Post by UnklDonald418 »

I already test it on two different computers and none of them work for me!
That's interesting.
I tested it on another machine running OpenOffice 4.1.5 and it works fine there also.
Then I tested it on LibreOffice 6.0.4.2 and it exhibits the behavior you describe.
Look for the following line in the "ToggleReadOnlyProperty" macro.

Code: Select all

If oControl.CurrentValue  = "" then
and replace it with

Code: Select all

If  oControl.BoundField.String = "" Then 
That fixed the problem with LibreOffice so hopefully it will work for you also.
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
DreamerArgentino
Posts: 17
Joined: Sun May 20, 2018 12:02 am

Re: SwitchBoard with Filter and Button

Post by DreamerArgentino »

UnklDonald418 wrote:
I already test it on two different computers and none of them work for me!
That's interesting.
I tested it on another machine running OpenOffice 4.1.5 and it works fine there also.
Then I tested it on LibreOffice 6.0.4.2 and it exhibits the behavior you describe.
Look for the following line in the "ToggleReadOnlyProperty" macro.

Code: Select all

If oControl.CurrentValue  = "" then
and replace it with

Code: Select all

If  oControl.BoundField.String = "" Then 
That fixed the problem with LibreOffice so hopefully it will work for you also.
Now it works! Many thanks! A hug!
DreamerArgentino.-
OpenOffice 4.1.5 On Windows 10
Post Reply