0

if, then not working within a function field script.

The following is code in a field. I want to have the field contain one value if a dynamic dropdown 1 is not empty and another if it is. 

 

I store the record ID of records in table A in field BPID1 and  in table B in field SPID1

I have a dynamic dropdown then provides fills BPID1 and another that fills SPID1

 

If the first dynamic dropdown is empty, then I want to return records from the second dynamic dropdown.

This works if the first dynamic dropdown has a value.  ('Business Process 1')

BUt if that field is empty, the ELSE part never fires. 

 

let theIDb := BPID 1
let theIDs := SPID 1

if length(text('Business Process 1'))<1 then
    (select 'Business Process Defs' where Id = theIDb).'Process Category'
Else
    (select 'sales process defs' where Id = theIDs).'Sales Process Category'
End
 

3 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    One solution is to check for null instead of length of text. You can try something like: 

    if 'Business Process 1' != null then
    (select 'Business Process Defs' where Id = theIDb).'Process Category'
else
    (select 'sales process defs' where Id = theIDs).'Sales Process Category'
end

    Line 1: the != means NOT equal to.

    I hope this helps. Let us know how it goes.

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Add a temporary formula field with the following 

    length(text('Business Process 1'))

    Then you can monitor its value when you are expecting it to be <1

    • Ninox developper
    • Jacques_TUR
    • 2 yrs ago
    • Reported - view

     Hello John,

    This line returns a set of records :   (select 'sales process defs' where Id = theIDs).'Sales Process Category'

    If more than one record matches the search, the formula will contain the records but will not display anything.
    You can try to see if the formula is really empty by creating a temporary formula with this code:

    debugValueInfo(if 'Business Process 1' != null then
        (select 'Business Process Defs' where Id = theIDb).'Process Category'
    else
        (select 'sales process defs' where Id = theIDs).'Sales Process Category'
    end)

    If you only want return one record, then you have to add the "first" function :

    first(select 'sales process defs' where Id = theIDs).'Sales Process Category'

Login to reply

Content aside

  • 2 yrs agoLast active
  • 3Replies
  • 136Views
  • 4 Following