Ask Your Question

[BASE] Insert to table with opt button and make calculations

asked 2018-10-13 18:00:48 +0100

trelozakinthinos gravatar image


I try to make an application that will manage a gym.

There is a search form for searching the customers [table Customers] and display their info and the programs that they bought. I have made it till this part. I also made an other form that I want to select the gym plan that they want to buy and add it as a new record in their info [table Program]. There are 3 option button sets and a start date picker. One to select the Type [Personal, Semi Personal], the duration [1 month (4 weeks), 3 months (12 weeks), 1 year (52 weeks)] and times per week [2/week, 3/week, unlimited (7/week for the calculations)].

When I select the option buttons and I press a button I want to add a new entry to the table [Program] with those info and make calculations of end date, total visits and cost.

For example I will choose Personal, 1 months, 2/week and 15 Oct 2018. The data I would like to add except the input is: End date: 15 Nov 2018, Remaining visits: 8 (4 weeks * 2/week), and total to pay: 50$.

Is it possible to do something like this? I know some basic stuff on SQL and my Base knowledge is based on the videos of the frugal computer guy. I have never used macros also (I do not know if this matters)

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2018-10-14 00:31:55 +0100

DrewJensen gravatar image

updated 2018-10-14 00:36:10 +0100

IMO macros are nothing to be afraid of and to be expected with just about any database front end application work.

Second - there is, what appears to be, a really fine basic library installed by default with LibreOffice named Access2Base which gives you the ability to do what you want without having to resort to a lot of SQL and which hides much of the complication of LibreOffice API - it does in fact give you the ability to script this the way MS Access would do it. You can find the developer's wiki for that library at even if you are not coming from MS Access you may find this an easier learning curve then the LibreOffice API exposed by the default basic functions. (the Access2Base functions are also included in the LibreOfffice help files these days IIRC)

Beyond that an example of this wouldn't hurt - it is a fairly common thing to want to do (maybe there is already an example here?). I am more comfortable using the LibreOffice basic without the addon library though but still I would be willing to put a simple example together. Why not - later this evening I'll do that. (ps, you don't say which version of LibreOffice or which database, so I'll use version 6.1.2 and an embedded Firebird database for the example, if that works for you?)

edit flag offensive delete link more


Hello, Thank you for your answer! I use 32bit (linux mint) and 6.1.1 32bit (windows), anything else give me error (6.1.1 64b, 6.1.2 both arch, I should mention the bug but I do not know what exactly goes wrong). I want to learn how this is gonna run whichever the method is! I will check your link and come back

trelozakinthinos gravatar imagetrelozakinthinos ( 2018-10-14 12:45:00 +0100 )edit

answered 2018-10-13 18:54:42 +0100

Ratslinger gravatar image

updated 2018-10-13 22:50:37 +0100


What you want to accomplish is conceivable. However is seems your background in Base and especially SQL is limited. Also your approach may be faulty in a number of ways.

Certainly all this can be done using macros, but macros should be avoided whenever possible. Also, there are databases which allow calculated fields - Firebird is one. It is common practice not to store information which can be calculated for other information already saved. For example, your 'End Date' is a calculation of 'Duration' * 'Start Date' (overly simplified). Therefore when displaying the information, the form is based upon SQL.

There are also some potential problems. 'Remaining Visits' is a striking one. Your example show 8 but how is this reduced? Since the earlier selection has a 'times per week' how is the 'Remaining Visits' controlled? This is not to say it can't be controlled. It is just that there is probably a lot more to be determined than what you have presented.

My advice is to lay out on paper what results you are looking for. This can include many aspects such as Customer, Programs purchased. Program expiration, amount due, visits and probably more. Then build tables accordingly so as to not duplicate data. Then build screens, queries (and reports if wanted) based on this. All this certainly requires some database knowledge and more SQL knowledge than you probably have currently.

Some of the LO documentation on Base may help -> LibreOffice Base Handbook. The samples in the docs can be downloaded from there and further down the page is a tutorial for database creation.

Unfortunately Base, databases or SQL is not something to be learned in a few days or weeks.


To give you some idea of how SQL can provide calculated results from your data, see my answer in this post -> SQL Query How to write SQL to acomplish different things in a form?. In the answer is a sample, based upon the question provided. There are no macros in this sample.

Learning SQL will consume much less time than any part of learning to code macros for Base. When dealing with macros you still need to know SQL. Learn SQL!

edit flag offensive delete link more

answered 2018-10-13 19:21:40 +0100

EasyTrieve gravatar image

updated 2018-10-13 20:38:17 +0100

I think you are doing quite well so far. Probably time to learn how to write macros. It's kind of a steep learning curve but in time you will be able to do quite a few things with them.

Here's a reading list I put together as I was going up that hill. Be sure to click 'more' at the bottom, ... as this is a long answer and doesn't all show up at once when you browse to it.

One thing you will need is an introspection tool. I use mri.

Then come back here when you get stuck. :-)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-10-13 18:00:48 +0100

Seen: 37 times

Last updated: Oct 14 '18