I need a good script!

[LaRetta]

Hi everyone,

I have a set of records, one field being ClientID. There is a second field called 'Select' which is just a '1' toggle. There may be 50 records belonging to one ClientID, but only one of those records will have a 'Select' = 1. I need to be able to find all of the records for a ClientID if even one is selected. Then I need to delete them.

I'm having trouble picturing how to accomplish this.

LaRetta

[Ugo DI LUCA]

I would create a calculation field called "c_select" based on the select field with Case(Select = 1,1,"") and a g_Client_ID (global)

Make a selfoin 1 with "select" at left side and c_select at right.

Make a selfjoin 2 with g_clientID::Client_ID

The the script should go loop with that "kind" of structure:

Go to related records (selfjoin1) -show records related only-

------>This will give you a set of records selected.

Go to first record

set g_Client_ID (=Client ID)

Go to related records (selfjoin2) -show records related only-

------> This will give you all records for that Client_ID

delete

[CobaltSky]

Hi LaRetta,

Here's a relatively simple procedure that will get you there.

1. Create a self-join called 'SelfByClient' which matches the ClientID field to itself.

2. Create an unstored calc number field called 'cSelect.Flag', with the formula set to Max(SelfByClient::Select).

With the above in place, create a script which performs a find for all records with a 1 in the cSelect.Flag field, then deletes them.

[djgogi]

Attention, the following action could be dangerous so backup your data before trying it!

Define an self relationship ClientID-->ClientID an check the option "When deleting records in this file delete also related records".

Now perform find on flag field "1", and simply delete the found set (if any).

As I said, cascading deletes could be very dangerous, so be careful.

Dj

[CobaltSky]

Hi Dj,

If it weren't so 'dangerous' (as you put it) , the method you have suggested would be a good one, because it would be the most efficient.

However although you've said it could be dangerous, you've not explained why...

The nature of the problem is that it will ensure that *every* time a client record is deleted (whether it is selected or not, and regardless of how or why it is being deleted) all the other records for that client will be deleted at the same time with no questions asked.

In fact, once the suggested code is in place, there will be no way to delete one record for a particular client without eradicating all reference to the client within the file. ...which goes rather further than meeting the original requirement

Depending on how the file is used, it may be that that isn't a problem, but it is certainly more than a trivial consideration.

[djgogi]

You won't believe it Ray, but I just wanted to delete the post because it is useless (as you noted).

Dj

This part is added:

If you control deletion of records (using script), invalidating ClientID ( if flag is not equal to 1) before deleteing it would by pass the cascade delation of other records, iow only the selected record will be deleted.

Any way, don't think I would use it, neither.

[Ugo DI LUCA]

I maybe misunderstood LaRetta's post as I thought she wanted to delete every set of records for a single Client when there was at least one record with 1 in the "select" field.

Or I maybe misunderstood your answer if the script you suggest does perform this condition.

Please tell.

[CobaltSky]

Hello Ugo,

No, you didn't misunderstand, that is exactly what LaRetta was asking for - and both Dj's answer and my own were offering ways to achieve it.

In the case of the solution I suggested, the relationship enables each of the records for a particular client to access each other record for the same client - and the Max( ) function when applied to the Select field via the clientID:clientID self-join relationship allows each of them to detect whether any of the records for that client has a 1 in the 'Select' field.

Once this is in place, a search for "1" in the calc field will locate not only all the records which have a 1 in the select field, but all the other records which have clientIDs which match any of the selected records.

Thus they can all be found and deleted at once with a script that has only two steps.

The approach that Dj suggested used a somewhat similar principle, based on a clientID:clientID self-join relationship, and using the relationship itself to delete the other records for a given selected client. It would have worked very swiftly because the field his suggested script would have searched on (the Select field) would be indexable. However, as he and I were also discussing above, it would have carried some unintended consequences and attendant risks which would probably outweigh it's advantages in process efficiency (ie raw speed of execution).

Hope the above comments make it a little clearer how the answers relate to LaRetta's request?

Cheers,

[Ugo DI LUCA]

So I almost misunderstood both your answers.

Not very used with Max, Min and other similar functions.

Min would have brought 0, right ?

Average ?

While I'm with these aggregate fuctions, in order to understand better, could you give an example of the use of either StDev and StDevP functions with relationships.

Finally, the solution you suggest work for a specific Client_ID.

What could have been the solution in the case she wanted to delete all records from the file with such conditions, at the fly ? Sure there is no need for a loop script...

[LaRetta]

Hi everyone!

Thanks to all for their suggestions. Ray's example was so easy to implement that it was like I didn't have to do anything at all

Much obliged!

LaRetta

[jasonwood]

could you give an example of the use of either StDev and StDevP functions with relationships.

StDev calculates the Standard Deviation and StDevP calculates the population standard deviation. This is for statistical analysis. Eg: say you are calculating the standard deviation of marks, and the marks are normally distributed, and the standard deviation is 5... This tells you that 66.67% of all marks are within 5 points from the mean (above and below), and 95% of marks (or something like that) are within 10 points from the mean.

If you're not familiar with standard deviation, you probably won't have a use for it.

[Ugo DI LUCA]

Hi Jason

Thank you for the reply.

I also hope I would't have to use these functions

from my above question. Min would have give me a 0 - Yes ?

Or is it that whatever standard deviation function I'll use, I will always be 99 points from the mean..., and that would always got a 0 on "statistical functions". I'll keep trying though...

[Ugo DI LUCA]

Hey Jason, just for fun, I checked the number of posts with StDev in it on this forum. I got 4 posts (including 2 for this thread).

At least I'm quite sure I won't use them.

Tom Fitch did make this answer

[CobaltSky]

Hi Ugo,

Standard deviation is the square root of the average of the squared deviations of values from the mean.

In simpler terms, you might say it is the average distance from the values to the average value. It tells you how clustered or spread a group of values are.

So, for example, if somebody tells you that the average cost of a theatre ticket in a holiday resort you are going to is $10, that could be because there are five theatres and they all charge exactly ten dollars - or it could alternatively be that there are five theatres and four of them charge $1 but the other one charges $46 - or any other combination of values to yield the average of $10.

Thus if you wanted to be able to plan your holiday budget, you might like to know more than just the average cost. In the example I've given, the standard deviation for the first case (all theatres charging $10) would be zero - no deviation. However in the second case, the standard deviation (average difference from the average) would be over 20.

The rule of thumb is that as the value of the standard deviation approaches (or exceeds) that of the mean (average) value, the predictive usefulness of the mean is progressively invalidated, and other measures (eg median or mode) may then be more meaningful indicators of the nature of the source data.

In the latter example I described, the mode and median values would both be $1 even though the average is $10 - so they would give a more accurate indication of what you might expect to pay.

So much for statistics...

On the other subject you queried, regarding this thread... No, the Min( ) function would not return a zero. Aggregate functions ignore blank (null) fields and since the select field that LaRetta described could only have one value in it (and since nulls are ignored), the Min( ) function would return a 1 also.

And last, but not least, regarding your earlier query, the script suggested in fact deals with all clients who have one or more select values, locating and deleting all the records for all selected clients in one pass. That is because the script begins with a find on the calculated Max( ) field, and therefore will locate all the records for all clients who have a value in the Select field on any record.

[djgogi]

Hi Ray, I've found the way to make it secure!

Define an calculated unstored, text field

clientToDelete=Case(Select,ClientID)

uncheck "validate only if all referenced fields are not empty"

then define an relationship

clientToDelete-->ClientID and check the option " delete also related records".

Dj

[CobaltSky]

Hi Dj,

Yes, that would do it.

It will be both efficient and secure that way. Good piece of lateral thinking!!

Cheers,