Handbook.

Introduction to databases

A database is simply an organized collection of information. The organization of this information is made possible thanks to the division into tables. Imagine as an example a phone book represented by a table with rows and columns: The columns (also called fields) will therefore be Name, Surname, Address and Telephone, while a line (also called record) represents information about a person. Databases are an IT tool and are managed through special software with which we can view, insert, modify or delete data. Databases can hold thousands of data and still take up little virtual space. This is one of the advantages, we think of the immense paper archives for which entire rooms are needed to contain them. A second advantage is research. The databases offer a quick consultation of the data simply by entering the term to be searched in a text box on the terminal. Naturally the operation will be much faster than going to consult hundreds of paper archives. Advanced solutions allow then to perform sophisticated searches with more search terms. The data in a database allow us, here is a third advantage, to extract important information through data analysis using specific software connected to the database. For example, we can improve the business of a store by analyzing a product table to see which ones sell more and which sell less.

We often use databases without realizing it: e-mail has a database, a phone book on the phone, a program for managing customers, a project manager and many others. Of course there are also specific tools for database management. They allow you to create, modify and consult databases through a more or less simple interface.

The online database

Online databases are a separate category because they are often software to be used directly on the web and the data reside on cloud systems, so it is often not necessary to download and install an application on your computer, just a simple browser and an internet connection will be enough .One of the big advantages is the ability to centralize data in one place and share it with other people.

Database and tables

Creating a new database

The first page that appears after creating a Basebear account is where to create a new database. Clicking on the appropriate button Create new database, the system will show a window where you can enter the name and choose whether to create one from scratch, copy from a model database or copy from an existing database, this last item involves the presence of another database already present in your account.

The databases template are databases already configured and ready for use. There are several types, here are some examples: Inventory management, Interventions managements, Customer support, Project management and others. Using a template is also a good way to see how they are made. If you use one you can anyway choose to modify it according to your needs.

The name and time zone of a database can be changed on the database list page by clicking on the Setting button. The time zone is inherited from database administrator settings, but it is also possible to put a different one.

Delete a database involves the loss of all database data (data and attached files), so be very careful and proceed with caution before performing this action.

Create tables in the database

The database can be defined the container, inside it there are the tables that contain the data.

When creating a new table the only mandatory information is the name and icon. The rest is therefore optional. Let's look at them in detail:

Records per page indicate how many records can be displayed on the screen. However, the arrow buttons allow you to move between pages to view all the data.

Users can export data allows users who have access to the table to use the data export feature.

Users can perform bulk edit can be enabled or disabled for those users who have access to the table. In order to use this property, users must also have permission to modify the table

Show the information of when and who creates and modifies the data lets you show when a record was created, who created it, when it was modified, and by whom. They can be shown in the table or only in the detail of a record.

Template for custom printing with this feature you have the possibility to change the print layout of the data of a single record. First you need to create a document in RTF format with Microsoft Word or another text editor. Inside the document you can choose to insert text and images in the position you want. In the document you must then place placeholders for the data of the fields of the record written with the name of the field enclosed in square brackets, example: [field name]. The only limitations are that the document can contain a maximum of 100 paragraphs and 5 tables. The RTF document once created must be loaded into the table settings. You can also choose in which format the document is created by the system being printed: PDF or RTF. To print, you need to access the data, click on a record (line) and click on the print button.

The elimination of a table involves the loss of all the data and any attached files present in the table itself. So be careful to perform this operation. The elimination of a filtered table instead does not erase the data, this because the filtered tables are custom views on the data of normal tables.

Add columns to a table

After creating a table it is necessary to add one or more columns (fields), which also correspond to the fields of the table itself. Clicking on the Add column button a window will appear where you can choose which type of column to add, here is a brief description of each:

• Sequential number automatically adds a progressive number to each record created starting from the number 1.
• Short textis a field that shows in the form a normal text box in which to insert a text up to 250 characters.
• Long text as a short text but with the possibility of exceeding the limit of 250 characters. In the form the text box is larger, and it is possible to write on multiple lines.
• Number it only accepts numeric values, it is possible to define the number of decimal places, see the value in percentage and whether to show the thousands separator.
• Yes\No the only allowed values are yes or no, a check box is shown on the form.
• Date-time this field has been designed for date and time values. You can define the format in which the value is shown on the screen and whether you have the time value or not.
• Dropdown shows a typical drop-down list to choose values from. In the settings you can define the values manually or take them from a column of another table. This field is useful for linking two tables. If you apply the relationship with an external table, you can choose whether to keep the value updated automatically when the source value changes. The "Shows the link to the source record" option allows you to view the source record by clicking on the field value.
• Radio shares the same properties as in the Dropdown list column but in the input form is shown a list of immediately visible options to choose from, you can only select one value.
• Checkbox it's like the Radio column but in this case you can choose more than one option.
• URL is used to insert a link to a web page. This field shows two text boxes during insertion, one for the link and the other for any alternative text to be shown on the screen.
• File / Image with this column you can attach a file or image to a record. For images it is possible to choose whether to show the preview in the tabular display of the data.
• Email has the function of accepting only e-mail values.
• Currency similar to the Number column, it is used to contain numeric values in floating point based on the type of currency chosen.
• Formula is the column used to perform calculations using the values of other columns of the same record. More information can be found here.
• Derived is a column that inserts its value automatically using the value of one field from another table. To be used in the table there must already be a Dropdown column that takes the data from another table, this is used by the derived column as a reference.

Almost all columns share the following properties:

• Initial value it is used to have an initial value when entering a new record.
• Supporting message in the insertion and modification form shows a support text in the field.
• Required field requires that a value be entered when entering a new record.
• No duplicate entries when entering or editing a record, it checks that the value has not already been entered.
• Show total this option can only be used if the column has numeric values. It serves to have at the end of each page of the table, in correspondence of the column, the sum of the values.

On the table page of a database there are other options at each column:

• Position the numerical values in this list are used to modify the positioning of one column with respect to the others.
• A-Z / Z-A indicates whether to sort the table records for this column in ascending or descending order.
• Show / Hide It is used to show or hide the value of the column in the tabular display of the data. When you access the detail of the record by clicking on the table row the value of the column will still be visible. To completely hide a column from a user, you need to use custom permissions.

Create filtered tables

On the table page of a database it is also possible to create filtered tables. When a new one is created it is necessary to choose a normal table as a reference. In fact, the filtered tables show the same data as the reference table, but one or more filter conditions can be applied to these data. This way you can have a partial view of the data. When working on the data of the filtered tables: the insertion, the modification and the elimination also take place on the data of the normal reference table.

Filtered tables are a powerful tool that you can use to show users a partial view of the records.

You can choose to combine filter conditions in many ways using the "e" or "or" operator and use keywords to have dynamic behavior:

• TODAY() Return the current date. To be used only with date and time columns.
• TODAY(n) Returns the current date increased by a number of days specified in the brackets. Only accept integer values, even negative ones. To be used only with date and time columns.
• TOMORROW() Return the date of tomorrow. To be used only with date and time columns.
• NOW() Return the current date, time and minutes. To be used only with date and time columns.
• EMAIL() Returns the email of the logged in user. Useful for filtering and then displaying records based on the email of the connected user.
• USERNAME()Returns the username of the logged in user. Useful for filtering and then displaying records based on the username of the connected user.

Access the data

After creating a database with tables, you can access the data in different ways. From the list of databases, a page that appears even when you have accessed Basebear, you can click on the name of a database and you will be redirected to the data in the first database table. From here you can also reach the other tables using the menu bar that shows the name of all the tables. Another way is to click on the “Go to data” menu item in the database modification pages.

Importing data from an Excel file

On the tables page you will find the Import from Excel button that allows you to access the features to import data from an Excel file. The system imports data only from the first worksheet. The data must be structured in tabular form with the name of the columns in the first row. There are 4 different import options:

• Create a new table this first option allows you to create a new table using the data imported from an Excel file. In this case you must also specify the name of the new table.
• Insert data into a table this option adds the data to those of an existing table.
• Edit data in a table using the import it is possible to modify the data already present on an online table. In this case it is necessary to specify the column to be used as primary key. The primary key column is used to uniquely identify the records in the table and must be present both on the online table and in the import table. In this way the system can understand how to modify the records. Often a "Sequential number" column is used as key columns.

In import it is also possible to use a file in CSV format, that is a text file in which the values are delimited by characters such as the comma or semicolon.

Another option to import data and to copy them from an Excel file and paste them into the text box on the page.

Note that the import does not check for duplicate values in the data.

Email notifications

Email notifications are the way to send to recipients messages containing all or some of the values of a record when it is inserted, modified and deleted. Notification settings are accessed by clicking on the Notifications link near a table. In addition to the name, it is required to enter at least one recipient and tick at least one event between Insertion, Modification and Deletion.

The content of the email, subject and message can be customized a pleasure including also some key words listed below:

• [TableName] inserts the table name.
• [DatabaseName] inserts the database name.
• [UserPerformedAction] inserts the username that performed the action.
• [RecAction] inserts the name of the action that was performed.
• [RecData] inserts all the values of the record fields involved in table form.
• [RecData([nome colonna 1], [nome colonna 2], ...)] inserts the values of the specified fields of the record involved in table form.
• [column name] inserts the value of the specified column. In case of multiple records involved, such as mass modification and deletion, the values of the first record will be used.

In the settings of a notification it is also possible to add a condition, written with the syntax of the formulas, which is used as a discriminant for sending the email.

Calendar

The records of a table that represent temporal events, identified by a date, can be displayed on the calendar. In the editing area of a database, the Calendar menu item allows you to access the page where you can link one or more tables to the calendar. A table, to be connected, must have at least one datetime column and a text type column. The first is to identify how to position the records in the calendar at a temporal level, the second as a label to identify the record on the calendar. Eventually, with a second date and time column in the table, an event end date can be set.

The calendar will be visible to all users but will only see the data for which they have permissions.

User management

In the database sharing page you can find the tools to share the database with other people. at the top of the page it is possible to choose whether to make a public or private database.

A public database is accessible by those people who know the database url (the one with the yellow background) and do not need email and password to access. On the public database it is possible to choose the access permissions of unauthenticated (anonymous) people through a dropdown list that appears on the right to the public option.

Sharing the database online with other people is one of Basebear's strengths and you can do it with ease. By clicking on the "Add user" button you can invite people to access the database. You will only need to enter the email, an option indicates whether to immediately send the invitation email containing the link and credentials. Users can receive the invitation at a later time, just select them and click on the "Send invitation" button. Users can enter Basebear from the link sent in the email or from the Basebear login page.

Authorization management

In correspondence of each user, by clicking on the dropdown list, it is possible to set the permissions to allow him to see all the data in the database, modify them or log in with custom permissions.

Custom permissions allow you to define permissions at the table and column level. A user can therefore only have access to certain tables and columns. In custom permissions you can also choose whether to show a user only the records he has entered. On the Sharing page there is the Groups button, this is used to set the permissions to a group only once. Users are then associated with groups, so authorization management is faster and better organized.

To associate a group to a user, first create the group and then use the dropdown list under each user to link it to the group.

Database and tables export

Exporting data to Basebear is extremely simple. You can use this feature to back up your data whenever you want.

On the database list page you will find the "Export database" link, which refers to the dedicated page. On this page you can select a database and export it. The download file will be a compressed file containing the data of the database tables in CSV format, a format that can also be read with Excel. This type of backup does not include the files attached to the table records, to get them you need to contact the Basebear support.

Another way to export data is to do it from the tabular display of data using the export dropdown list. From here it is possible to export only the data of the table in which you are located and you can choose different export formats: Excel, Excel 97-2003, HTML, CSV and TSV.