I want to print one of my variables (integer) with the format “000” (eg: 005, 032, 107…). How can I do that?
Edited by @Ratslinger to clarify question with what was stated in a comment by OP:
My goal is to have an invoice serial that follows the structure 000/YY, and being able to sort the invoices in chronological order on Base. So, if I’m creating the third invoice of the year, the number would be 003/22.
End edit
Hello,
Result (ID is set):
I feel stupid, I forgot to add that I’m using a function, so I can have text and numbers at the same spot, so it’s formatted as text. I changed it to the user-defined 000 format but didn’t change anything.
In this case your (user-defined?)-function will have to convert the numbers to formatted text.
Can you show the used function?
I see, sure: it’s "Invoice nº "&[IDInvoice]&"/22" but the more I try to think about this, the more problems I see, so I think I probably just didn’t create it in the best way from the beginning.
My goal is to have an invoice serial that follows the structure 000/YY, and being able to sort the invoices in chronological order on Base. So, if I’m creating the third invoice of the year, the number would be 003/22. Sorry, I’m very new to Base.
SELECT RIGHT('000' || "ID",3) || '/' || Right(Year("D"),2) AS "Invoice Number" FROM "Data"
but that would not restart the numbering with every new year.
Next try:
Download https://ask.libreoffice.org/uploads/short-url/nW2PUGMTAXtJcBy67iiy8RvNj8m.odb and add the following query:
SELECT "D1"."ID", "D1"."D", COUNT( "D1".* ) AS "C"
FROM "Data" "D1"
JOIN "Data" "D2" ON "D1"."ID" >= "D2"."ID" AND YEAR( "D1"."D" ) = YEAR( "D2"."D" )
GROUP BY "D1"."ID", "D1"."D"
ORDER BY "C", "D1"."D"
The C column counts how many IDs are equal or greater within the same year. Notice that the dates in this table are random. The smallest ID in 2005 is in November, smallest ID in 2006 is in May etc.
P.S. Major problem: You must never delete any record because that would affect the subsequent year based IDs. You need to store the year based IDs in a separate column. This can be done with a form-subform approach where the main form calculates the new year based ID and the subform takes new records only inheriting the Y-ID from the parent.
See [Example] Invoice Forms (without macros) (View topic) • Apache OpenOffice Community Forum
@Moshpirit,
you don’t give any details of database used, field names etc. but the answer definitely lies in SQL.
i attach an example with 1 table, 2 queries and 1 report, coded using the default HSQLDB embedded.
Query1 calculates a unique row number by “Date” ascending. duplicate dates are split using the value of “ID”.
I created a view from Query1 called “vQuery1”.
Query2 takes “vQuery1” as its data source.
it calculates the position of each record within a group, a group being defined as YEAR(“Date”).
the field “IDinvoice” is also defined here.
i created a simple report from Query2 using the report wizard, take note that Analyse SQL command = NO.
Invoice.odb (10.2 KB)
You’re right, I didn’t share any more info and that’s a lot of stuff, I’ll share a safe copy of my ODB file so we can talk using the same terms, is it alright?. I use Firebird instead of HSQLDB, btw but only because it’s the default option.
Invoices.odb (58.3 KB)
All I cannot change is in Spanish (the main names are: Factura = Invoice; Cliente = Customer; Tareas = Tasks, feel free to ask anything)
used libreoffice 6.4.4.2 (x64), embedded Firebird, windows 10 (x64).
I do not use nor rate Firebird so sometimes have to consult the documentation.
an accomplished Firebird user may have done things differently.
Firebird 3.x does have a ROW_NUMBER() function which would have helped but I use an old version of libreoffice and it was not available to me.
each record in an invoice database should contain a “STATUS” field (issued, paid, cancelled etc.), this is something you must do.
records other than the very last record entered should never be deleted.
the reference number “IDinvoice” has been created according to your instructions.
I also made a report because I know that you will have issues with the sorting and grouping.
If you are not happy with the presentation then play with a copy until you are.
The data-source for the report is the view “vReportQuery”. The view is required to enable sorting and grouping.
I attach the database you uploaded with the addition of 3 queries, 2 views and 1 report.
hope it’s O.K.
EDIT: 2022-01-30
ATTACHMENT REMOVED
please see below
@Moshpirit
I discovered that I am using Firebird 3.0 which prompted me to take a closer look at the documentation.
ROW_NUMBER is in fact a WINDOW function which makes the coding much simpler.
I have deleted the attachment from my previous comment, if you have already downloaded it then please delete it.
The attachment below “UniqueReference2” is your database plus 2 queries, 1 view and 1 report.
This and the previous attachment are identical appearance wise other than the formatting of Total Cost in the report.
The big difference is in the simplicity of the coding due exclusively to the modern database window function ROW_NUMBER.
To be honest if I had known that you use Firebird then I would never have posted a reply in the first place but having done so I feel the need for successful completion.
UniqueReference2.odb (67.3 KB)
Hello again, after some crazy days I have some time to come back to this issue again. The file that you shared with me really helped me. I tried replicating it on my original file, but I couldn’t, so I just modified yours by using the real data instead, although it’s not exactly what I wanted since it uses “-” instead of “/” to separate the ID from the year. I tried editing myself by going to the query “IDinvoice” and changing the line
right('00' || cast(f.grp_pos as varchar(10)), 3) || '-' || right(cast(f.yr as varchar(4)), 2) "IDinvoice"
to
right('00' || cast(f.grp_pos as varchar(10)), 3) || '/' || right(cast(f.yr as varchar(4)), 2) "IDinvoice"
(the only difference is the || '-' || (now|| '/' ||), but it doesn’t seem to have any impact on the reports, which is driving me crazy tbh haha.
But beyond all of this, I’d like to thank you all your huge effort 
hello Moshpirit,
.
i replaced the backslash with a hyphen for reason of clarity i.e. when viewing the report the backslash may be mistaken as 1, this will depend on the font used.
the data-source for the report is the VIEW “vReportQuery”, it’s very easy to update the VIEW just follow these steps:
.
right click your amended query “ReportQuery” and select Edit in SQL View.
.
hit ENTER
paste this code on the very first (blank) line
ALTER VIEW "vReportQuery" as
copy everything and close the window. DO NOT SAVE
.
paste into menu:Tools>SQL and hit Execute
.
close the window and execute your report.
Hello,
Based upon:
I took a slightly different approach avoiding report builder at this point as it has no bearing.
Although not a macro it is a bit similar - a Trigger. A trigger is a macro of sort within your database which executes upon an occurrence such as inserting, updating or deleting a record. It can execute before or after the occurrence. Now I have dabbled in this a bit before but certainly not proficient in it. But this is small and seems to fit what you want.
With the sample attached, there is a new form - Invoice_new based upon a table of the same name. It is similar to your invoice table. When a new record is created, the Trigger invoice_yr is executed. The invoice date is searched for the last two digits of the year and that is used to find the highest sequence number already used in that year. If not found 0 is used, otherwise one in added to the number and that is used. For the test kept these in two separate non key fields.
With this setup you can enter or delete invoices in any year.
Because of that, if you were to edit an invoice date, the numbers cannot be modified. It is better to delete the old and enter a new item.
To stop the update from occurring, a second Trigger was created - ck_date. this checks the date before entry and after to see if there was a change and if there was, an EXCEPTION is called ( named INVALID_DATE_CHG).
The three items are in the Query section only for your perusal. They actually reside in the Firebird database installed through menu item Tools->SQL..
Take a look and if further information is needed please ask.
Sample ----
Invoices.odb (68.6 KB)
After making entries on the form, you can examine the results in the actual table.
@Ratslinger,
hard coding is not an option unless you can guarantee that data is input in chronological order!
the sequence is not maintained if the input date is less than max(date) for client_id.
so the OPs desired formula cannot be permanently assigned to any specific invoice whatever method is used.
the formula must be a calculated value in order to maintain the sequence and used solely by the user for reference.
@Moshpirit,
all you have to do is create a view from the tiny query ‘IDinvoice’, name that view “vIDinvoice” and use it whenever you wish to view your formula.
e.g. using your own query which strangely enough is called ‘Query’ just paste this code into the Create Query in SQL View window and execute.
in most cases it would be wise to embed the code of ‘IDinvoice’ within the calling query, as shown in my query ‘ReportQuery’ and use direct SQL mode.
if you want a unique reference code which can be attached to customer invoices then use a formula which is similar to that used by banks, industry etc.
select q.*, v."IDinvoice"
from "Query" q
join "vIDinvoice" v on q."IDFactura" = v."IDFactura"
At this point, as stated by OP not being certain of direction, it is simply presented as an option. Don’t know if OP has determined what is actually wanted/needed so presented another option.
.
Could even prove somewhat useful - date of invoice vs sequence entered. All kinds of what ifs. What if a invoice wasn’t entered when it was supposed to? or with an incorrect invoice date (human error). So many unanswered questions.
.
Again, just another option so OP (self stated as very new to Base) can see what may be available.