Hi, Base newbie here. I’m working on a project for school, and I’m trying to make it so when I update a number(count) on a form, the Total Count goes up as well. Is this something I need to do on my table or what? I just need a resettable counter that when I click a spinwheel, the overall count goes up +1.
Hello,
Edit your form. Should be using a numeric field. Select properties for control (right click control and select Control Properties).
There you will find elements such as Spin Button, Inc./decrement value, Initial and min max values.
I can do that already, my issue is, I want it to automatically update another field when I update one field. Basically add one to the overall counter.
Sorry I must have mistaken this:
What you ask for depends a lot upon the database you are using. Some you can have a calculated field and others you may do this with a Trigger or Procedure. There is also the use of macros to do this.
It may help if you provide your OS, specific LO version and the database you are using.
Ok, I’m using Mac OS, LO 7.2.5.2, HSQLDB, (embedded database).
Here is a pic of my table design.
I have most fields on one table, the “Current Rank” field pulls data from a different table, but what I’m trying to do is update the total when I click the spin button to update the “# Classes Since Promotion”.
It appears you would need to create a macro to perform this. But the process doesn’t even make sense. What if too many classes are entered? Mistakes happen. In real life today I enter 2 and tomorrow I don’t recall if yesterday’s entries where completely done.
Also to actually give you the code, is this providing you with the answer you need for class credit? To see something similar, look at the sample in my edit of this answer → Code counting
.
Edit:
Very little you can do with HSQLDB embedded (that is v1.8 - very old). Looked into Triggers but seems cannot be done with that version.
1 Like
I can write a macro I think if I can find how to create a new macro. I am somewhat familiar with SQL language. I was just wondering if this is the best(only) way to do it. It seems clunky to me.
No! Already noted:
The issue is your use of HSQLDB embedded and the use of a process in a relational database of which it is not intended for - total accumulation within a record based upon something else in the record. I still have no idea how you think this is to work. Seems ridiculous if every time I access that record one can hit the spin button and update the total!
Has nothing to do with coding a macro. Already gave you a link to a sample with the the code in it. Not good enough I guess.
Please post the original task - not just how you think it should work. With what you have, it will be difficult at best for you to complete.
Just based upon the outlined form.
Main table record should be - ID, Name, Current rank, Date of last promotion.
Related table record - ID, Link to Main Table ID, Date of completed class
With that, you can create SQL for a query to display a total number of classed since the last promotion. SQL can count # of records since.
No macros and no fancy counters. Simple SQL. Nothing to reset.
.
For a bit of a sample, see this post → Base Form data uneditable
.
The first sample Base file is without macros and displays a calculation on the form using a query.
1 Like
I haven’t even had a chance to look at your post with the macro code in it. And no, you’re not giving me the answer and helping me cheat, I’m graded on presentations, not my work.
I like the idea of the related record though, that is what I’m ultimately trying to do. So I create a related table with the date of the classes, does that store all the dates in one table? Do I need to make a new column for each date of completed class?
I saw your samples and like the idea of using a query instead of a macro, I’m uploading a sample of my project.
Sample Dojo DB.odb (137.6 KB)
Isn’t a presentation a result of your work?
Well it took two days for some of the response but you never stated (and importantly):
which is why answering your question is so difficult. Have little idea (just my educated guess so far) as to what you are attempting.
Can see at a quick glance of your sample fields in the master which do not belong such as # Classes Since Promotion and Total # of Clases. BTW - avoid special characters & spaces in naming.
Yes (except a proper link is missing) but I would add more information such as name of class and if relevant to position. Again, do not know what your task is supposed to be.
Also do not see date of last promotion as I suggested in main table. How are you to know how many classes since last promotion since it is based upon a query?
Also noted that the related table need a link field to main table else how are you to knw what records belong to each master record.
Have a look through this information → https://help.libreoffice.org/latest/en-US/text/shared/02/01170203.html?DbPAR=SHARED#bm_id3150040
Edit:
Since this is going extremely slow, one response per day and multiple days for answers to questions, here is a sample. If you don’t get this, your need to review the LO Base documentation.
Sample Dojo DB.odb (148.9 KB)
There is a new form PEOPLE_SEEN. It contains a form and sub form to display people and when seen (respective form/subform). There are two additional sub forms to display totals based upon a query each. This should be the basis for your project? Maybe? Don’t know what it is.
Me too. No idea what needs to be calculated. The sum of all dates for each contact + something else?
@Ratslinger @Villeroy Ok, I didn’t outline it well enough. I am trying to build a database for a friend of mine who runs a Dojo. I built it well enough that it is being used right now, the biggest thing he would like to see is that the NumClassesSincePromo(I updated my naming schema) updates the TotalNumClasses when you update it.
I like Ratslinger’s idea of putting the date of last promo and then having a query field simply count how many classes since that promotion. In that case you would only have to tick up the TotalNumClasses field and the query would do the math every time you select the record.
I saw Ratslinger’s past example’s of calculating the data in the query and then having it shown in the form. That is what I’m going to try to implement now, but I’m not sure how to do it. I need to make this as “point-and-click” as possible when I’m done with it. Not sure if I need to input a date each day?
Sample Dojo DB.odb (137.5 KB)
As a rule of thumb, calculatable values should not be stored anywhere. They can be queried and calculated at any time from the stored data. Things become very complicated when the calculation gives x whereas the stored value is y. No, you certainly don’t need to input a date each day unless you want to store a diary.
“Sample Dojo DB.odb” by Ratslinger is as clear and simple as possible. Just open the form for editing, get the FORM NAVIGATOR and inspect the form with its 3 subforms.
This would be needed if the (as one of the many confusing options here) number of classes since last promotion (maybe Rank now) is actually wanted.
We enter a date for each event but we don’t enter 365 days per year including holydays as seen in cross table spreadsheets.
was opening question and now:
This is not being forthright. Documentation is helpful. Good luck with the presentation.
Yeah I agree with that. OP’s comments are not always clear to me.
First off, you are in no position to judge me on what I’m doing. I started this project as a personal favor and now I am working it into a CPS class at college. Professors know that students don’t know everything and will go to people for help.
You’ve been a condescending jerk to me on almost every post I’ve made about this. I came here because I thought this was a forum where you can ask questions on how to do specific things on LibreOffice, and while you honestly have tried to help me, you have sneered on every post made.
I explained to you exactly what I was trying to do, and you were being an impatient, know-it-all biggot on all of your posts, while claiming you had no idea what the original task was even after I explain it several times. I’m sorry I haven’t memorized the documentation yet, maybe if I do, I could be as all knowing as you. You have now turned me off to this whole forum. I won’t be asking for help here again. Have a nice life.
My apologies for making you feel that way.
.
You can keep posting. I will not respond but maybe others will help.