We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2021-02-11 13:09:27 +0200

Przemo gravatar image

updated 2021-02-12 11:13:11 +0200

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2021-02-11 18:32:40 +0200

Ratslinger gravatar image


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.

edit flag offensive delete link more


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

Przemo gravatar imagePrzemo ( 2021-02-12 11:14:50 +0200 )edit

answered 2021-02-12 12:59:15 +0200

Przemo gravatar image

updated 2021-02-15 12:01:39 +0200

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
end sub

function get_last_doc_no()
    if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
    statement = Thisdatabasedocument.CurrentController.ActiveConnection.createStatement()
    result = statement.executeQuery("SELECT MAX(invoice_number) FROM invoices")
    get_last_doc_no =  result.getInt(1)
end function
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-02-11 13:09:27 +0200

Seen: 52 times

Last updated: Feb 15