Questions about poor sorting

English
#20

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.

#21

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.

#22

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.

#23

@Renel

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.

#24

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…)

#25

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.

#26

Wanderer,
.
Thank you for this very relevant information concerning the functioning of “ask”-site.

#27

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.
.

RelationTables
RelationTables1044×668 85.8 KB

.
.
Thank you again for everything and see you next time (surely)!

#28

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.

#29

Do you have any good suggestions for me for learning API and Python.

#30

This post should help start. It has a link in my comments about Python.
.

.
Edit:
.
Here is another which I don’t believe was in that post → LibreOffice Developer’s Guide

#31

Forget all this macro stuff. It is pointless and frustrating. Learn SQL and how to use the form navigator.

Sorry, I forgot to sort your films. Have a look at this:
OpenForm From TableControl List v4.odb (25.2 KB)
You open a form, navigate from actor to actor, see all films of this actor sorted in ascending order by name and you can easily edit the movie details (only name so far).
I removed the actor ID from the form (not from the record set). The user should not be confronted with this mere record number. It serves technical purposes only.
The subform with the table control showing the listboxes of movies belonging to the current actors is linked to a query which sorts the movie IDs of the mapping table by the movie names.

1 Like
#32

Thanks Villeroy,
.
For the subform that contains the control table, using a query rather than an SQL command as the content type, is that a more adequate and logical way to work or it doesn’t matter?

#33

SELECT * FROM “TableX” is identical with “TableX” itslef. All columns, all rows in same order of columns and rows.

I used a query to enforce the sort order you want. A query is an SQL SELECT statement. This is a bit like the formula in a spreadsheet. However, contrary to a spreadsheet formula, a query returns a whole record set (rows and columns) whereas most spreadsheet formulas returns single values. Queries can return calculated results for each row or for entire columns without dragging around any formulas.
SELECT “Amount”, “Amount” * 0.2 AS “VAT”
FROM “Payments”
returns all amounts and 20% VAT for each “Amount”.

SELECT AVG(“Amount”) AS “Average”,
SUM(“Amount”) AS “Sum Amounts”,
SUM(“Amount” * 0.2) AS “Sum VAT”
FROM “Payments”
returns the average of all amounts under colunm name “Average”, the sum of all amounts under colunm name “Sum Amounts” and the sum of VATs under column name “Sum VAT”.

#34

So, a query offers much more flexibility and it is also the best option when needs and situations require it.
.
Thanks Villeroy, that’s really cool! I will go deeper into the SQL language.

#35

The problem here is determining when this occurs and what you are looking to accomplish. Experience will come into play.
.
Using one of the noted SQL statements, here it is used in the Query and the SQL Command of the form:
.

Screenshot at 2022-07-15 13-13-06
Screenshot at 2022-07-15 13-13-06457×661 65.8 KB

.
Each works the same in this case. Of course these are non editable fields since they are calculated.

#36

Ratslinger,
.
I experimented with creating a query that gives me access to the fields of a junction table and this just in order to be able to take a quick look at certain links. I also noticed that I couldn’t edit them. However, is it possible to determine or fix the width of the columns or fields that appear on the screen? Let’s say that I find it annoying to have to adjust the width of each of the columns each time I use this query.

#37

@Renel
This is going off topic. Original question answered and this should be a new question. There is an open Bug report dealing with this.

#38

I don’t really understand. Do I have to submit a new thread?

#39

@Renel
This is a question and answer site (not a forum).
.
In this case the original question was (although incorrect at first) about the sequence of a table grid. That was answered. Now you are asking about Queries. Different topic and needs to be a new subject. The problem is that items like this get buried and others will have difficulty finding this.
.
I did mention this was a Bug report but I cannot find it right now. I believe it originally had to do with table views, which was to be fixed but the query was not.
.
Edit:
This may be what I was recalling: Field properties changes - #14 by RobertG
.
End Edit
.

You should also read these if you have not already:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.