Hi,

First at all, to all of you that live in UK, I would like to express
my deepest condolences for what it is happening today in London. It is
terrible and makes no favour to human kind. I live in Spain and as you
know we suffered a similar not so far ago.

Please excuse me if I introduce a question now.

I am using FM Server 5.5 and FM Pro 5.5, multiuser solution.

I have scripted a Go to related records (one to many). It works fine.
Then I need to carry out a Find within this records. I have found no
easy way to do it. The best option I have tried so far it is to create
a script in the related file that flags the found records, then adds a
Find script step that includes all the desired parameters + the flag
field.

However, this option is not practical. The database consist of
+200.000 records and growing fast. This means that the Replace step
can take several minutes to complete. Besides, after finding and
showing the records, I need to reset the flag field, so that it is
ready for a future find.

I have also tried a loop, that includes or excludes records based on
the find criteria. However, this is also a very slow solution.

Is there any optimal way to do this in a networked solution with so
many records? From what I have read in the newsgroups, there is no way
to Constrain the Found set if the Found set comes from a Go to related
records (this would be a good option, if it worked).

Thanks,

Carlos

In article <a51qc15q80e45hdd218nnn61pn2p7evje8 (AT) 4ax (DOT) com>, Carlos Pereira
<cpereira (AT) nnnhotmail (DOT) com> wrote:
Since you don't really want the Related records (or at least not all of
them), there's no point in using the Go To Related Records command at
all.

Instead, you can basically write your own "Go To Related Records"
scripts that find the records you do want. A script in the main can
first store the current Relationship link data in a Global field, then
run a second script in the related file that uses that data plus the
new parameters to find the appropriate records you are wanting.
ie.
Script in main file:

Set Field [Relationship::GlobalField, RelationshipLinkField]
Perform Script [RelatedFile, FindRelatedSubset]


Script in RelatedFile called "FindRelatedSubset":

Enter Find Mode []
Set Field [RelationshipLinkField, GlobalField]
Set Field [{whatever other parameters are wanted}]
Set Field [{whatever other parameters are wanted}]
...
Perform Find []

You may or may not want some error checking in there to cope with
situations where none of the related records match the extra required
parameters.




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

Dear HH,

Thanks for your reply.

Your idea should work if the first search criteria (from Parent to
Child) is a single value. However, the reason to go from Parent to
Child is that I need the user to choose a date range, then find the
related records that match the range, then specify the other criteria
in a script in the Child file.

I already have the date range values in a global field in the Parent
file (dates converted to number, using the Matt Petrowsky technique
for finding date ranges). It finds the related records in the Child
file. However, if I try to script what you suggest:

Script in RelatedFile called "FindRelatedSubset":

Enter Find Mode []
Set Field [RelationshipLinkField, GlobalField]
Set Field [{whatever other parameters are wanted}]
Set Field [{whatever other parameters are wanted}]
...
Perform Find []


and I have already tried it, it does not works because the
RelationshipLinkField has a list of carriage return separated values
for the date range, like:

0732132
0732133
0732134
0732135
0732136
0732137
0732138
0732139
0732141
0732140

I thought of splitting this value list and searching the values one by
one, but this is slow and very complicated if I want to end up with a
found set that meets other criteria besides the date range.

Any ideas?

Thanks

Dear HH,

I sort of found a viable solution, using your idea adjusted to my date
search requirements. I have tested it with pretty complex searches and
it seems to be working OK, very fast.

What I have done is:

1.- Let the user select the date range in Main file
2.- Store the date range in a field in the Main file (Range_field), as
dates converted to numbers, separated by carriage returns (Petrowsky
method). I might need to store the value in a global field instead,
jsut to avoid record locking issue in a multiuser environment.
3.- In the RelatedFile, create 3 global fields
4.- A script in the RelatedFile do the following:

Script in RelatedFile called "FindRelatedSubset":

Set Field [ global_0, RelationshipLinkField, Range_field ]
Set Field [ global_1, 1 ]
Set Field [ global_2, PatternCount( global_0; "¶" ) ]
Enter Find Mode
Loop
Set Field [ Searchfield_1, MiddleWords (global_0; global_1 ; 1 ) ]
Set Field [ Searchfield_2, other parameter wanted ]
Set Field [ Searchfield_3, other parameter wanted ]
Exit Loop If [ g_i = g_ii ]Set Field [ g_i, g_i + 1 ]
New Record/Request
End Loop
Perform Find[ Replace Found Set ]
Show the Found set

-----------------------------------------------

Just for reference to whoever reads this and might wonder how the
Range_field is calculated, here is the formula:

You need 2 global fields:

g_Start (global): start date
g_End (global): end date

Unstored calculation=
Substitute(Middle(" 0 1 2 3 4 5 6 7 8 9"; (Int(Mod(g_Start;
10^1)/10^0) + 1) * 2- 1; (Int(g_End/10^0) - Int(g_Start/10^0) + 1) *
2); " ";"¶" & Left(Right("000000"& g_Start; 7); 6))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(g_Start; 10^2)/10^1)
+ 1) * 2 -1; (Int(g_End/10^1) - Int(g_Start/10^1) - 1) * 2); " ";"¶"&
Left(Right("000000" &g_Start; 7); 5))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(g_Start; 10^3)/10^2)
+ 1) * 2 -1; (Int(g_End/10^2) - Int(g_Start/10^2) - 1) * 2); " ";"¶"
& Left(Right("000000" &g_Start; 7); 4))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(g_Start; 10^4)/10^3)
+ 1) * 2 -1; (Int(g_End/10^3) - Int(g_Start/10^3) - 1) * 2); " ";"¶"
& Left(Right("000000" &g_Start; 7); 3))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(g_Start; 10^5)/10^4)
+ 1) * 2 -1; (Int(g_End/10^4) - Int(g_Start/10^4) - 1) * 2); " ";"¶"
& Left(Right("000000" &g_Start; 7); 2))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(g_Start; 10^6)/10^5)
+ 1) * 2 -1; (Int(g_End/10^5) - Int(g_Start/10^5) - 1) * 2); " ";"¶"
& Left(Right("000000" &g_Start; 7); 1))
&
Substitute(Middle(" 1 2 3 4 5 6 7 8 9"; (Int(Mod(g_Start; 10^7)/10^6)
+ 1) * 2 -1; (Int(g_End/10^6) - Int(g_Start/10^6) - 1) * 2); " ";"¶"
& Left(Right("000000" &g_Start; 7); 0))
&
Substitute(Middle(" 9 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(g_End;
10^1)/10^0)) * 2 +1; (Int(g_End/10^0) - Int(g_Start/10^0) + 1) * 2); "
";"¶" & Left(Right("000000" &g_End; 7); 6))
&
Substitute(Middle(" 8 7 6 5 4 3 2 1 0"; (9 - Int(Mod(g_End;
10^2)/10^1)) * 2 + 1;(Int(g_End/10^1) - Int(g_Start/10^1) - 1) * 2); "
";"¶" & Left(Right("000000" &g_End; 7); 5))

In article <ufesc1dvshrg8ark7odv08kpk3mlf1a45f (AT) 4ax (DOT) com>, Carlos Pereira
<cpereira (AT) nnnhotmail (DOT) com> wrote:


Okey-dokey ... from a quick glance it looks VERY complicated, but you
say it works so I won't even try playing around with it. )

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