Base Form: How to make one input field mandatory if the value entered in another field is less than zero

[brightstar]

I have multiple fields in the form entry.
In one field, I can enter either a positive value or a negative value.
If I enter a negative value, the next field should become mandatory.

Can anyone please guide me on how to achieve this requirement? Thanks a lot in advance.

[Ratslinger]

Hello,
This would require a macro to check the value of your field on exiting that field for the negative value. If negative then set the InputRequired parameter to True:

  oObj2 = oObj1.getByName("YOUR_FIELD_NAME")
  bInputRequired = oObj2.InputRequired
  oObj2.InputRequired = True

.
You need to be aware that this was an old bug recently revived & newly implemented in LO v7.4.1.2
.
See tdf#150577

[ggk]

In 7.3.5.2 one still needs to have the table field set to not null for the InputRequired of the form to work.

[RobertG]

If you see the bug @Ratslinger has been linked to: LO 7.4.1.2 and also LO 7.3.6.2 will work well. Older version won’t do anything with set input required only in the form.

[brightstar]

Thanks for your response. Have now upgraded to 7.4.1.2. Would you please provide some detailed steps of how to accomplish this as this looks promising.

[Ratslinger]

@brightstar

Would you please provide some detailed steps of how to accomplish this as this looks promising.

With code it depends upon what you have set in your form, especially control names. Do you have any macro experience? Have you looked at the documentation for this?
.
If you want, I can attach a sample but you will need to translate to your Base file. Or, you can attach your Base file and I will modify for you. However, there concern then is if you are not experienced, how are you going to maintain in the future when modifications are needed?

[brightstar]

Thanks @Ratslinger for your quick response.
If you can please attach a sample, I will try to translate to the base file.

[Ratslinger]

@brightstar

If you can please attach a sample

Appears there may be a further problem to this fixed Bug. Upon entering an incorrect Value which sets the requirement, if you go to correct the requirement is still there.
.
Need to check further.
.
Also, you did not answer my questions from the comment.

[brightstar]

Thanks @Ratslinger - I will try to answer all your questions.

1. I have experience in MS Excel Macros
2. I have not looked at the Base Macro Documentation. If there is any, willing to go through it.
3. I am not sure how to attach MY base file without data for your help to fix it.

Thanks for your help and guidance

[Villeroy]

ALTER TABLE "tbl" ADD CONSTRAINT "MinusPlus" CHECK (("number"<0 AND NOT "next" IS NULL)OR "number">=0)

[Ratslinger]

@brightstar
.
Here is a sample with a different macro. Checks the value when record change is requested (Before record action event of form):

MandatoryFieldBasedUponValue.odb (13.7 KB)

For documentation see:

https://help.libreoffice.org/latest/en-US/text/shared/05/new_help.html?&DbPAR=WRITER&System=UNIX

and search for macros. Also see:

https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Handbook

And for posting a sample, there is a toolbar icon when posting. See:

This is the guide - How to use the Ask site?

This is intended to help you use this website. There will be additions to this website as we go along. Bring a positive spirit to your posts, and thank you. Attention, this is not a real question, but a description of how to use this Ask site. Introduction / Basics The community welcomes you. Before you ask your first question, please read the FAQ. Purpose of this site The site serves as a platform for sharing questions and answers. It allows LibreOffice community members to come togeth…

[Ratslinger]

@Villeroy
Your statement works in HSQLDB embedded only if you remove the quote marks from the constraint name. Otherwise it thinks it is a field name.

[Villeroy]

The following generates a valid database table with constraints. When you violate the constraint, you get the regular SQL error message. The upper-case name “B_NUMBER_MISSING_FOR_NEGATIVE_A” stands out, so the user gets a hint about the reason. [I gave up on SQL error handlers and triggers]

CREATE TABLE TBL(
    A INTEGER NOT NULL,
    B INTEGER NULL,
    ID INTEGER IDENTITY);
ALTER TABLE TBL ADD CONSTRAINT "B_NUMBER_MISSING_FOR_NEGATIVE_A" CHECK((A < 0 AND NOT B IS NULL)OR A >=0)

[Villeroy]

This is the database created by the above statements.
MinusConstraint.odb (3.2 KB)