AnsweredAssumed Answered

How to delete an entire line when a variable is empty

Question asked by jaywalker7 on Mar 2, 2018
Latest reply on Mar 6, 2018 by danshockley

Like • Show 0 Likes0
Comment • 5

Hey!

I'm trying to write a calculation field that will delete a line (with a leading string, although this could be chnaged to be a variable too) when a variable is empty.

It will look something like this;

"Datecode- " & Returned Sprocket Item::Sprocket1Datecode & ¶ & "Part Number- " & Returned Sprocket Item::Sprocket1CustPN & ¶ & Pictures::Sprocket1Gear1 & Returned Sprocket Item::Sprocket1ToothWear1

Except with about ten lines. If for example I don't have a value for a datecode, I don't want that or the leading string "datecode" to appear in the final calculated result. I've tried using the hiding tools but then formatting is a nightmare.

Thanks for the help!

P.s. I feel like this FileMaker Custom Function:RemoveValue ( Value ; ValueList ) and this

filterLines( filterField; filterValue )

Case( IsEmpty( filterField );"" ;

Let ([

numValues = ValueCount( filterField );

topValue=LeftValues ( filterField; 1 );

match=Case(PatternCount(topValue; filterValue); topValue);

remainValues=RightValues( filterField; numValues-1)];

match & filterLines( remainValues; filterValue)

)

are close to what I want, but I need it to find variables in general, not a specific value

No one else has this question

Outcomes

philmodjunk Mar 2, 2018 6:48 AM

If ( not IsEmpty ( Returned Sprocket Item::Sprocket1Datecode ) ; "Datecode- " & Returned Sprocket Item::Sprocket1Datecode & ¶ ) & If ( Not IsEmpty (...

2 people found this helpful

Actions

jaywalker7 @ philmodjunk on Mar 2, 2018 7:00 AM

Thank you!!!!!

Is there a way to efficiently loop that, or can it only be done line by line?

Actions

philmodjunk Mar 2, 2018 7:09 AM

I suppose you could use a script to set a variable or a field to the value and then you might be able to loop through the values, but given that each label and each field is different, it probably wouldn't be worth the effort to do it in a loop. (if the data was in the same field, but each value-label pair were in a different record for a set of related records, this could be done differently.)

If it wasn't for the quoted label text, you could just use list:

List ( Field1 ; Field2 ; ....)

And the null values then automatically drop out.

1 person found this helpful

Actions

user19752 @ philmodjunk on Mar 4, 2018 7:14 PM

List() function may be useful, to avoid line breaks (in calculation and tail of result).

List (

Case ( field1<>"" ; "label1:" & field1 ) ;

Case ( field2<>"" ; "label2:" & field2 ) ;

...

)

2 people found this helpful

Actions

danshockley @ philmodjunk on Mar 6, 2018 7:20 AM

Combining Phil's advice, you'd have something like this:

List (

If ( IsEmpty ( SampleTable::SomeField1 ) ; "" ; "Label 1: " & SampleTable::SomeField1 )

; If ( IsEmpty ( SampleTable::SomeField2 ) ; "" ; "Label 2: " & SampleTable::SomeField2 )

; If ( IsEmpty ( SampleTable::SomeField3 ) ; "" ; "Label 3: " & SampleTable::SomeField3 )

)

That works, but you have to put each field in it twice, and it's a bit hard to read. Another option would be to have a very simple custom function named something like LabelWhenValueNotEmpty ( conditionalLabel ; someValue )

Then, your usage would look like this - much cleaner and easier to read, as well as easier to avoid making the mistake of accidentally picking two different fields for single line:

List (

LabelWhenValueNotEmpty ( "Label 1: " ; SampleTable::SomeField1 )

; LabelWhenValueNotEmpty ( "Label 2: " ; SampleTable::SomeField2 )

; LabelWhenValueNotEmpty ( "Label 3: " ; SampleTable::SomeField3 )

)

The custom function definition would be the following (or something similar):

// LabelWhenValueNotEmpty ( conditionalLabel ; someValue )

// version 1.0

/* Puts the "conditional label" in front of the value if and only if the value is not empty. */

If ( IsEmpty ( someValue )

; ""

; conditionalLabel & someValue

)

1 person found this helpful

Actions

5 Replies

philmodjunk Mar 2, 2018 6:48 AM
Mark Correct
Correct Answer
If ( not IsEmpty ( Returned Sprocket Item::Sprocket1Datecode ) ; "Datecode- " & Returned Sprocket Item::Sprocket1Datecode & ¶ ) & If ( Not IsEmpty (...
2 people found this helpful
Like • Show 1 Like1
Actions
jaywalker7 @ philmodjunk on Mar 2, 2018 7:00 AM
Mark Correct
Correct Answer
Thank you!!!!!

Is there a way to efficiently loop that, or can it only be done line by line?
Like • Show 0 Likes0
Actions
philmodjunk Mar 2, 2018 7:09 AM
Mark Correct
Correct Answer
I suppose you could use a script to set a variable or a field to the value and then you might be able to loop through the values, but given that each label and each field is different, it probably wouldn't be worth the effort to do it in a loop. (if the data was in the same field, but each value-label pair were in a different record for a set of related records, this could be done differently.)

If it wasn't for the quoted label text, you could just use list:

List ( Field1 ; Field2 ; ....)

And the null values then automatically drop out.
1 person found this helpful
Like • Show 1 Like1
Actions
user19752 @ philmodjunk on Mar 4, 2018 7:14 PM
Mark Correct
Correct Answer
List() function may be useful, to avoid line breaks (in calculation and tail of result).

List (
Case ( field1<>"" ; "label1:" & field1 ) ;
Case ( field2<>"" ; "label2:" & field2 ) ;
...
)
2 people found this helpful
Like • Show 0 Likes0
Actions
danshockley @ philmodjunk on Mar 6, 2018 7:20 AM
Mark Correct
Correct Answer
Combining Phil's advice, you'd have something like this:

List (
If ( IsEmpty ( SampleTable::SomeField1 ) ; "" ; "Label 1: " & SampleTable::SomeField1 )
; If ( IsEmpty ( SampleTable::SomeField2 ) ; "" ; "Label 2: " & SampleTable::SomeField2 )
; If ( IsEmpty ( SampleTable::SomeField3 ) ; "" ; "Label 3: " & SampleTable::SomeField3 )
)

That works, but you have to put each field in it twice, and it's a bit hard to read. Another option would be to have a very simple custom function named something like LabelWhenValueNotEmpty ( conditionalLabel ; someValue )
Then, your usage would look like this - much cleaner and easier to read, as well as easier to avoid making the mistake of accidentally picking two different fields for single line:

List (
LabelWhenValueNotEmpty ( "Label 1: " ; SampleTable::SomeField1 )
; LabelWhenValueNotEmpty ( "Label 2: " ; SampleTable::SomeField2 )
; LabelWhenValueNotEmpty ( "Label 3: " ; SampleTable::SomeField3 )
)

The custom function definition would be the following (or something similar):
// LabelWhenValueNotEmpty ( conditionalLabel ; someValue )
// version 1.0

/* Puts the "conditional label" in front of the value if and only if the value is not empty. */

If ( IsEmpty ( someValue )
; ""
; conditionalLabel & someValue
)
1 person found this helpful
Like • Show 0 Likes0
Actions

How to delete an entire line when a variable is empty

Outcomes

Related Content

Recommended Content