If you do not do it manually then a macro is the only other option.
.
I seem to recall that @Villeroy has already given you a macro it appears can be used here. Try that.
.
Using macros and not knowing how to construct them will only lead to many other problems.
I wouldn’t be able to build a macro for this type of command. I imagine this must involve UNO services and for me understanding all of this is still very basic.
I can adapt a macro to my needs if it is not too complex and comment well.
Well, thank you for taking your time to answer me. Eventually there will be a solution. In life, there are only solutions, problems are temporary.
I don’t get it. It is just refreshing the form after entering a record.
.
Not certain you need to “build” anything. If you cannot adopt the routine given you already (seems very simple to me), will look at this probably tomorrow. It is nothing more that refreshing a form after a new (at least that is what you have stated) record is entered. I am certain there is one or more posts here that accomplish that.
.
Edit:
After a bit more consideration, the macro is not a way to go. There is a button on the table grid for Refresh.
.
The problem is the list box. What you see is not what you have. You see the wording but in the record is actually a record ID which if sorted will not make sense (maybe what you have?). Have used a Query in the past to get around this. It is posted somewhere here but will look for it when I have some available time tomorrow.
As always, the answer is proper SQL. Since we do not even know which database you are using, I assume embedded HSQL.
Having a “Data” table with foreign key “PID” linking to “Persons”.“ID” you can sort the PIDs by the persons’ names.
SELECT "Data".*
FROM "Data"
ORDER BY(
SELECT "N"
FROM "Persons"
WHERE "Persons"."ID"="Data"."PID"
) ASC
You can test the above statement with this database of mine: https://ask.libreoffice.org/uploads/short-url/8nC9OrU7HOQpQmQHg1eZLpkq415.odb
1 Like
Hello Villeroy,
I am using LibreOffice 7.3.2.2 x64 suite. I don’t have to connect to a server or anything. I simply created a DB from this suite. I have no idea what kind of SQL language is used for this suite in Base. Also, I had never programmed a database before the beginning of the summer. The idea took me to do this without really knowing how it worked. Honestly, I find it exciting and I want to refine this DB which is only useful for myself.
I send the DB example (OpenForm From TableControl List) which demonstrates very well what I would like to do. In the form “Actors” in the first record, that of “Al Pacino” we can see a list of films where he played. In the list box, the movies are sorted alphabetically. However, in the control table, the rows which are in fact a sequence of list boxes are not sorted. I would like these to be sorted despite the additions that may be made later.
OpenForm From TableControl List v2.odb (26.3 KB)
Currently, I’m wondering if this was the right way to do it in order to have a recording of an actor where we can see a list of movies associated with him. However, he still needs to have a tool to select a film that comes to him from another table. Here is where I am.
It is absolutely necessary to know the actual database software that is used with Base. LibreOffice can generate 2 types of embedded databases. Both embedded databases are “installed” temporarily from the document when you access the database. The database software you are dealing with is always indicated in the status bar of your document window.
However, the vast majority of database documents is connected to some spreadsheet because this is what everybody uses as a (rather problematic) source for mail merge:
Most likely you are using embedded HSQL which is documented on Chapter 9. SQL Syntax
This 16 year old version 1.8 of HSQL is fairly simple and complying with SQL standards. Although tailored around this particular HSQL 1.8, Base has not implemented all the features that this database offers. Apart from that, certain things are way easier and faster do do with SQL statements.
One example: Base can set the default value of some field to constant values. For instance, you may set 2022-07-01 as default value for a date field when the date is missing in a new record. However, Base can not set today’s date as a “dynamic default value” nor the current time nor the current date-time as timestamp.
Solution: call menu:Tools>SQL…
ALTER TABLE "T" ALTER COLUMN "MyTimeStamp" SET DEFAULT CURRENT_TIMSTAMP
Always call menuView>Refresh tables after modifying the database with the SQL dialog.
From now on every new record in table T gets the current timestamp in column “MyTimeStamp” unless some value has been given.
OpenForm From TableControl List v3.odb (25.6 KB)
I modified your many-to-many form and removed all macro trash. First things first.
Villeroy,
Here is a 2B version of my example. This one contains text corresponding to the thoughts I currently have regarding this impossibility of sorting a control table.
OpenForm From TableControlList v2-B.odb (28.2 KB)
@Renel
Had not posted further since @Villeroy had given you the SQL for the sequencing. Obviously you have not adapted it.
.
Using that code for a basis, created this SQL and used it on your SelectedFilms sub form:
SELECT "jtblActorsFilms".*, "FilmName"
FROM "jtblActorsFilms"
Left Join (SELECT "FilmName", "IDFilm" FROM "tblFilms")a
on "a"."IDFilm"="jtblActorsFilms"."jIDFilms"
ORDER BY "FilmName"
.
resulting in the form looking as:
Can add/delete/change records and just press the refresh button to get the correct sequence:
1 Like
I am really sorry. I don’t think I understood at all what Villeroy was proposing to me. I owe you both a lot of gratitude. As I mentioned before, this is my first database. I have a lot of shortcomings, but I like to learn. However, you never learn alone, mutual aid is always a guarantee of success.
Thanks to you two!
@Renel
Just out of curiosity, on my posts you mark your comment as the solution but on other posts you mark the one giving the actual solution. Why?
.
Also, if you don’t understand what is proposed, ask. Also you would benefit a lot from going through the Base documentation.
Hmm! I saw at the bottom of the answer that I sent you the Solution with a checkbox. I just checked. But I understand that I should have checked your answer. I’ve made the same mistake on other occasions.
.
You are right, I should have asked to have an answer to my misunderstanding. Regarding the documentation, I started to read, but there is so much to learn: SQL, relations, understanding the tools available, Basic and macros, UNO services, API… I embarked on this adventure at the beginning of the summer, so I think I’ll have a few months to sort it all out. Despite everything, I want a DB which is functional. That’s not bad.
.
Time, patience and rigor.
Just curious. I don’t get anything. It does help others seen what answered the question.
.
You can also upvote and click on hearts for responses you may like by any or all - doesn’t even need to be one of your questions.
Actually, I’m not familiar with how the forum works. I’m more used to writing a thank-you and acknowledgement. I was not ill-intentioned, nor had the will to take credit for myself. I believed that clicking on the word Solution was enough to let others know that there was a solution available to a problem. But that doesn’t seem to be the case.
.
Please let me know if it happens again.
A Thank You is more than from some. Was only concerned as to the inconsistent method you were marking something as an answer. Never even considered you were taking credit for resolving the issue (have had a number of others who did). Again, I get nothing whether my answer is marked or someone else. Just would like what answered the questioned marked.
.
Just marking that a solution was had may be OK but if there are ten different points from people, then which solved your problem would be clear to others.
.
I’ve had hundreds of no responses at all - nothing. Never even know if you were of any help.
Look at this thread from top: The answer you marked is shown below your question, so a new reader doesn’t need to read the complete thread. You also can yump to this answer.
.
At the beginning of an answer there is a number below an shortened arrow-up. Maybe more then one solution is presented. The you/we can upvote the better solution by pressing the arrow More popular solutions are presented first.
.
As the upvoting implies the answers are sorted by vote you may see, why we use comments: Comments stay in order of the comunication, while answers may be re-sorted. So commenting with answers can lead to unreadable threads, because the sequence is broken.
.
So this is an “ask”-site like stackoverflow, not a forum. (Or it is thought to be…)
Instead of opening another form document for editing a movie record, you could simply add a movie form to the existing form document. My sub-sub form lets you edit the selected movie in place. For beginners macros are a waste of time and major source of frustration. Be focussed on proper relation designe, SQL and in the frontend use arbitrary complex hierarchies of forms and subforms with (parameter) queries.
Wanderer,
.
Thank you for this very relevant information concerning the functioning of “ask”-site.
Hello Villeroy,
.
I didn’t understand the point of this feature, but now I understand that it’s a way to avoid traveling from one form to another. That’s how I see it.
.
What I really didn’t understand was the solution you offered to sort the rows of the control table using an SQL command. Ratslinger insisted that you already gave me the answer to my problem. That’s why I’m grateful to you both. Now everything works.
.
It’s true that I’m a beginner, this is my first DB. I read a lot, watched a lot of videos on YouTube, did a lot of research on the Internet to guide me in the design of my DB. I came to this site through this research.
.
Currently, I have almost finished reading LibreOffice Base Guide 6.4. I am also doing this free course (https://sql.sh/) to learn how the SQL language works. I also want to deepen and understand Basic (Access2Base). I think I need several months to manage to get by with all this.
.
Here is my DB and existing relationships. The tables could be improved over time by adding information fields. Maybe new tables could be added. However, I have not yet reached the level of querying my database using a query. When I have a better understanding of SQL, this will surely happen.
.
.
.
Thank you again for everything and see you next time (surely)!
In my view, this is only a helper for those transferring from M$ to LO. It is further overhead. I started that way years ago and dropped it quickly in favor of learning the API (still working at it). Then for a language Python - much better than basic.