[Solved] Updating a lookup table

Creating and using forms
Post Reply
lsemmens
Posts: 49
Joined: Thu May 22, 2014 11:17 am

[Solved] Updating a lookup table

Post by lsemmens »

I have developed a database that contains several lookup tables linked to a master table as a 1-n relationship. The form for the MASTER Table has several list boxes that extract the data from those lookup tables. All works well until I find I am missing something in one of the lookups. I can easily open the source and add a new record but...... I cannot find any reference to the new record in the lookup from the list box on the Master form without closing and re- opening the MASTER form.

Code: Select all

Sub CloseForm
	Dim oForm
	oForm = ThisComponent.DrawPage.Forms.getByIndex(0)    ' Main Form
	oForm.updateRow()
	oForm.dispose()                                       'releases all references
	oForm.close(true)                                     'closes the form
	thisComponent.CurrentController.Frame.close( true )   'closes the window
        oForm.reload()   'refresh SubForm	<<<<<< This has no effect which is what I expected as this is the macro to close the window with the lookup data.
End Sub 
How can I show the new entries without having to re-load the master form?
Last edited by robleyd on Fri Jun 07, 2019 9:45 am, edited 2 times in total.
Reason: Add green tick
LibreOfice Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.6 HQSLDB on Linux Mint 19 Tara - Cinnamon 3.8.9 (Gtk 3.22.30-1ubuntu3)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Updating a lookup table

Post by Villeroy »

The navigation toolbar has 2 refresh buttons. The second one becomes accessible when a list box or combo box has the focus. It refreshes the current control only, staying at the same record position and without reloading the entire form.

A macro to refresh anything anywhere: viewtopic.php?f=21&t=88831
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: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Updating a lookup table

Post by UnklDonald418 »

Another option that doesn't require a macro is to assign a Push Button control to the form or sub-form where the list box control is located. Set the Action property for the Push Button control to Refresh Form.
After you add a new record to the lookup table, press the Push Button to update the selection list for the list box.
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: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Updating a lookup table

Post by Villeroy »

The draw back when refreshing the entire form is that you lose the record where you wanted to enter a new item ID from the list box.

We can have a separate button to refresh a list/combo box with no macro:
Set button action = "Open Web Page or URL"
URL: .uno:RefreshFormControl
This does the same as the second refresh button on the toolbar when a list/combo box has the focus.
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
lsemmens
Posts: 49
Joined: Thu May 22, 2014 11:17 am

Re: Updating a lookup table

Post by lsemmens »

I am now getting very frustrated. I was hoping to upload a stripped down version of my program so that you can see my issue and the smallest it will compact to is 1.6Mb so I've re-written it with just a couple of fields. Two issues have come to light, the first is the subject of this thread, the second has only just popped up, which is the error message if I do not move away from the newly added record when i hit the "close" button.
Attachments
stampEXAMPLE.odb
(21.67 KiB) Downloaded 245 times
LibreOfice Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.6 HQSLDB on Linux Mint 19 Tara - Cinnamon 3.8.9 (Gtk 3.22.30-1ubuntu3)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Updating a lookup table

Post by Villeroy »

Every master can have 0, 1, 2 or more colors while every color may belong to 0,1,2 or more masters?
That would be a many-to-many relation.
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
lsemmens
Posts: 49
Joined: Thu May 22, 2014 11:17 am

Re: Updating a lookup table

Post by lsemmens »

NO it is 1-n every master can only have one colour. every colour can have multiple masters.
LibreOfice Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.6 HQSLDB on Linux Mint 19 Tara - Cinnamon 3.8.9 (Gtk 3.22.30-1ubuntu3)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Updating a lookup table

Post by Villeroy »

1) I stripped the macros and forms andadded a unique index on color names so the table won't accept duplicate names.
2) I let the form wizard create a new MASTER form.The wizard can not produce list boxes. The context menu of any visible control has a submenu "Replace with...", so I replaced the color ID box with a list box. A working list box has a first column of visible text (color names) and an invisible column keeping the primary key of some other table (IDs of colors), ordered by the visible names. See data properties of the form.
3) I added a most simple logical form to the same form document which serves the only purpose to insert a new color name into the color table. Property "New Record Only" and no navigation bar. Notice that the form is not a subform of another form as shown in the forms navigator from which I added a screenshot.
3a) The form has an OK button by default pushed when you finish the name entry with Enter. The action property of the OK button is set to "New Record", so the field is cleared after the new record has been saved.
3b) The cancel button can be used to cancel the entry of a new name.
So far the form works well. You can enter a new name, go back to the main form's list box and then click the second refresh button.
3c) The text box where you enter the colour name is a combo box showing already names: SELECT DISTINCT "COLOUR" FROM "COLOUR" ORDER BY "COLOUR"
4) I added a hidden field to the new-color-form named "AutoRefresh". Its string value points to the other form's list box. I also added a comment to that control. If you installed my AutoRefresh.py module you can easily activate that macro without further customization. Hidden fields are made for macro customization.
5) There is a close button on the form which works without any macro code. See button properties.
Attachments
stampEXAMPLE_Villeroy.odb
One-to-many with new-item form
(27.15 KiB) Downloaded 251 times
Last edited by Villeroy on Thu Jun 06, 2019 3:22 pm, edited 1 time in total.
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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Updating a lookup table

Post by Villeroy »

The color names may not follow any strict rule about admissible color names. If you want to enter any color names freely while still having kind of pre-fill feature, you may consider to drop the color table entirely and replace the table's foreign key (color-ID) with a text field. In the form you replace the list box with a combo box SELECT DISTINCT "Colour" FROM "MASTER" ORDER BY "Colour". A combo box pre-selects existing entries while you are still able to enter any not yet existing name.
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
lsemmens
Posts: 49
Joined: Thu May 22, 2014 11:17 am

Re: Updating a lookup table

Post by lsemmens »

Thank You Villeroy, that appears to solve it. Rather than a list box, the combo seems to solve two problems. I'll do some mods and perform some testing. If all is well I shall flag this a s solved! :bravo:
LibreOfice Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.6 HQSLDB on Linux Mint 19 Tara - Cinnamon 3.8.9 (Gtk 3.22.30-1ubuntu3)
lsemmens
Posts: 49
Joined: Thu May 22, 2014 11:17 am

Re: Updating a lookup table

Post by lsemmens »

That works fine, except there will be no cascade of updates if, say I change Red to Green, not that ever should be an issue. Thanks again
LibreOfice Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.6 HQSLDB on Linux Mint 19 Tara - Cinnamon 3.8.9 (Gtk 3.22.30-1ubuntu3)
Post Reply