How to set dynamic default value in numeric field in form?

[Przemo]

How can I set a default value in a numeric field in a form based on what’s already in the database? E.g. last entered document number was 12, so I want to have 13 as the default value in a numeric field when creating new record.

Another version of the same question: can I use SQL query to set a default value of a numeric field?

The only way I could do it is with a macro, but is there a simpler way?

The full story:
I’m designing a small document handling database. Documents for a given year have consecutive numbers (2021/1, 2021/2 etc). When creating a new record I want to set the current document number based on the largest number found in the database for the given year, so if the database has 2 documents for 2021 I want to have 3 as the default value for the document number. I use numeric fields for year and document numbers.

P.S. The question is about Base

Edit:
I use the HSQLDB embedded

[Ratslinger]

Hello,

Using HSQLDB embedded (you did not state database used), you can get the next value using SQL:

Select MAX("document number") +1 from "YOUR_TABLE" Where "documentYEAR" = XXXX

Now that will get the next value of the document for the wanted year. The problem remains that the number must go into the field for the record. For that you need to use a macro. You need to pass information from one place to a different place.

Based upon your requirements, cannot picture getting information into a field based upon other information in the table in a simple fashion.

[Przemo]

OK, thanks for the confirmation. I think I have a working code. I’ll post it after polishing.

[Przemo]

The code assigned to the field’s “When receiving focus” is set_default_nf_value. I don’t use year here to show the solution without too complicated code (getting year from another field).

sub set_default_nf_value()
    o_form = ThisComponent.Drawpage.forms.getByName("form_invoice")
  	o_field = o_form.getByName("nf_invoice_number")
    nf_invoice_number = o_form.GetByName("nf_invoice_number").getCurrentValue()
	if nf_invoice_number = 0 then
           'if the current doc number is zero set it to the largest doc number + 1'
	    o_field.Value = get_last_doc_no() + 1
	    o_field.commit()
	endif
end sub

function get_last_doc_no()
    if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
            Thisdatabasedocument.CurrentController.connect
    endif
	statement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
    result = statement.executeQuery("SELECT MAX(invoice_number) FROM invoices")
    result.next()
    get_last_doc_no =  result.getInt(1)
end function