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

[trelozakinthinos]

Hello,

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)

[Ratslinger]

Hello,

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.

Edit:

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!

[EasyTrieve]

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.

[drewjensen]

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 Access2Base - It's about converting PEOPLE, not data 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?)

[trelozakinthinos]

Hello, Thank you for your answer! I use 6.0.6.2 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