Libreoffice Base ID auto increment problem printing reports

[Chris-H]

Hi,
I am using Libreoffice base 6.3.5.2 (x64) on Windows 10 and have setup a small base with the idea to create invoices with a customer table and items table etc… I have set the auto value for the invoice numbers starting at 0. Then I have created a form to write the invoice and a report for print which involves queries, everything works fine untill I decided to change the auto increment (1,2,3 etc…) starting at say 300, I have achieved that by using the following SQL code:
alter table “MyTableName” alter column “ID” restart with 300
This worked and set the number to 300
the problem I am facing is that the report (Invoice) with ID 300 will not print anything of my form’s input meaning everything else but what’s in the form (no items, amounts, invoice ID etc…).
I have been looking on various solutions but cannot find the reason for that. If somboey would have a hint or idea of where I should look at I would be thankful.

Edit: in fact it doesn’t even print number 300, it rather prints an earlier form with ID nr 3

A wonderful day to you all.

[Ratslinger]

Hello,

Have not seen with any DB (you do not specify yours) that just changing an auto increment field to start with a different value would cause this problem. It could be that this value is checked somewhere for some reason (query maybe?) and that could be the cause.

With the information presented there is little else to be done. It would be best if you would post a scrubbed sample with this problem.

[Chris-H]

Thank you so much for your speedy answer Ratslinger, I will check on my queries as per your suggestion and I will get back. Again, thanks much!

[Chris-H]

Dear Ratslinger, many thanks, you pointed into the right direction, it had to do with the queries. Some other problems arose but it looks that it is all tied to the queries being misformulated.

[romu1]

I have the same problem, except that my auto value actually starts at zero.

I’m using this version Libreoffice base 7.4.3.2(x64) on Windows 11

I used the invoicing tamplate available in Base.

As a new user I can’t only add one upload, so please see attached document for pictures.
AutovaluesNotShowingInReport.pdf (279.1 KB)

Final document. I should be able to see a fat round zero…but nothing. The other values show perfectly,

I ran another report with CustomerID and TaskID auto value and its the same problem. it seems it’s only autovalues that don’t show.

Besides, it looks like the autonumber is not counting. These below are supposed to be invoices no. 0 and no. 1, for the same customer. But instead of doing two different documents, it puts everything in the same one.

I can’t find the solution and I’m totally new to DBases and have no clue about SQL.

Chris, how did you fixed the problem in the queries ?

Help is greatly appreciated! Thanks!

[Ratslinger]

@romu1
Hello,
Expecting someone else’s solution of a query will typically not be an answer to your situation as there are many different ways a query can be constructed. The original question had the problem originate when the auto increment starting value was modified. This is not your case.
.
As you upload only shows a bit of your query, please upload the entire SQL (edit in SQL mode to obtain) for a start.
.
Edit:
Decided to try and duplicate your Base file (used HSQLDB embedded). Could duplicate your 0 not printing. This is a bug (have not seen one open on this). Should report it here → Bugzilla.
.
There is a work around. In the report, change the Formatting of the fields (CustomerID; InvoiceID) to Text:

.
Shows:

.
As for you statement about auto numbering not working, well for one, you could not enter the same ID for two records without getting an error. There must be something in your SQL or report design.
.
Would need your SQL (as asked above) or a sample Base file - redacted.
.
Edit 2:
BTW, SQL used for report shown:

SELECT "Invoices"."InvoiceID",
       "Invoices"."InvoiceDate",
       "Invoices"."Status",
       "Customers"."CustomerID",
       "Customers"."CompanyName",
       "Customers"."Address",
       "Customers"."City"
FROM "Invoices",
     "Customers"
WHERE "Invoices"."CustomerID" = "Customers"."CustomerID"

or the GUI version:

[romu1]

Thank you so much for taking the time to look into my problem.

I’ve just filed the bug as you suggested.

I’ll try to extract the SQL query but in a few days.

I don’t know, I feel discouraged. This is the first data base I make, I followed the tutorials and it was supposed to be easy and straightforward.

I like LibreOffice a lot and I was hoping it to replace MS Office for my basic needs so I don’t have to buy either a new license or a subscription, but if I can’t create working data bases, I might need the subscription…

Sorry to bother you with my concerns. I really appreciate your help.

[Ratslinger]

@romu1
Since this is a tutorial you are working with, you can just post the entire Base file instead of just the SQL as asked for. After 24hrs on the site you will be able to attach more.
.
Please see → This is the guide - How to use the Ask site? for site info and adding of files.

[RobertG]

@Ratslinger : Formatting to ‘0’ instead of default formatting of the field will also solve the problem. ‘0’ won’t be shown by default in a numeric formatted field in the report.

[Ratslinger]

@RobertG

Formatting to ‘0’ instead

Thank you. Had not known that, although don’t fully understand nor accept why. Bottom line is it works.

[RobertG]

Writte a bug report for this: Bug 152344. Shouldn’t be difficult to find #,## and set 0,## as standard, but I’m not a developer …