Home > Forum > Rules, JS, SQL > Isnull for Form Field type Floating Number

Isnull for Form Field type Floating Number

0

22.07.2021

21:05

Hi all,

I'm having a problem in Studio with a Form Validation rule using SQL.

I have an item list that is populated with lines if the user select a Sales Order in another field or manually filled if there is no information on the SO.

There is a column that is mandatory to be filled whenever the item list has any rows and I tried many examples to construct a validation form rule in order to have information on that column.

I was using a SQL query because:
- Column cannot be required (there are cases that the user is sending documents so the item list will be empty)
- Form rules using Condition By Row cannot be used inside the form validation which expects True/False results (only Business Rule but conditions for Item List are limited)

The problem with my SQL query is that, I need to use something like isnull(FORM FIELD, '') <> ''
but this only works for string fields and not for numeric (which is my case, floating number form field).

Does anyone know how can I evaluate if the value is there?

Thank you all for the help!

22.07.2021

22:30

Hi Andreia,

you have to cast/convert it's value to a string example:

select isnull(cast(WFD_AttDecimal1 as varchar(20)),'It''s null')
from WFElements

Output:
It's null
1.210000
1.210000

Nevertheless, I'm wondering why setting the column as required is not enough.
The check is only executed for an existing row. If there are no rows, no value is required, if there's a row the column will be required.
It get's a little 'trickier' to mark it as required if this depends on another column, but it works just fine too.
I'm not sure whether, there's a post about this in the knowledge base here, therefore I'm referencing my post about this:
https://daniels-notes.de/posts/2021/series-expert-guide-part-6#conditional-required-fields

Edit:
While writing this, I assumed that form field is referring to the database column. If you are using this in some places where you can directly access the field value you can simply write:

'Form field value's <> ''

Best regards,
Daniel

Solution (1)

Helpful (0)

Wrong answer (0)

28.07.2021

04:13

In reply to: Daniel Krüger (Cosmo Consult)

Hi Daniel,

Thank you for your answer!

Worked in both ways, I was worried that it could impact the item list itself because of the initialization that is done based on another 2 fields but it worked ok.

I'm saving the SQL query still, it might come handy for another case :)

Thank you for being so helpful!

Solution (0)

Helpful (0)

Wrong answer (0)