Check for empty records?

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Check for empty records?

Hans J. Gunnarsson
I need to check for empty records
Each record contains about 140 fields.

What is the easiest method to search for these empty records?

TIA
Hans
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Check for empty records?

Richard DeShong
For data entry and rcd maintenance, it is useful to have a series of
Boolean calc fields that check for relevant conditions.  So if this is
an ongoing issue, then break up those 140 fields into logical groups and
create a Boolean calc that checks for data.

Name the fields something like:  Group1HasData  or Group1IsEmpty  
(depending on you p.o.v.).  Then create an overall Boolean calc field
that considers all of these group fields.


On 5/23/2017 9:55 AM, Hans Gunnarsson wrote:

> I need to check for empty records
> Each record contains about 140 fields.
>
> What is the easiest method to search for these empty records?
>
> TIA
> Hans
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

--
Richard DeShong
Logic Tools
510-642-5123 office
925-285-1088 cell

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Check for empty records?

Stephen Wonfor-3
In reply to this post by Hans J. Gunnarsson
Hans

My first take would be to morph into “Messes with Variables”.  Less elegant than Richard’s Boolean approach - but it actually worked on the 6th attempt!  Not sure how you would handle logical groups of fields unless there was a predictable naming convention.  Portals would be an issue.  As would related fields on the layout.   But, other than that...

Set Variable [ $$Result ; Value: "" ]
Set Variable [ $$Fields ; Value: FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) ]
Set Variable [ $$FieldCount ; Value: ValueCount($$Fields) ]
Set Variable [ $Count ; Value: 1 ]
Loop
        Set Variable [ $$Result ; Value: List($$Result; Get ( LayoutTableName ) & "::" & GetValue($$Fields;$Count) & "&") ]
        Set Variable [ $Count ; Value: $Count + 1 ]
        Exit Loop If [ $Count > $$FieldCount ]
End Loop
Set Variable [ $$Something ; Value: Substitute($$Result;"¶";"") ]
Set Variable [ $$What ; Value: Evaluate(Left($$Something; Length($$Something)-1)) ]

$$Something is "PREFS::flag_first_start_needed&PREFS::flag_Invoice_Print&PREFS::flag_Lat_Long&PREFS::flag_Software_Version&PREFS::flag_Status_Area&PREFS::flag_Store_Email&PREFS::flag_Tooltips”

$$What returned “0ModernNo4.0v2VisibleYesYes"

Stephen

---

"I prefer the Easter Bunny.  He's less judgmental than Santa Claus" --- B. Smaller ( in a New Yorker cartoon)

> On May 23, 2017, at 10:55 AM, Hans Gunnarsson <[hidden email]> wrote:
>
> I need to check for empty records
> Each record contains about 140 fields.
>
> What is the easiest method to search for these empty records?
>
> TIA
> Hans
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Check for empty records?

Tom Fitch
That's clever, Stephen. I've leveraged your idea to make a calculation:

Evaluate (
"List( " &
Substitute ( FieldNames ( ""; "" ); ¶; ";" & ¶ )
& ")"
 )

Note that the file name and layout name are actually optional if you
want the current file and layout.

You could use this calc in a field or a script depending on how you
want your process to flow. E.g., use IsEmpty on it to test.

But also note that you should use a layout that has ONLY fields from
the table of interest, and if you're going to make this a calculated
field for a Find, don't include any fields on the layout that
reference related data, or any unstored calcs, summary fields, etc. or
this could be quite slow.

Tom Fitch
FileMaker Pro Certified Developer
Portland, Oregon


On Tue, May 23, 2017 at 10:21 AM, Stephen Wonfor <[hidden email]> wrote:

> Hans
>
> My first take would be to morph into “Messes with Variables”.  Less elegant than Richard’s Boolean approach - but it actually worked on the 6th attempt!  Not sure how you would handle logical groups of fields unless there was a predictable naming convention.  Portals would be an issue.  As would related fields on the layout.   But, other than that...
>
> Set Variable [ $$Result ; Value: "" ]
> Set Variable [ $$Fields ; Value: FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) ]
> Set Variable [ $$FieldCount ; Value: ValueCount($$Fields) ]
> Set Variable [ $Count ; Value: 1 ]
> Loop
>         Set Variable [ $$Result ; Value: List($$Result; Get ( LayoutTableName ) & "::" & GetValue($$Fields;$Count) & "&") ]
>         Set Variable [ $Count ; Value: $Count + 1 ]
>         Exit Loop If [ $Count > $$FieldCount ]
> End Loop
> Set Variable [ $$Something ; Value: Substitute($$Result;"¶";"") ]
> Set Variable [ $$What ; Value: Evaluate(Left($$Something; Length($$Something)-1)) ]
>
> $$Something is "PREFS::flag_first_start_needed&PREFS::flag_Invoice_Print&PREFS::flag_Lat_Long&PREFS::flag_Software_Version&PREFS::flag_Status_Area&PREFS::flag_Store_Email&PREFS::flag_Tooltips”
>
> $$What returned “0ModernNo4.0v2VisibleYesYes"
>
> Stephen
>
> ---
>
> "I prefer the Easter Bunny.  He's less judgmental than Santa Claus" --- B. Smaller ( in a New Yorker cartoon)
>
>> On May 23, 2017, at 10:55 AM, Hans Gunnarsson <[hidden email]> wrote:
>>
>> I need to check for empty records
>> Each record contains about 140 fields.
>>
>> What is the easiest method to search for these empty records?
>>
>> TIA
>> Hans
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Check for empty records?

Stephen Wonfor-3
Tom

Thanks, I got lucky on that one as I was doing something rather different but structurally similar.  Sure are lots of codicils.

Stephen

---

"Interesting - I use a Mac to help me design the next Cray." - Seymour Cray (1925-1996) when he was told that Apple bought a Cray supercomputer to help design the next Mac.

> On May 23, 2017, at 11:45 AM, Tom Fitch <[hidden email]> wrote:
>
> That's clever, Stephen. I've leveraged your idea to make a calculation:
>
> Evaluate (
> "List( " &
> Substitute ( FieldNames ( ""; "" ); ¶; ";" & ¶ )
> & ")"
> )
>
> Note that the file name and layout name are actually optional if you
> want the current file and layout.
>
> You could use this calc in a field or a script depending on how you
> want your process to flow. E.g., use IsEmpty on it to test.
>
> But also note that you should use a layout that has ONLY fields from
> the table of interest, and if you're going to make this a calculated
> field for a Find, don't include any fields on the layout that
> reference related data, or any unstored calcs, summary fields, etc. or
> this could be quite slow.
>
> Tom Fitch
> FileMaker Pro Certified Developer
> Portland, Oregon
>
>
> On Tue, May 23, 2017 at 10:21 AM, Stephen Wonfor <[hidden email]> wrote:
>> Hans
>>
>> My first take would be to morph into “Messes with Variables”.  Less elegant than Richard’s Boolean approach - but it actually worked on the 6th attempt!  Not sure how you would handle logical groups of fields unless there was a predictable naming convention.  Portals would be an issue.  As would related fields on the layout.   But, other than that...
>>
>> Set Variable [ $$Result ; Value: "" ]
>> Set Variable [ $$Fields ; Value: FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) ]
>> Set Variable [ $$FieldCount ; Value: ValueCount($$Fields) ]
>> Set Variable [ $Count ; Value: 1 ]
>> Loop
>>        Set Variable [ $$Result ; Value: List($$Result; Get ( LayoutTableName ) & "::" & GetValue($$Fields;$Count) & "&") ]
>>        Set Variable [ $Count ; Value: $Count + 1 ]
>>        Exit Loop If [ $Count > $$FieldCount ]
>> End Loop
>> Set Variable [ $$Something ; Value: Substitute($$Result;"¶";"") ]
>> Set Variable [ $$What ; Value: Evaluate(Left($$Something; Length($$Something)-1)) ]
>>
>> $$Something is "PREFS::flag_first_start_needed&PREFS::flag_Invoice_Print&PREFS::flag_Lat_Long&PREFS::flag_Software_Version&PREFS::flag_Status_Area&PREFS::flag_Store_Email&PREFS::flag_Tooltips”
>>
>> $$What returned “0ModernNo4.0v2VisibleYesYes"
>>
>> Stephen
>>
>> ---
>>
>> "I prefer the Easter Bunny.  He's less judgmental than Santa Claus" --- B. Smaller ( in a New Yorker cartoon)
>>
>>> On May 23, 2017, at 10:55 AM, Hans Gunnarsson <[hidden email]> wrote:
>>>
>>> I need to check for empty records
>>> Each record contains about 140 fields.
>>>
>>> What is the easiest method to search for these empty records?
>>>
>>> TIA
>>> Hans
>>> _______________________________________________
>>> FMPexperts mailing list
>>> [hidden email]
>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Check for empty records?

Hans J. Gunnarsson
In reply to this post by Stephen Wonfor-3
Thank you guys.

I had hoped there was some magic I was overlooking.  :-)
Something that enabled me to get a result without addressing all the fields.

But I’ll just use my usual approach then.

Kind regards
Hans




> On 23. maí 2017, at 17:21, Stephen Wonfor <[hidden email]> wrote:
>
> Hans
>
> My first take would be to morph into “Messes with Variables”.  Less elegant than Richard’s Boolean approach - but it actually worked on the 6th attempt!  Not sure how you would handle logical groups of fields unless there was a predictable naming convention.  Portals would be an issue.  As would related fields on the layout.   But, other than that...
>
> Set Variable [ $$Result ; Value: "" ]
> Set Variable [ $$Fields ; Value: FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) ]
> Set Variable [ $$FieldCount ; Value: ValueCount($$Fields) ]
> Set Variable [ $Count ; Value: 1 ]
> Loop
> Set Variable [ $$Result ; Value: List($$Result; Get ( LayoutTableName ) & "::" & GetValue($$Fields;$Count) & "&") ]
> Set Variable [ $Count ; Value: $Count + 1 ]
> Exit Loop If [ $Count > $$FieldCount ]
> End Loop
> Set Variable [ $$Something ; Value: Substitute($$Result;"¶";"") ]
> Set Variable [ $$What ; Value: Evaluate(Left($$Something; Length($$Something)-1)) ]
>
> $$Something is "PREFS::flag_first_start_needed&PREFS::flag_Invoice_Print&PREFS::flag_Lat_Long&PREFS::flag_Software_Version&PREFS::flag_Status_Area&PREFS::flag_Store_Email&PREFS::flag_Tooltips”
>
> $$What returned “0ModernNo4.0v2VisibleYesYes"
>
> Stephen
>
> ---
>
> "I prefer the Easter Bunny.  He's less judgmental than Santa Claus" --- B. Smaller ( in a New Yorker cartoon)
>
>> On May 23, 2017, at 10:55 AM, Hans Gunnarsson <[hidden email]> wrote:
>>
>> I need to check for empty records
>> Each record contains about 140 fields.
>>
>> What is the easiest method to search for these empty records?
>>
>> TIA
>> Hans
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Check for empty records?

tim ballering-2
In reply to this post by Hans J. Gunnarsson
We do it the brute force way:  

We perform a find with the “=“ sign in every field that matters and then save that as a script that we run, in our case, every night.  

We do not delete, but rather the script sets a status field to “delete.”  We hide those records when creating reports.  That way you never have to worry about what happened to the missing records.



Tim Ballering
[hidden email]



> On May 23, 2017, at 12:55 PM, Hans Gunnarsson <[hidden email]> wrote:
>
> I need to check for empty records
> Each record contains about 140 fields.
>
> What is the easiest method to search for these empty records?
>
> TIA
> Hans
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Check for empty records?

Larry Heuvelman
Ultimately in my solution, I am going to set it up so no records are ever deleted, only hidden. You know you are going to get the call, just preparing in advance.

Larry

Larry Heuvelman, CR, CGR, ROI
President








www.TheOwnerConsultant.com <http://www.theownerconsultant.com/>


CONFIDENTIALITY NOTICE: This email message, including any attachments, is the sender's private and confidential property, and is intended solely for the receipt, use, benefit, and information of the recipient indicated above. If you have received this message in error, please notify the sender immediately and delete this message from your computer system. Failure to do so, and any review, disclosure, distribution, or copying of this message is strictly prohibited, and may result in legal liability on your part. We believe that this email message is free of any virus or other defect. However, it is the recipient's responsibility to ensure that it is virus free and the sender accepts no liability for any loss or damage.





> On May 24, 2017, at 7:03 AM, Tim Ballering <[hidden email]> wrote:
>
> We do it the brute force way:  
>
> We perform a find with the “=“ sign in every field that matters and then save that as a script that we run, in our case, every night.  
>
> We do not delete, but rather the script sets a status field to “delete.”  We hide those records when creating reports.  That way you never have to worry about what happened to the missing records.
>
>
>
> Tim Ballering
> [hidden email]
>
>
>
>> On May 23, 2017, at 12:55 PM, Hans Gunnarsson <[hidden email]> wrote:
>>
>> I need to check for empty records
>> Each record contains about 140 fields.
>>
>> What is the easiest method to search for these empty records?
>>
>> TIA
>> Hans
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Check for empty records?

Mike Duncan
In reply to this post by Hans J. Gunnarsson
If you make the assumption that you have at least an ID field that will
have a value, you can perform an executeSQL function on that record to
dynamically return all field values from that record.

Substitute (
  ExecuteSQL ( "SELECT * FROM MyTable WHERE id = ?" ; "" ; "" ; MyTable::id
);
  "," ;
  ""
)

You can then test to see if the result of this function is not equal to the
id field, and if it is, you know there is data in the record. Otherwise,
all other fields are blank.

Would that work?
Mike


On Tue, May 23, 2017 at 12:55 PM, Hans Gunnarsson <[hidden email]> wrote:

> I need to check for empty records
> Each record contains about 140 fields.
>
> What is the easiest method to search for these empty records?
>
> TIA
> Hans
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
Reply | Threaded
Open this post in threaded view
|

Re: Check for empty records?

Hans J. Gunnarsson
In reply to this post by Stephen Wonfor-3
Once I started taking a close look at the data, as it is being imported from an Excel document, I noticed that the following would work after the import is completed:

Loop
  If Field_RecordID is not between 1 and 99999
    Set field Flag_Empty = 1
  End If
End Loop

Enter search mode
  Set field flag_Empty = 1
Perform search

Delete found set of records.

Simple enough once your brain decides to do things the simple way.  :-)

Another way I had thought of was to move the contents of all relevant fields into a list and then check if the list is empty.
But that method would have been problematic because some records contained alpha numeric text.

Kind regards
Hans




> On 23. maí 2017, at 18:16, Stephen Wonfor <[hidden email]> wrote:
>
> Tom
>
> Thanks, I got lucky on that one as I was doing something rather different but structurally similar.  Sure are lots of codicils.
>
> Stephen
>
> ---
>
> "Interesting - I use a Mac to help me design the next Cray." - Seymour Cray (1925-1996) when he was told that Apple bought a Cray supercomputer to help design the next Mac.
>
>> On May 23, 2017, at 11:45 AM, Tom Fitch <[hidden email]> wrote:
>>
>> That's clever, Stephen. I've leveraged your idea to make a calculation:
>>
>> Evaluate (
>> "List( " &
>> Substitute ( FieldNames ( ""; "" ); ¶; ";" & ¶ )
>> & ")"
>> )
>>
>> Note that the file name and layout name are actually optional if you
>> want the current file and layout.
>>
>> You could use this calc in a field or a script depending on how you
>> want your process to flow. E.g., use IsEmpty on it to test.
>>
>> But also note that you should use a layout that has ONLY fields from
>> the table of interest, and if you're going to make this a calculated
>> field for a Find, don't include any fields on the layout that
>> reference related data, or any unstored calcs, summary fields, etc. or
>> this could be quite slow.
>>
>> Tom Fitch
>> FileMaker Pro Certified Developer
>> Portland, Oregon
>>
>>
>> On Tue, May 23, 2017 at 10:21 AM, Stephen Wonfor <[hidden email]> wrote:
>>> Hans
>>>
>>> My first take would be to morph into “Messes with Variables”.  Less elegant than Richard’s Boolean approach - but it actually worked on the 6th attempt!  Not sure how you would handle logical groups of fields unless there was a predictable naming convention.  Portals would be an issue.  As would related fields on the layout.   But, other than that...
>>>
>>> Set Variable [ $$Result ; Value: "" ]
>>> Set Variable [ $$Fields ; Value: FieldNames ( Get ( FileName ) ; Get ( LayoutName ) ) ]
>>> Set Variable [ $$FieldCount ; Value: ValueCount($$Fields) ]
>>> Set Variable [ $Count ; Value: 1 ]
>>> Loop
>>>       Set Variable [ $$Result ; Value: List($$Result; Get ( LayoutTableName ) & "::" & GetValue($$Fields;$Count) & "&") ]
>>>       Set Variable [ $Count ; Value: $Count + 1 ]
>>>       Exit Loop If [ $Count > $$FieldCount ]
>>> End Loop
>>> Set Variable [ $$Something ; Value: Substitute($$Result;"¶";"") ]
>>> Set Variable [ $$What ; Value: Evaluate(Left($$Something; Length($$Something)-1)) ]
>>>
>>> $$Something is "PREFS::flag_first_start_needed&PREFS::flag_Invoice_Print&PREFS::flag_Lat_Long&PREFS::flag_Software_Version&PREFS::flag_Status_Area&PREFS::flag_Store_Email&PREFS::flag_Tooltips”
>>>
>>> $$What returned “0ModernNo4.0v2VisibleYesYes"
>>>
>>> Stephen
>>>
>>> ---
>>>
>>> "I prefer the Easter Bunny.  He's less judgmental than Santa Claus" --- B. Smaller ( in a New Yorker cartoon)
>>>
>>>> On May 23, 2017, at 10:55 AM, Hans Gunnarsson <[hidden email]> wrote:
>>>>
>>>> I need to check for empty records
>>>> Each record contains about 140 fields.
>>>>
>>>> What is the easiest method to search for these empty records?
>>>>
>>>> TIA
>>>> Hans
>>>> _______________________________________________
>>>> FMPexperts mailing list
>>>> [hidden email]
>>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>>>
>>> _______________________________________________
>>> FMPexperts mailing list
>>> [hidden email]
>>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>> _______________________________________________
>> FMPexperts mailing list
>> [hidden email]
>> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au
>
> _______________________________________________
> FMPexperts mailing list
> [hidden email]
> http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au

_______________________________________________
FMPexperts mailing list
[hidden email]
http://lists.ironclad.net.au/listinfo.cgi/fmpexperts-ironclad.net.au