Concatenating 'Find' scripts

[enquirerfm]

I have a large DB of some 500K records. I have set up a number of scripts to find specific items in the DB. How can I concatenate these to find a combination of, say, 5 scripts.

For example, Script 1, finds all white cats, script 2 all black cats. If I create a script which simply has Perform Script 1 and underneath Perform Script 2 then the script will find all the white cats but then forget this and find all the black cats whereas I want black and white cats. It is too laborious to use the Find requests because each find request for each original script has a number of other elements.

I suppose one way is to create a field which I could put an 'x' into (first zero this field in case I used it before). Then the first script would find all the white cats, and insert and 'x' into this field. It would then find all the black cats, insert and 'x'. Then I would find all the records in the file which had an 'x' in this field. Is this the best way?

 

[bruceR]

Not really.

FileMaker has a find command "Extend Found Set"

Example

Set Variable [ $modkeys; get( activeModifierKeys) ]

Enter Find Mode []

Set field [ Cat_color; "black"]

If [ $modKeys = 1]

Extend Found Set

Else

Perform Find

End If

Edited November 25, 2015 by BruceR

[enquirerfm]

Thanks.  However,  I want to concatenate my find scripts because these are worked out to,  for example,  search between dates,  exclude cats in Russia,  avoid duplicates,  omit some records etc etc. I don't want to have to rewrite all these.  So the find has to be based around existing find scripts.

 

[bruceR]

See attached example.

CatFinder.fmp12.zip

The critical point was to let you know about the existence of the Extend Found Set script step.

The other info was to provide examples of how to use it.

Once aware of this feature you should be able to adapt your own scripts to use it.

[LaRetta]

24 minutes ago, enquirerfm said:

I don't want to have to rewrite all these.  So the find has to be based around existing find scripts.

Hi enquirefm,

Can you update your profile to show your FM version and OS as well as your self-rated level of expertise?  It helps us assist you.  :-)

Another option is to allow your first script to run and then, if your FM version allows, use the new summary ListOf to gather the unique record IDs of the found set in a variable.  Then let the second script run and add its found set of IDs to the original variable.  After your scripts run and have produced your combined set of record id results, switch to virtual table to display the results.

I suggest you forget your idea of putting an 'x' in the records of each found set since this is not multi-user friendly.  Writing the record's unique serial to either a variable or global field allows multiple users to run the script at same time.  The other problem with record marking ( with an 'x' ) is that you must then clear those marks for future use.  If a record is being modified by someone else, you can't clear the 'x'.  This all creates problems and complicates the issue.

So if you are not familiar with virtual technique, it might be simpler to write each record set's ids to a global field instead.  Then create a relationship from the global to your table and Go To Related Records to produce your combined found sets.  This would be fairly simple to set up.  You can use a wrapper script which runs each script as sub-script, writing each set to the global field.

Your profile does not show your level of expertise either so I do not know which method (virtual or global) would work best.  Nor do I know what your scripts entail so I cannot offer further details on the process but I hope this gives you additional ideas.

Edited November 25, 2015 by LaRetta

[enquirerfm]

Thanks for your replies. Sorry BrianR I can't open this (I've updated my profile LaRetta and I still using 8.5) - thank you for taking the time to make up a file.

 

Quote

So if you are not familiar with virtual technique, it might be simpler to write each record set's ids to a global field instead.  Then create a relationship from the global to your table and Go To Related Records to produce your combined found sets.  This would be fairly simple to set up.  You can use a wrapper script which runs each script as sub-script, writing each set to the global field.

This looks possible just not absolutely sure how to do this... not sure I fully understand how global fields work in this context - I understand their basic operation in terms of storing data across a whole file but not in using scripts.

For example, if I run the first script which brings up white cats, say this is a set of 2000 records. What am I writing to the global field and surely this will then write it to the whole file?

[LaRetta]

2 hours ago, enquirerfm said:

f I run the first script which brings up white cats, say this is a set of 2000 records. What am I writing to the global field

You are writing only the unique ID from the table.  Every table should have one and it should be a meaningless id, preferably a simple auto-serial (or in more future versions a UUID).  When writing only the unique ID to a variable and then setting the global field with that multiline value, you have created the 'left side' (parent) key of a relationship.

2 hours ago, enquirerfm said:

and surely this will then write it to the whole file?

You will be setting a single global field which is very lightweight.  

1) establish unique IDs (if you need help let us know)

2) add the text field called gListIDs (in the field's Options > Storage, checking 'global') in your data table

3) Open your graph, select your data table and duplicate it.  Then connect the tables placing the original table occurrence (parent) of the table on your left and the duplicate (child in this case called self-join) on your right (this isn't required but it can be helpful).  Now join left Data::gListIDs to right Data 2::id using =.

With this relationship in place, and if you are on a Data layout, you can GoToRelatedRecords [ data 2 ] which 'finds ALL the records and displays them on your layout.  Whew!  The attached will walk you through it all.  I'm unsure if you have FMP Advanced (maybe still called Developer back then) but you can walk through the script using debugger if so.  Regardless, I commented the scripts heavily.  I did not error trap since I did not know what your scripts did.

Also for others reading, there are faster methods of gathering IDs (even export/import) or Copy All Records but I wanted to focus on getting enquirefm established with this concept first.  You all are welcome to take enquirefm deeper into the fine-tuning realm if you choose.  BTW, El Capitan does not like v11 at all.  It is like working in molasses, screen blanks and jerks, and the differences between 11 and 14 is glaring and almost painful.

enquirefm, if you get stuck with it just let us know.  Even if you choose not to go this route, this will be a concept which is basic in understanding many aspects of FileMaker so it is well worth picking apart anyway.

I used version 11 since I am pretty sure El Capitan would not run v8 and I didn't want to unarchive it to even try.  Hopefully you can open this.  If not, I've explained it quite thoroughly I hope and I could just post the scripts as well.

MultipleFinds.fp7.zip

Wait ... were variables available back in version 8?  Nope!  It would be really helpful to upgrade both your Windows box and FM if you can.  Instead of writing to a variable, you'll have to set the global field directly.  

Assisting someone with a really old version of FileMaker isn't easy. 

What else wasn't available back then?  I'll look again tomorrow ... it is very late and we have holiday.

I also question your methods of finding.  It would make more sense to set up checkboxes of different criteria and perform a single find but it seemed you weren't ready to change what exists now.    :-)

Edited November 26, 2015 by LaRetta

[comment]

16 hours ago, enquirerfm said:

I want to concatenate my find scripts because these are worked out to,  for example,  search between dates,  exclude cats in Russia,  avoid duplicates,  omit some records etc etc. I don't want to have to rewrite all these.  So the find has to be based around existing find scripts.

I am not sure rewriting - actually, copy-pasting - would be such a bad idea. However, if you wanted to follow strictly good practice and keep all the logic in a single place, then consider splitting your existing scripts into two parts each: the first part creates the find request/s, the second part performs the first part and performs the actual find.

Once you have that, you can easily add another script that performs several scripts of the first part (i.e. creates multiple find requests defined in those scripts), then performs a find.

I believe this - combining find requests, before performing a single find - would be both simpler and more efficient that any attempt to perform multiple finds first and then try to combine the found sets.

 

Edited November 26, 2015 by comment

[LaRetta]

So you are suggesting that we not break Humpty Dumpty apart only to put him back together again.  Rearranging enquirefm's scripts would certainly be the way to go!  Nice!

[enquirerfm]

I will get into this and let you know but just wanted to tell you how much I appreciate your help and the trouble you have taken with the problem. I hope this is of some help to others. Thank you.

[comment]

47 minutes ago, LaRetta said:

So you are suggesting that we not break Humpty Dumpty apart only to put him back together again.

Yes, something like that.

Another option is to use Extend Found Set[] conditionally - see the attached example:

 

CombineFinds.fp7

[bruceR]

Attached is my example file, turned into FP7 format. Comment's file does essentially the same thing.

Also attached is Tony White's excellent guide to FileMaker features and when they were introduced.

The question of when variables were introduced was raised.

Looks like they started with 8.0

CatFinder.fp7.zip

FileMaker-Code-Objects-as-ERD_2015-04-27_v5.pdf

[enquirerfm]

Trying to work through comment's suggestion and it seems to work great! Many thanks. However, I will work through everything here since there is a lot to learn... thank you again. (I love the chart by the way BruceR)

[enquirerfm]

In passing... just to let you know I found the CombinedFinds from 'comment' the easiest to implement - thank you for the file - it was very helpful.

[Jondb]

Hi - I found this thread when I had the same question.

I can't open the fp7 examples as I'm using fp12, but with a bit of trial and error I found a working solution which - frankly - is much simpler than I expected it would need to be. This may be exactly what some of you have already suggested, but just in case...

Enter Find Mode []

Set Field [Field one: "Black Cat"]

New Record/Request

Set Field [Field one: "Fat Cat"]

New Record/Request

Set Field [Field one: "Crocodile"]

Perform Find []

This does the trick, giving a cumulative result from all the requests. I presume it would also work if the fields were different. Hope this is useful.

[comment]

13 minutes ago, Jondb said:

I can't open the fp7 examples as I'm using fp12

Of course you can.
http://www.filemaker.com/help/12/fmp/en/html/fmp_basics.3.10.html#1073169