Not IsEmpty and Case function

[enquirerfm]

I'm trying to output a concatenated field which is made up of several fields. I don't want to output the decriptors of a field (its contents are zero) if under usual circumtances it would otehrwise return zero (or be blank). I've set up the following but there's something wrong with the formula:

Trim (

Case (not IsEmpty (Field 1); "F1 description" & " " & Field 1 & "%" & " | ") &

Case (not IsEmpty (Field 2); "F2 description" & " " & Field 2 & "%" & " | ") &

Case (not IsEmpty (Field 3); "F3 description" & Field 3 & "%" & " | ") &

Case (not IsEmpty (Field 4); "F4 description" & " " & Field 4 & "%" & " | ") &

Case (not IsEmpty (Field 5); "F5 description" & " " & Field 5 & "%" & " | ")&

Case (not IsEmpty (Field 6); "F6 description" & " " & Field 6 & "%" & " | "")&

Case (not IsEmpty (Field 7); "F7 description" & " " & Field 7 & "%" & " | ")&

Case (not IsEmpty (Field 8); "F8 description" & " " & Field 8 & "%" & " | ")&

Case (not IsEmpty (Field 9); "F9 description" & " " & Field 9 & "%")

)

What's wrong? Is this the best way to do this? Thx

[bcooney]

Try using List ( ) instead.

If you provide us with the true field names and content, I could be more specific. I'm questioning whether these fields should really be records in a child table.

[enquirerfm]

Try using List ( ) instead.

If you provide us with the true field names and content, I could be more specific. I'm questioning whether these fields should really be records in a child table.

This doesn't seem to work.

[bcooney]

Really, could you show me what you did?

[Raybaudi]

Try this one:

Let(

t = Case ( not IsEmpty ( Field 1 ) and Field 1 ; "F1 description " & Field 1 & "% | ") &

    Case ( not IsEmpty ( Field 2 ) and Field 2 ; "F2 description " & Field 2 & "% | ") &

    Case ( not IsEmpty ( Field 3 ) and Field 3 ; "F3 description " & Field 3 & "% | ") &

    Case ( not IsEmpty ( Field 4 ) and Field 4 ; "F4 description " & Field 4 & "% | ") &

    Case ( not IsEmpty ( Field 5 ) and Field 5 ; "F5 description " & Field 5 & "% | ") &

    Case ( not IsEmpty ( Field 6 ) and Field 6 ; "F6 description " & Field 6 & "% | ") &

    Case ( not IsEmpty ( Field 7 ) and Field 7 ; "F7 description " & Field 7 & "% | ") &

    Case ( not IsEmpty ( Field 8 ) and Field 8 ; "F8 description " & Field 8 & "% | ") &

    Case ( not IsEmpty ( Field 9 ) and Field 9 ; "F9 description " & Field 9 & "% | ") ;



Left ( t ; Length ( t ) - 3 )

)

[comment]

not IsEmpty ( Field 1 ) and Field 1 

?

[Raybaudi]

Because I read that he ALSO doesn't want something like:

F1 description 0%

But you are correct, Field 1 is enough !

So the calculation could be:

Let(  

t = Case ( Field 1 ; "F1 description " & Field 1 & "% | ") &  

    Case ( Field 2 ; "F2 description " & Field 2 & "% | ") &  

    Case ( Field 3 ; "F3 description " & Field 3 & "% | ") &  

    Case ( Field 4 ; "F4 description " & Field 4 & "% | ") &  

    Case ( Field 5 ; "F5 description " & Field 5 & "% | ") &  

    Case ( Field 6 ; "F6 description " & Field 6 & "% | ") &  

    Case ( Field 7 ; "F7 description " & Field 7 & "% | ") &  

    Case ( Field 8 ; "F8 description " & Field 8 & "% | ") &  

    Case ( Field 9 ; "F9 description " & Field 9 & "% | ") ;  

  

Left ( t ; Length ( t ) - 3 )  

)

[enquirerfm]

This works great! Thank you. It has many applications.