Combine Multiple If Statements as Single String? 1

[supportsvc]

Not sure if I'm asking that correctly?

There are several fields for a section and the user would like to see the fields combined when they are checked and display the field name(s).

So I started this but it's not fully working

If {RA_ReceiptsHeader.UDF_COMPLETE_SET_OF_ATTACHMENT} = "Y" then "Complete Set of Attachments" & ",  " &
If {RA_ReceiptsHeader.UDF_CASE} = "Y" then "Case" & ",  " &
If {RA_ReceiptsHeader.UDF_HOOK} = "Y" then "Hook" & ",  " &
If {RA_ReceiptsHeader.UDF_FLAT} = "Y" then "Flat" & ",  " &
If {RA_ReceiptsHeader.UDF_CHISEL} = "Y" then "Chisel" & ", " &
If {RA_ReceiptsHeader.UDF_VGROOVE} = "Y" then "V-Groove" & ",  " &
If {RA_ReceiptsHeader.UDF_EXTENSION_ROD} = "Y" then "Extension Rod" & ",  " &
If {RA_ReceiptsHeader.UDF_CHUCK_KEY} = "Y" then "Chuck Key" & ",  " &
If {RA_ReceiptsHeader.UDF_USB_CABLE} = "Y" then "USB Cable" & ",  " &
If {RA_ReceiptsHeader.UDF_110V} = "Y" then "110V" & ",  " &
If {RA_ReceiptsHeader.UDF_EUR} = "Y" then "EUR" & ",  " &
If {RA_ReceiptsHeader.UDF_UK} = "Y" then "UK" & ",  " &
If {RA_ReceiptsHeader.UDF_PROTECTIVE_TUBING} = "Y" then "Protective Tubing" else "Non-selected"

Example, if UDF_CASE, UDF_FLAT, UDF_110V and UDF_UK were marked in the order only those should show like this:
Case, Flat, 110V and UK

Hope this makes sense?

 

[pmax9999]

Are all those fields always a "Y" or an "N"? Can the fields be null?

On the face of it, the formula should work, unless it encounters a NULL value, in which case it will fail.

You state its "not fully working", so my guess is that it works where all fields are not NULL, and the times it doesn't work are where the formula encounters a null. In this case you can either test for NULLs or change the setting at the top of the formula workshop screen to "Default Values for Nulls" rather than "Exceptions for nulls"

If nulls are not the issue let us know and provide some examples (including sample data) where it does not return the expected result.

Hope this helps.



Cheers, Pete

 

[supportsvc]

Thank you Pete
Don't think there would be Nulls since it's a checkbox field
but I did change it to Default Values for Nulls, however it's doing the same thing.

It shows it on some orders but then not on other orders and some orders not all are showing that are checked Y

I don't get it?

 

[pmax9999]

So the next stage of problem solving is to put all the relevant fields on the report so you can see the exact circumstances where the formula fails.

Report back with actual sample data and the formula result if you need further assistance.

Regards, Pete

 

[supportsvc]

On this order the left top box shows Y for Complete Set of Attachment, Case and USB Cable. To the top right is me doing it the long way and creating a formula for each one then adding it to a text box so it combines them but the "," shows between all of them. To the bottom top right is the formula in this thread. It shows all but the USB Cable.

On this order Case and Hook is showing on the Bottom Right of the 1st box which again is the formula listed in this thread.

On this order the Ext Rod and USB Cable aren't showing again with this formula.

On this order Case isn't showing.

The bottom 2 boxes I need to do the same to show all the Y ones combined as one string.

 

[pmax9999]

I still believe the problem is data related but without being able to see all of the relevant fields I'm struggling to understand your report in the current format. I'm guessing that it is not the field that is being omitted that is the problem, but rather one of the (earlier in your formula) fields that does NOT contain a "Y".

So I am looking at something that makes sense to me I'd recommend you create a new report that simply has all 13 fields in the details section (with field names in the page header) plus your formula, then post a screen shot that includes the output where the formula returns what you expect, and where it doesn't.

Even more useful would be posting a copy of that report with saved data so that if necessary I can differentiate between null fields and empty strings.


Cheers, Pete.

 

[pmax9999]

Actually, I've just been testing this and the Default Values for Nulls doesn't help in this scenario (I never use this approach personally as I'd rather explicitly test for NULLS so I know that I understand the data I'm working with).

You will need to add a test for nulls for every field. My formula is as follows (note I only extended it for the first 5 fields in your formula, so you will need to expand it out to cover all 13 fields:

(
    If      IsNull({RA_ReceiptsHeader.UDF_COMPLETE_SET})
    Then    ''
    Else
    If       {RA_ReceiptsHeader.UDF_COMPLETE_SET} = "Y" 
    Then    "Complete Set of Attachments, " 
    Else    ''
)
&
(
    If      IsNull({RA_ReceiptsHeader.UDF_CASE}) 
    Then    ''
    Else    
    If      {RA_ReceiptsHeader.UDF_CASE} = "Y"  
    Then    "Case, " 
    Else    ''
)
&
(
    If      IsNull({RA_ReceiptsHeader.UDF_HOOK})
    Then    ''
    Else    
    If      {RA_ReceiptsHeader.UDF_HOOK} = "Y" 
    Then    "Hook, " 
    Else    ''
)
&
(
    If      IsNull({RA_ReceiptsHeader.UDF_FLAT})
    Then    ''
    Else
    If      {RA_ReceiptsHeader.UDF_FLAT} = "Y" 
    Then    "Flat,  "
    Else    ''
)
&
(
    If      IsNull({RA_ReceiptsHeader.UDF_CHISEL})
    Then    ''
    Else
    If      {RA_ReceiptsHeader.UDF_CHISEL} = "Y" 
    Then    "Chisel, "
    Else    ''
)

If I'm right about null values in your data, this should fix your problem.

Cheers, Pete

 

[supportsvc]

That's it! Did not think there would be issues with blanks!

Thank you so much!