GTRR with Multikey Question

[Brian C]

Does anyone know what the maximum item limit is for a multi-key? In other words, how many keys can be stored in a multikey?

Is there a limit to using a multi-key with the GTRR script step?

[mr_vodka]

I would assume that it would possibly only be limited by the maximum character limitation.

Text field (up to 1 billion characters)

[JesseSFR]

Sounds about right to me.... If you are talking about creating a compound key meaning you have a calculation of several values then I would suspect it's as big as a text field which is some ridiculous size up to a couple Gigs. When you are talking about multiple joins in a relationship I don't know of any published limit.

[Brian C]

What I am doing is copying the get(recordid) of all records in the found set using Copy All Records/Requests and then pasting it into a single text field. I then use a GTRR script step to locate the found set of records by using a relationship that links the multikey to a calculation field that stores the get(recordid)

I am attempting to avoid using either a loop to mark individual records or to use a Replace All script step for purposes of speed.

[Ender]

How about using the 'Match all records in found set' option in the GTRR step?

[Vaughan]

Wow, that's how it had to be done in FMP 6 and earlier.

For FMP 7 and later, use the "match all records in found set" option in the GTRR script step. It just works.

[JesseSFR]

Vaughn and Ender definitely know what they are talking about... with 8+ you can click match all records in found set option on the GTRR script step. ... and it will do exactly what you are looking for.

[Brian C]

I have a post from a few months back where we very carefully explored what you refer to.

This does not work in a true separation model where the data and presentation layers are always kept separate.

With how GTRR currently works, the destination file MUST contain the table in question. If you are going from one Interface file to another interface file where neither of the files contain data tables, GTRR is completely useless for the purpose of transferring a found set of records.

Edited July 18, 2007 by Guest

[Søren Dyhr]

found set using Copy All Records/Requests and then pasting it into a single text field

I would only use it if I really would need the last drop of performance, please note that an unstored calc' can be woken up, by say:

http://www.filemaker.com/help/Functions%20Ref4.html

While ValueListItems( needs a field that is stored and indexed, in order to make the valuelist work.

What's wrong with copy/paste, well you are not abel to restore every kind of data stored in the clipboard, in a clip-save preludium ...although a test in both a container and a text field covers most of it.

--sd

[mr_vodka]

I have a post from a few months back where we very carefully explored what you refer to.

This does not work in a true separation model where the data and presentation layers are always kept separate.

With how GTRR currently works, the destination file MUST contain the table in question. If you are going from one Interface file to another interface file where neither of the files contain data tables, GTRR is completely useless for the purpose of transferring a found set of records.

Hi Brian,

I am going to assume that you meant this post.

Moving Found set to new file Thread

I am curious... Do you have a stripped down version of what you are exactly trying to achieve that we can take a look at?

[JesseSFR]

I read your post it was very informative. It never occurred to me that there would be difficulty using GTRR with the seperation model, but in retrospect it makes perfect sense giving the way the "external table's layout" functions.

With this in mind... I have a couple questions.... One is why are you using get(recordID) in a calculation for your id? Do you not have unique id on the records in question? If you had a serial number that was stored on your table, then it would be much, much quicker. You could use this in your multikey join instead of the calculation.

Basically, after you have your found set, I am guessing you switch to a layout that has just the id field in question on it, then you run your copy all records command. Now you have all your ids on your clipboard.

Next, you switch go to the file you want to end up in. Paste your multikey into a field that's related to the id on the table you would ultimately like to end up in. Finally, use GTRR to get to those records and you are all set.

This is pretty tough to explain in text, if it's unintelligible because of my poor explanation I can probably put together a file pretty quick.

[Brian C]

I think you explained it rather well actually.

To answer your question I will attempt to explain the best that I can. The simplest answer is this:

I use get(recordID) only for use with GTRR in transferring found sets to an external print file or to mark and relocate marked records for the user.

I need a functional field with a unique id with the same name in every single table for interface purposes. This allows me to create one script one time that I can use to refer to this one field in any table no matter what table it is and it will work.

If I import records I have to remember to re-populate the serial number sometimes.

To explain farther would require me to explain my import processes, the differences between my current and history tables, my naming conventions for ids at each level, etc.

As to what I do:

- Go to a layout with only the one field existing

- Copy all records/requests (This creates a return delimited list of all record ids in the clipboard)

- Go to layout (original layout)

- Go to object (printkey) - this keeps my script generic enough to be used on any table.

- Paste (replace contents)

- Commit Record/request (no dialog)

- Navigate to external print file

- locate the correct layout to be printed for the specific table

- Use GTRR to locate the same found set of records

[Ender]

You might try using the list() function with a Set Field[] instead of the Copy/Paste stuff.

[Brian C]

I cannot use Setfield[]. This script step has no ability to be set via indirection.

I would have to create a script step for every Table in my solution which is impractical if I ever need to change the script or add a new Table.

My entire goal is to make all my scripts function for all of my tables without ever having to make the script specific to any table.

The only place where this breaks down is the GTRR script step which I keep in subscripts on purpose so that I can remember to add or fix as I add more tables.

So I use Go To Object and then I use

Paste or Insert Calculated Result[].

[Ender]

Hey, we can't help it if you picked a complicated implementation in your efforts to make it simple! We're just trying to help.

Alright, one more idea for you to shoot down:

Use Go to Object to pick your destination field, then use Set Field to insert the list of values.

[JesseSFR]

Alright, I gotcha... I typically name all my ID's id and that is why my solution makes sense to me and doesn't make any sense to you, which makes perfect sense ;-)

I think you have pretty much have the right approach. I would do basically the same thing if I were trying to accomplish what you are. The paste is the only way to go because unfortunately you have all that junk on your clipboard so you can't do any set fields or anything.

As far as setup goes, I would definitely put a global in my print file to act as my compound key. Unfortunately, you can't get around making relationships for each table you add so I would do that in my print file off said global.

This would be my process:

-call my script with a parameter that corresponds to the table I am importing so I can later use it to determine which relationship and print layout to use.

//this may look familiar it's yours

- Go to a layout with only the one field existing

- Copy all records/requests (This creates a return delimited list of all record ids in the clipboard)

-call a script in the print file that takes the same parameter passed into your script that does the copying.

-go to a layout attached to the TO that contains your global which all the new relationships are branching off

//this will look familiar again

- Go to object (printkey) - this keeps my script generic enough to be used on any table.

- Paste (replace contents)

- Commit Record/request (no dialog)

- finally call a "dispatch" script and pass along your parameter to it.... this script would just have a huge if/else block with the different GTRR commands you would need.

Then you're done. So I think I basically agree with everything you did, it seems like you had the right idea and it took a little while for me to catch up.

[JesseSFR]

oh yea... by the way just an FYI you can use:

-go to object

-set field[no specified field , "blah"]

and it would work. The problem is in your case it won't because of all the data being on your clipboard.

[Søren Dyhr]

I am attempting to avoid using either a loop to mark individual records or to use a Replace All script step for purposes of speed

I came to think, multikeying or the above isn't an either/or for saving found sets:

http://www.sumware.net/robfm/savingfoundsets.php

--sd

[JesseSFR]

This is a great way of saving your found set. I don't think it will fix this particular problem though because you can't bring use that technique across files I don't believe. What do you think?

[Søren Dyhr]

Yes Brian writes

GTRR is completely useless for the purpose of transferring a found set of records.

I have to poke into it, to see if can get what and why!

--sd

[Brian C]

Hey, we can't help it if you picked a complicated implementation in your efforts to make it simple! : We're just trying to help.

Alright, one more idea for you to shoot down:

Use Go to Object to pick your destination field, then use Set Field to insert the list of values.

While I think that it is interesting to note that set field can be used without specifying the source field I cant help but worry if it was intentional on FM's part... Is it more of an undocumented feature that could potentially become broken in a future release?

My other question is this: is there a limit to using the list function? Or is it as unlimited as the clipboard? I have to make sure I can grab a multi-key that can be 100,000 records or even more... If I have users that are doing searches on historical archive data, it could potentially be half a million records they could be dealing with. I have to make sure that whatever I do, it will not break.

I like using the copy all records because it is very fast. Will the list function get slower as the record numbers increase?

[comment]

set field can be used without specifying the source field I cant help but worry if it was intentional on FM's part... Is it more of an undocumented feature that could potentially become broken in a future release?

It's a documented feature. Of course, any feature can change significantly or disappear entirely in a future release - whether documented or not.

[Brian C]

So using "Set Field" without specifying the source field seems to be the same thing as using "Insert Calculate Result" with the "Select entire contents" option checked. Who would have thunk it? :)

[Brian C]

Ok I am working on how to use the List () function to see how easy it will be to replace my Copy All Records method.

So far I have determined that some sort of relationship must exist in order for List() to grab multiple values from a set of records. If I do not use a relationship I only get the id from the current record... Anyone have any tips on how they use it to generate a multikey on the current found set of records?

Do you have to mark your records first and then use a relationship to grab only the marked records? If so this will slow down my process a lot...

[Søren Dyhr]

Do you have to mark your records first and then use a relationship to grab only the marked records?

Well the next best, is the tail-recursive CF that JMO shows here:

http://www.filemakerpros.com/GetNthRecordTAIL.zip

Unless there might be a way to do it with unrealted TO's, like:

http://www.sumware.net/robfm/savingfoundsets.php

...because my hunch tells me that List( is an overloaded object enherriting methods from GTRR, when speaking C++'ish???

I have just arrived home after a holiday, and needs to see to other matters than experimenting with this.

--sd

Edited July 25, 2007 by Guest

[Brian C]

My users will have found sets in excess of 100,000 and will approach half a million records in a given found set when dealing with history.

GetNthRecords used with recursion is limited to 10,000 iterations.

With tail recursion it can be pushed to FM's built in recursion limit of 50,000.

If I use GetNthRecords in a loop with pure variables I loose any speed benefits I would have gained by using a recursive custom function.

Using the List function is more flexible than GetNthRecords since it can grab the same number of records as Copy All Records. Unfortunately, it requires an additional relationship for each TO it will be used in and it also requires marking the records in some fashion so that they can be identified via the relationship. Being forced to mark the records removes any speed benefit that would have been gained from using List. Unless someone can show me a way that List can used without recursion and without having to mark records, it will do me no good.

Result: Copy All Records is hands down the fastest way that I can get what I need with the speed that I need.

For dealing with loss of the clipboard data I store the clipboard in a temporary field and restore it once my script has completed its run. This is limited to text data but it solves the problem of the lost clipboard data in most cases.

[comment]

I store the clipboard in a temporary field and restore it once my script has completed its run. This is limited to text data

Not if you use a container field to store the clipboard:

http://fmforums.com/forum/showpost.php?post/168330/

[Brian C]

Thanks for the tip Comment! I will use a container field instead.

[jamesa555]

It just occurred to me if you are concerned about the size limits of a copy all records/paste, you could chop each fount set into two or even three parts (assuming the finds themselves don't take to long to process).

Find

omit top half the records (or third, etc)

copy all records/paste

redo the find (modify last find with no changes)

omit the bottom half

copy all records/paste

tack together

You could easily program the number of splits based on the size of the foundcount.

Also, I notice you mentioned in your other post about replicating the find from one file into another. I just thought of a way to fairly easily store find requests (not found sets) for future use, or to pass to another file. Well, "just thought of it" is an exaggeration, I've been racking my brains for a day now for my own project.

Here is how it works, and forgive me if you already know it or if it has already been posted - while still in find mode but after the user entered their find criteria, tab through each field on the find layout and build a "list" of fields and their contents into a variable (let $fieldlist = Get(ActiveFieldName) & "ZZZ" & Get(ActiveFieldContents) & $fieldlist). Also include a header/separator line in $fieldlist for each find request/record and note the omit state get(RequestOmitState); you can even tack on further extends and constrains. Obviously you can stick $fieldlist into a real field or pass it as a script parameter to another file. Then when you need to reproduce that find, just have a script that loops through each line of $fieldlist one line at a time, tabs through all fields and pastes the appropriate value into the appropriate field if(get(ActiveFieldName) = left(middleValues(blah blah $fieldlist ) with some tweaking for pasting into related fields. You could even store these find requests for the users to call on again and again; and it's independent of tab orders (unlike copy all records with multiple fields on the layout). I admit it's ugly because you have to tab through the layout and make sure field additions go onto the layout in both files, but it seems to work. Hope this helps,

Jim

[Søren Dyhr]

Indeed there is a good point in bringing in the Saved searches technique - here as well:

http://www.filemakermagazine.com/videos/saved-searches-making-it-easy-to-search-again-and-again-and-again-br-free-video.html

And your suggestions to an improvement of the original method which reflects what was possible at the time Matt made the video, is a welcome input to this debate.

But since we are tied to a certain layout anyway could some liberties perhaps be taken?

The collection part of the script could really benefit from Daniele's CF:

http://www.briandunning.com/cf/389

Freeze Window 

Modify Last Find 

Go to Record/Request/Page [ First ] 

Set Variable [ $$Result; Value:AllFields ( 1 ) ] 

Loop 

      Go to Record/Request/Page [ Next; Exit after last ] 

     Set Variable [ $$Result; Value:$$Result & "¶" & AllFields ( 1 ) ] End Loop 

Enter Browse Mode 

The $$Result is obviously stored in a record like Matt does it, then bringing it out into a series of request can't be CF'ed as far as I know, but the inclution of some of the newer bells and whistles makes the recursivity found in Matt's script a bygone ...at least easier to read and debug with $variables:

Enter Find Mode [  ] 

Go to Record/Request/Page [ First ] 

Go to Field [  ] 

Go to Next Field 

Set Variable [ $crlf; Value:Get ( ActiveFieldName ) ] 

Set Variable [ $counter; Value:1 ] 

Loop 

        Set Field [ GetValue ( $$Result ; $counter ) ] 

        Go to Next Field 

        If [ $crlf = Get ( ActiveFieldName ) ] 

              New Record/Request 

        End If 

        Exit Loop If [ $counter = ValueCount ( Get(ScriptParamter) ) ] 

        Set Variable [ $counter; Value:$counter+1 ] 

End Loop 

Perform Find [  ] 

--sd

[Brian C]

Your use of variables is similar to an approach I use in my audit trail processes. I discovered this technique from another audit trail process that someone else had come up with in version 8. I make use of repetitions in variables to split up the data so that you don't have to work as hard at trying to parse it out later on.

As for storing the queries to transfer found sets:

Many of the searches that occur may require separate queries.

So after locating a core found set, users may extend or constrain to get what they are looking for. So for me, storing the queries is not the answer. Some queries will in rare cases reference unstored calculations, which of course takes a long time. If I reconstruct queries just to locate a found set - it will add a lot more complexity and it will add a lot more time just to get the user to a report screen.

P.S.

I just discovered that you can use Tabs to isolate the ID field on a layout from the rest of your fields. This means that Copy All Records will no longer require you to create a separate layout just to copy the contents of a single field.

[comment]

you can use Tabs to isolate the ID field on a layout from the rest of your fields. This means that Copy All Records will no longer require you to create a separate layout just to copy the contents of a single field.

Most interesting!!