Using checkbox and script to delete sets of records

Mac and PC/FMP 9 Advanced
Skill level: moderate

Table B: 2 fields
Field 1 is "N_Codefk" text field with 225 unique records consisting of
3 numerals each (eg: 257)
Field 2 is "N_Name" text field with the name of the neighbourhood,
also all unique

Table A (Primary Table in which the user interacts with the records)
Related fields are "N_Codepk" and "N_Name" which is a Lookup from
table B.

When each user imports new records monthly, the raw data may contain
2000 records with 25 Neighbourhood Codes of which she needs only 10.
She will want to delete the unwanted records. Each user will receive
and want different subsets of the 225 so I must include them all.

I solved the problem earlier on "Areas" of which there are only 12 by
creating a checkbox field that the user could use to check off
unwanted "Areas" and then a Find and Delete script would run.
Here it is:

Show All Records
If[PatternCount(Sales_Data::Areas_Delete_Checkboxes ; "01") =1
Perform Find[Restore]
Delete All Records
End If
Show All Records
If[PatternCount(Sales_Data::Areas_Delete_Checkboxes ; "02") =1
Perform Find[Restore]
Delete All Records
End If

and so on 12 times.

But there has to be a better way because I don't really want to type a
script with 225 possibilities (is parameters the correct word here?)

Any help greatly appreciated. Thanks

Re: Using checkbox and script to delete sets of records

On Nov 18, 11:29 am, CRC123 wrote:
> Mac and PC/FMP 9 Advanced
> Skill level: moderate
>
> Table B: 2 fields
> Field 1 is "N_Codefk" text field with 225 unique records consisting of
> 3 numerals each (eg: 257)
> Field 2 is "N_Name" text field with the name of the neighbourhood,
> also all unique
>
> Table A (Primary Table in which the user interacts with the records)
> Related fields are "N_Codepk" and "N_Name" which is a Lookup from
> table B.
>
> When each user imports new records monthly, the raw data may contain
> 2000 records with 25 Neighbourhood Codes of which she needs only 10.
> She will want to delete the unwanted records. Each user will receive
> and want different subsets of the 225 so I must include them all.
>
> I solved the problem earlier on "Areas" of which there are only 12 by
> creating a checkbox field that the user could use to check off
> unwanted "Areas" and then a Find and Delete script would run.
> Here it is:
>
> Show All Records
> If[PatternCount(Sales_Data::Areas_Delete_Checkboxes ; "01") =1
> Perform Find[Restore]
> Delete All Records
> End If
> Show All Records
> If[PatternCount(Sales_Data::Areas_Delete_Checkboxes ; "02") =1
> Perform Find[Restore]
> Delete All Records
> End If
>
> and so on 12 times.
>
> But there has to be a better way because I don't really want to type a
> script with 225 possibilities (is parameters the correct word here?)
>
> Any help greatly appreciated. Thanks

You need a way for users to enter the codes they want to keep. For
the previous problem, you used checkboxes, but that might not work
here with 255 options. A simple global text field in which the user
enters each code they want to keep on a single line would work.

User enters the list of codes they want to keep into field, gCodes.

You script would look like this...

//You will build a Find to grab all the records you want to keep
Enter Find Mode
Set Variable[$counter;1]
Loop
Set Field[NCodepk; GetValue(gCodes; $counter)]
Exit Loop If [ValueCount(gCodes) = $counter]
Set Variable[$counter; $counter+1]
New Record/Request
End Loop
Perform Find
//Now you'll reverse that find and delete those records.
Show Omitted Only
Delete All Records


You'll probably want some error trapping in there, to make sure that
users enter values in the global and don't enter blank lines, etc, but
this is the essence of the thing.

Re: Using checkbox and script to delete sets of records

In article
<25235074-2aa6-49d7-8115-9ad998d4e096@a39g2000pre.googlegroups.com>,
CRC123 wrote:

> Mac and PC/FMP 9 Advanced
> Skill level: moderate
>
> Table B: 2 fields
> Field 1 is "N_Codefk" text field with 225 unique records consisting of
> 3 numerals each (eg: 257)
> Field 2 is "N_Name" text field with the name of the neighbourhood,
> also all unique
>
> Table A (Primary Table in which the user interacts with the records)
> Related fields are "N_Codepk" and "N_Name" which is a Lookup from
> table B.
>
> When each user imports new records monthly, the raw data may contain
> 2000 records with 25 Neighbourhood Codes of which she needs only 10.
> She will want to delete the unwanted records. Each user will receive
> and want different subsets of the 225 so I must include them all.
>
> I solved the problem earlier on "Areas" of which there are only 12 by
> creating a checkbox field that the user could use to check off
> unwanted "Areas" and then a Find and Delete script would run.
> Here it is:
>
> Show All Records
> If[PatternCount(Sales_Data::Areas_Delete_Checkboxes ; "01") =1
> Perform Find[Restore]
> Delete All Records
> End If
> Show All Records
> If[PatternCount(Sales_Data::Areas_Delete_Checkboxes ; "02") =1
> Perform Find[Restore]
> Delete All Records
> End If
>
> and so on 12 times.
>
> But there has to be a better way because I don't really want to type a
> script with 225 possibilities (is parameters the correct word here?)
>
> Any help greatly appreciated. Thanks

I'm not sure I fully understand it (especially since the numbers seem a
little confused), but perhaps a simple looping script would work.
eg.

Set Field [g_LoopCounter, "000"]
Loop
If [ PatternCount(Sales_Data::Areas_Delete_Checkboxes,
g_LoopCounter) = 1]
Set Error Capture [On]
Enter Find Mode []
Set Field [N_Codefk, g_LoopCounter]
Perform Find []
If [Get(CurrentError) = 0]
Delete All Records
End If
End If
Set Field [g_LoopCounter, g_LoopCounter + 1]
Exit Loop If [g_LoopCounter > {whatever highest possible code is}]
End Loop

This script starts at code "000" using a temporary Global field
(g_LoopCounter), checks to see if that code's Checkbox is turned on and
if so Finds and Deletes the records. It then increments the
g_LoopCounter field by 1 and loops back around to check code "001". It
continues looping until g_LoopCounter is more than the highest possible
code.

Obvious you can't use "Perform Find [Restore]" command, so have to
script the Find Request. You also need to make sure some records have
actually been found - otherwise if the user clicks Cancel on the "no
matching records" window, your script may end up deleting ALL records
in the table!

It doesn't really matter if the codes are not sequential since the
script won't be able to find the "missing" codes in the Checkbox field
anyway. Technically you could use a Value List to only go through the
codes you do have (and would have to if the codes are not numerical
like the examples), but it's probably more hassle than it's worth for
only 200-odd codes.

The above probably needs some tweaking, so DO NOT use it on important
data until you're 200% positive that it does what you need.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Using checkbox and script to delete sets of records

Helpful Harry that seems like just the ticket. Thanks One clarifiction
please? Below.

On Nov 19, 9:30 pm, Helpful Harry
wrote:
> In article
> <25235074-2aa6-49d7-8115-9ad998d4e...@a39g2000pre.googlegroups.com>,
>
>
>
> CRC123 wrote:
> > Mac and PC/FMP 9 Advanced
> > Skill level: moderate
>
> > Table B: 2 fields
> > Field 1 is "N_Codefk" text field with 225 unique records consisting of
> > 3 numerals each (eg: 257)
> > Field 2 is "N_Name" text field with the name of the neighbourhood,
> > also all unique
>
> > Table A (Primary Table in which the user interacts with the records)
> > Related fields are "N_Codepk" and "N_Name" which is a Lookup from
> > table B.
>
> > When each user imports new records monthly, the raw data may contain
> > 2000 records with 25 Neighbourhood Codes of which she needs only 10.
> > She will want to delete the unwanted records. Each user will receive
> > and want different subsets of the 225 so I must include them all.
>
> > I solved the problem earlier on "Areas" of which there are only 12 by
> > creating a checkbox field that the user could use to check off
> > unwanted "Areas" and then a Find and Delete script would run.
> > Here it is:
>
> > Show All Records
> > If[PatternCount(Sales_Data::Areas_Delete_Checkboxes ; "01") =1
> > Perform Find[Restore]
> > Delete All Records
> > End If
> > Show All Records
> > If[PatternCount(Sales_Data::Areas_Delete_Checkboxes ; "02") =1
> > Perform Find[Restore]
> > Delete All Records
> > End If
>
> > and so on 12 times.
>
> > But there has to be a better way because I don't really want to type a
> > script with 225 possibilities (is parameters the correct word here?)
>
> > Any help greatly appreciated. Thanks
>
> I'm not sure I fully understand it (especially since the numbers seem a
> little confused), but perhaps a simple looping script would work.
> eg.
>
> Set Field [g_LoopCounter, "000"]
> Loop
> If [ PatternCount(Sales_Data::Areas_Delete_Checkboxes,
> g_LoopCounter) = 1]
> Set Error Capture [On]
> Enter Find Mode []
> Set Field [N_Codefk, g_LoopCounter]
> Perform Find []
> If [Get(CurrentError) = 0]
> Delete All Records
> End If
> End If
> Set Field [g_LoopCounter, g_LoopCounter + 1]
> Exit Loop If [g_LoopCounter > {whatever highest possible code is}]
> End Loop
>
> This script starts at code "000" using a temporary Global field
> (g_LoopCounter), checks to see if that code's Checkbox is turned on and
> if so Finds and Deletes the records. It then increments the
> g_LoopCounter field by 1 and loops back around to check code "001". It
> continues looping until g_LoopCounter is more than the highest possible
> code.
>
> Obvious you can't use "Perform Find [Restore]" command, so have to
> script the Find Request. You also need to make sure some records have
> actually been found - otherwise if the user clicks Cancel on the "no
> matching records" window, your script may end up deleting ALL records
> in the table!
>
> It doesn't really matter if the codes are not sequential since the
> script won't be able to find the "missing" codes in the Checkbox field
> anyway. Technically you could use a Value List to only go through the
> codes you do have (and would have to if the codes are not numerical
> like the examples), but it's probably more hassle than it's worth for
> only 200-odd codes.

When you say "numerical" do you mean of field type number? The codes
are all 3 numerical digits in length but the field is defined as text.
>
> The above probably needs some tweaking, so DO NOT use it on important
> data until you're 200% positive that it does what you need.
>
> Helpful Harry
> Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: Using checkbox and script to delete sets of records

In article
,
CRC123 wrote:
> On Nov 19, 9:30 pm, Helpful Harry
> wrote:
> > In article
> > <25235074-2aa6-49d7-8115-9ad998d4e...@a39g2000pre.googlegroups.com>,
> >
> > I'm not sure I fully understand it (especially since the numbers seem a
> > little confused), but perhaps a simple looping script would work.
> > eg.
> >
> > Set Field [g_LoopCounter, "000"]
> > Loop
> > If [ PatternCount(Sales_Data::Areas_Delete_Checkboxes,
> > g_LoopCounter) = 1]
> > Set Error Capture [On]
> > Enter Find Mode []
> > Set Field [N_Codefk, g_LoopCounter]
> > Perform Find []
> > If [Get(CurrentError) = 0]
> > Delete All Records
> > End If
> > End If
> > Set Field [g_LoopCounter, g_LoopCounter + 1]
> > Exit Loop If [g_LoopCounter > {whatever highest possible code is}]
> > End Loop
> >
> > This script starts at code "000" using a temporary Global field
> > (g_LoopCounter), checks to see if that code's Checkbox is turned on and
> > if so Finds and Deletes the records. It then increments the
> > g_LoopCounter field by 1 and loops back around to check code "001". It
> > continues looping until g_LoopCounter is more than the highest possible
> > code.
> >
> > Obvious you can't use "Perform Find [Restore]" command, so have to
> > script the Find Request. You also need to make sure some records have
> > actually been found - otherwise if the user clicks Cancel on the "no
> > matching records" window, your script may end up deleting ALL records
> > in the table!
> >
> > It doesn't really matter if the codes are not sequential since the
> > script won't be able to find the "missing" codes in the Checkbox field
> > anyway. Technically you could use a Value List to only go through the
> > codes you do have (and would have to if the codes are not numerical
> > like the examples), but it's probably more hassle than it's worth for
> > only 200-odd codes.
>
> Helpful Harry that seems like just the ticket. Thanks One clarifiction
> please?
>
> When you say "numerical" do you mean of field type number? The codes
> are all 3 numerical digits in length but the field is defined as text.

Since it is a numerical number, you can simply increment by 1 (unlike a
code that is alphabetically based: eg. AA, AB, AC, AD, etc.) ... BUT
because you are using a Text field and need to retain three digits you
will have to do a bit more manipulation.

If, near the end of the above script, you use just:

Set Field [g_LoopCounter, g_LoopCounter + 1]

you will lose any leading zeroes since FileMaker converts the Text to a
Number, increments that and then converts it back to Text (you'll
actually lose any non-numerica characters when performing numerical
functions on them).

To get around this you will need to "manually" reinsert the leading
zeroes when needed. The easiest way to do this is to simply always add
three zeros to the front and then use the Right function to grab just
the last three characters of the resulting text.
eg.
Set Field [g_LoopCounter, Right("000" & g_LoopCounter + 1, 3)]


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)