Page 1 of 1

[Solved] Enter data in more than one field with a List Box?

PostPosted: Mon Jan 04, 2021 2:09 am
by kdunkelberg
I'm using LibreOffice Base on a Mac (OS 10.13), and am new to OpenOffice Base. I would like to use one List Box to enter data into three fields of a form to update a table with this data. I have been able to create a list box that lists one field and then adds the data from another field from the source table to the correct field on the form. It seems like I should then be able to take data from two other fields to populate the form, possibly using a macro. But there may be an easier way to do this that I'm overlooking, since I'm new to using databases.

Here's a more thorough explanation of what I'm trying to do, in case that's helpful:

I have three tables in my database that are linked:
Titles, containing fields for TitleID, Title, Genre, etc. (that aren't relevant here), where TitleID is the primary key
Places, containing fields for PlaceID, Place, Type, etc. where PlaceID is the primary key
Submissions, containing fields for SubmissionID, TitleID, Title, PlaceID, Place, Genre, Type, etc. (that don't affect what I want to do here)

I want to use the list box to choose a Place and enter PlaceID, Place, and Type into the form to update Submissions
Then I would use another listbox to choose a Title and enter its TitleID, Title, and Genre into the form.

I know I could have a Submissions table that only had SubmissionID, TitleID, and PlaceID, but I want to be able to filter Submissions by Genre or Type in queries on the Submissions table, and I want to display the Place and Title on subforms that display submissions for each Place and Title, so I can see what has been submitted where, when, etc. That's all working well with the database structure I have currently (imported from a SuperCard project I've been using, via a Calc spreadsheet, so I have lots of data).

Maybe I'm making things too complicated, and I could accomplish everything by linking relationships between the tables better. Right now the Submissions table is linked to Places and Titles with PlaceID and TitleID as foreign keys. If I could use Relationships to automatically bring in Title and Genre, for instance, then I may not need update more than the one field.

I'm open to other solutions, in other words, but the way I conceive of it now is to use list boxes to select the place and the title, so that I always put the right PlaceID and TitleID into each Submissions record and also transfer in the Place and Type or Title and Genre. Is there a way to do this in the List Box control? Or do I need to use a macro to update the two additional fields? Or do I need to try a better solution?

I have LibreOffice Base Guide and have searched this forum and elsewhere online for answers, but haven't found any (maybe I'm not asking the right question). I'm comfortable reading the manual and looking for answers, but thought I might save some time if someone could point me in the right direction, especially if I'm conceiving of things all wrong or missing something basic. Thank you in advance.

Re: How to enter data into more than one field with a List B

PostPosted: Mon Jan 04, 2021 6:28 pm
by F3K Total
Hello,
it's not a good idea, having same datas in two tables. Use a query instead, if you involve ALL primary keys of the participating tables, the query will stay writeable.
But that must not be done this way, you can also have an inputform, based only on table submissions without the extra fields, and then use the query for filtering in another form.
Find a sample attached.

Re: [Solved] Enter data in more than one field with a List B

PostPosted: Mon Jan 04, 2021 8:26 pm
by kdunkelberg
Thank you for the quick reply. I was afraid I was barking up the wrong tree. Thank you for the examples as well. I'll explore using queries more and I'm sure your examples will help me see how I can do what I'm trying to do that way.