Page 1 of 1

Table with multiple entries (array)

Posted: Thu Jan 10, 2019 5:00 pm
by Krone
Hi

I trying to make a hour registration database for a small company.
I have 4 different work types and all work types I would like a starting time and end time, but this give a lot of manuel work setting up it up ( 31 days * 2 (start and stop time) * 4).
There is what is what it looks like:
https://ibb.co/YZXzTzS

Is there a way of making an array for all the starting hours and stopping hours for the different work types and somehow link them to the dato about? If a guide of this exist I would gladly read it.

I'm still quite new to using databases, and im sorry if this is taking the mouth to full.

BR
Anders Krone

Re: Table with multiple entries (array)

Posted: Thu Jan 10, 2019 6:01 pm
by Villeroy
In a relational database you store one work time as:
Date, StartTime, EndTime, WorkTypeID, whatever

WorkTypeID is an integer number pointing to a list of WorkTypes:
ID (automatic), Description, Price, whatever

This is a one-to-many relation where each WorkTime belongs to one WorkType

The input form where you enter the work times you select the WorkType from a list box.

You must not store non-information such as a calendarium with all possible work days to come. A relational database consists of most simple lists of things that actually exist or happened.

Re: Table with multiple entries (array)

Posted: Thu Jan 10, 2019 7:19 pm
by Krone
Hi Villeroy

Thanks for the reply.

If I understand you correctly, it's a bad idea to have all the dates present and then the employee only add the hours. It would be better if they added both the date and the hours?
Is it a completely no go? I'm currently using spreadsheets to control all hours, that sheet looks like this: https://ibb.co/vBPLKVx
I would really like to make something similar, because this works fine, but is it just a lot of work going through all of them each month.

Re: Table with multiple entries (array)

Posted: Thu Jan 10, 2019 7:42 pm
by Villeroy
You may do so on a spreadsheet and then cope with the mess. A relational database follows a more structural approach which makes it possible to store shifts of thousands of workers and query all info about the time shifts easily.
https://support.microsoft.com/en-us/hel ... ion-basics

P.S.
Todays related topic: viewtopic.php?f=9&t=96444

Re: Table with multiple entries (array)

Posted: Thu Jan 10, 2019 8:18 pm
by Krone
Thansks for the link.

I assume that you do alot of database work.
If you where to make a database like this, how many hours would you assume that if would take?
I need to know if it is worth seeking professionel help or trying to figure this out by my self.

Re: Table with multiple entries (array)

Posted: Thu Jan 10, 2019 9:15 pm
by Villeroy
You need a week or two to get the basics by working (not only reading) through a beginners book or workshop. This is far beyond document creation. Database design is a lot more like programming.
Regarding Base, you need another day to get the form design tools and the role of each form control type.
Creating a very simple database with 4-6 tables for productive use may take a full Sunday afternoon if you really know how to do it. For stability and safety reasons is essential that the final structure is exported from the Base document to a stand-alone database directory outside the document.

Re: Table with multiple entries (array)

Posted: Fri Jan 11, 2019 11:30 am
by Krone
I'm still failing to see how I get multiple entries for the different worksdays within a month.
But as you write I'll keep on learning the bacis. I'm currently going through a guide I found on youtube from this guy: https://www.youtube.com/user/TheFrugalComputerGuy

If you have some other great free guides or workshops I would appreciate if you could link to them.

Re: Table with multiple entries (array)

Posted: Fri Jan 11, 2019 3:12 pm
by Villeroy
You enter a date. It is possible to set the current date as default value so you can leave out manual entry instead of entering the current date. If you have to enter some date multiple times, it is possible to set up a main form with preset values and then edit a subform taking over the preset values from the parent form. BUT: all this is secondary stuff. You need a set of normalized tables. Before you start up any software, take a sheet of paper where you outline the set of tables, columns and relations covering all the information that needs to be stored. Before you can do that, you need some theory (relations, normalization, Cartesian product)

A date value includes all information about the year, the month, the quarter, the day in month, the week day and the week number of that day.
You never assign info about a work time to a certain year, month, day etc. The date value speaks for itself. You can tell if it was a week day or not and if you keep a list of holidays, you can tell if it was a holyday or not.

The database simply stores thousands or possibly millions of work times in one table. You never store equally structured information in separate tables for each worker, each month, each work day, each type of shift. Doing so is the most common beginner's mistake.
If you have fixed shifts, you store a table of shifts and store the information about who and when like this:

Table WorkTimes
ID(auto) DT P_ID SH_ID

For technical reasons, the table has a unique record identifier "ID" as primary key (integer number) which is not shown to the user. It is just for internal reference.
Everything about the day in field "DT" of type Date.
Everything about a person by means of integer P_ID which is a reference to all the information about a person in a separate persons table. Integer P_ID refers to a unique primary key (say "ID"(auto)) in the persons table.
Everything about a shift by means of integer SH_ID which is a reference to all the information about a shift in a separate shifts table. Integer SH_ID refers to a unique primary key (say "ID"(auto)) in the shifts table.

In this table "WorkTimes", the integer numbers "P_ID" and "SH_ID" are so called foreign keys.

The following record set
12345 2019-11-01 99 5
describes a work time with auto-ID 12345 completed on Friday, 11th of January in the year 2019 (week #2) by person #99 at shift #5.
All details about person #99 can be looked up in the related persons table.
All details about shift #5 can be looked up in the related shifts table.
The date speaks for itself.

If the relations are set up properly, it is impossible to enter a foreign key P_ID that has no matching primary key value in the persons table, and it is not possible to enter a a foreign key SH_ID that has no matching primary key value in the shifts table. This is called "referential integrity".

This is a formal network of foreign and primary keys linking tables where each table row (record) describes exactly one item and each table column (field) describes exactly one property of an item. Since the 70ies empires are built on relational databases.

Re: Table with multiple entries (array)

Posted: Fri Jan 11, 2019 6:56 pm
by UnklDonald418
If you have some other great free guides or workshops I would appreciate if you could link to them.
For an introduction to database design
https://wiki.documentfoundation.org/ima ... torial.pdf
Also, look at the top of the Base forum page for the Base Tutorials section. There you will also find an Examples sub-section.

Re: Table with multiple entries (array)

Posted: Tue Jan 15, 2019 3:37 pm
by Krone
I've succeed making the functions that I would like for the time registration, thanks to the help.
My relations is now made like this: https://ibb.co/tJj4Krz

I now have a some other questions regarding auto fill according to the last used entry.
Here is a link to my design: https://ibb.co/6NGSZk8
What I would like is when I press next the "Next Entry" button, I want it to use the previous selected in my list box for the new entry.

Another thing that I would like that the default dato for "Work Data" change according to the month ID. I would also would like to make it so that you only is allowed to change the date to something within the selected month.

The last thing I would like is some sort of login method, for the different employees. As it is now all employee can see and add time registrations for another empolyee, which is not ideal.

I hope that some of you are able to help me.

BR
Anders

Re: Table with multiple entries (array)

Posted: Tue Jan 15, 2019 4:39 pm
by Villeroy
May be you want a professional database development tool. Base is just a tiny addition to this office suite. WIth some creative handling of the existing features you can get quite far.

The topic viewtopic.php?t=23659&p=107888#p107888 discusses the "copy record" issue in depth. The attached defaults2.odb includes 2 forms following different approaches.
A subform having fields that are linked to fields of its parent form inherits the field values that are selected in the parent form. The subform is filtered by these values in order to show all subform records related to the parent form's current record AND inherit the parent form values for any new record.
"Form2" lets you select an existing data record in the grid and the subform (which is set up to take new records only) gets the date and category values so you only have to enter a value for the new record.
"Form1" is a little bit more sophisticated. It makes use of a table named "Filter" with only one record taking the filter criteria.
The main form is linked to this particular row by means of SQL: "SELECT * FROM "Filter" Where "FID"=0" and the subform works as expected.

When you set up such a filter form, you disallow everything but modification of data. Otherwise the user may tab into the new record or delete the filtering record.
Search this forum for "power filtering"
The following screen shot shows a typical properties window of a filter form.
Image

Off topic:
Subforms and list boxes play an important role with Base forms because they allow you to edit related row sets: [Example] Relations reflected by list boxes in forms (one-to-many and many-to-many relations)
The "form wizard" is almost userless since it covers no more than 10% of the possibilities. The "form navigator" window allows you to build form hierarchies consisting of many forms and subforms at any level. Call the 5th button on toolbar "Form Design" and right-click to add logical forms. A more sophisticated set of manually created forms: download/file.php?id=34817 (soccer world cup tournaments).

Re: Table with multiple entries (array)

Posted: Tue Jan 15, 2019 4:50 pm
by Krone
Thanks for the quick reply.
I'll have a look at the links later today.

To understand you correctly, do you suggest that i use another database program for this database?