Specified count of records in subform

Creating and using forms
Post Reply
harda
Posts: 2
Joined: Sat May 28, 2016 9:53 am

Specified count of records in subform

Post by harda »

Hi guys! I'm a newbie here but I started to love LO and the community arround it. Hope you can help me solve my problem.

I am trying to build a simple database to watch productivity and few other things in our factory. We use Calc for this right now, but as the number of records and functions rises, I think we should migrate this in Base. Building the database itself seems to be quite easy, but right now I am struggling with an input form/subform.

The question is - can you somehow specify how many records need to be in subform for each record in main form?
Simply so, that the subform it looks this way:
Výstřižek.PNG
Above this table are fields for date, name, etc. - those stay in main form.
In this table a numeric value (project ID) is filled in for each ten minutes interval. I need to count these values and group by fields from the main form, so I believe they should be kept in a single field. I have no idea how to do this though :lol:

In case my question is not clear enough, just ask me and I will try to explain better ;)
LibreOffice 5.1.2.2
Win10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Specified count of records in subform

Post by UnklDonald418 »

The solution to your problem lies in a SQL Query. You might be able to create your Query using the Query Wizard or in the Query Design View. SQL does have COUNT and GROUP functions. If you are actually going to use Base you need to learn SQL.
This is a link to a fairly comprehensive tutorial on Base.
https://wiki.documentfoundation.org/ima ... torial.pdf
Chapter 9 is where he discusses Queries, but I would recommend you read the entire tutorial if you want a good foundation in database design and SQL.
If you upload a small sample of your database here someone might be able to provide you with a more specific answer.
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
harda
Posts: 2
Joined: Sat May 28, 2016 9:53 am

Re: Specified count of records in subform

Post by harda »

Thanks for your reply!
I have of course read the tutorial you linked to (and few others) before posting here ;)
The problem is not in queries, more likely in my english :D

I attached a sample database, 5 tables, 1 form, no queries. The form InputA works quite well, allows me to have a different amount of records in InputA2 for each record in InputA1
These 2 input tables allow me to watch how many pieces of a product were made on a workplace per day.

Then I need to have another table/tables to watch who was working on that place and for how long. There's an obvious way, having two tables with following fields:
InputB1: PersNr, Date and automatic key IDinB1
InputB2: IDinB1, StartTime, EndTime, IDwp and automatic key IDinB2
And then a query to subtract the times, group by workplace and calculate the productivity. I have even tried to write one and it worked, so I'm not worried about queries :D

But having the feedback from people, that will eventualy fill in the data, I'd like to make this more comfortable for them. There is so much workplace changes, they would end up writing just start and end times forever. And here comes the picture I posted in previous post - each worker gets a card like this, writes IDwp to each ten minutes interval accordingly. So my idea was to make the form look just the same. That would mean two tables with following fields:
InputB1: PersNr, Date and automatic key IDinB1
InputB2: IDinB1, IDwp and automatic key IDinB2
And a query to count the amount of IDwp for each IDinB1.
Here I got lost. I need the form to show specified amount of InputB2 records for each InputB1 record - and let me to fill in just some of them. Is there a way to do this?
Attachments
simpleProductivity.odb
(12.32 KiB) Downloaded 197 times
LibreOffice 5.1.2.2
Win10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Specified count of records in subform

Post by UnklDonald418 »

I looked at your upload and it wasn't clear to me what you were doing with some of your tables. But you said
I posted in previous post - each worker gets a card like this, writes IDwp to each ten minutes interval accordingly. So my idea was to make the form look just the same.
I uploaded FactoryDemo.obd that may point a way for you. It demonstrates a form similar to my understanding of what you want.
There is a table 'TimeSheets' that stores the data being entered.
There is a form TimeSheetEntry that has 6 tables. If needed, it could be modified to the twelve on your form, I used 6 because it was easier. If you inspect it with the Form Navigator you will see each table is in a SubForm that uses a query and filter to populate that table. The user can then enter a Job Number in the appropriate TimeSlots.
There is a Date Field that allows the user to select the date they want to enter the data. This uses a filter table 'DateFilter' that stores only one record. That table is used by the Date Field to link to the SubForms. There is a Refresh Button that saves the current data and loads new data if a different Date has been selected.
There is a second button that is used to insert a new day of empty TimeSlots from the table 'TimeSlotsTemplate' . This uses a macro to execute an SQL Insert command. It can be viewed at :
Tools->Macros->Organize Macros->OpenOfficeBasic->FactoryDemo->Standard->InsertNewDay->Edit
The macro only works on registered databases so you will need to make sure that FactoryDemo.obd is registered. It does check to see if there is any data for the current day before adding a blank Time Sheet.
Finally, there is a query "Query_CountTimeSlots" that displays a list of Jobs and the number of matching TimeSlots.
Attachments
FactoryDemo.odb
(39.18 KiB) Downloaded 230 times
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
Post Reply