Using relationships to create a record of a find?

[merkaba22]

I have a list view which includes simple calcs that I can first sort by manufacturer (with up to 25 products, for example) and then limit that sort by further criterion -- I want to take the data from that last found set of records and create a new and permanant record in a new layout (in a new file, if necessary).

[bobearl]

Hi there,

I've seen a couple of your recent posts, and I think you and I are trying to accomplish exactly the same thing. I am trying to take a found set and create a portal into the found set. It seems that that can't be done, although I must confess I'm puzzled as to why. It seems that the portal only looks into all records, so another approach would be to take the found set and export it into another table, or to save it as another table, or to export or save it to another file. Did you ever figure out how to do this?

I've read some advice regarding taking the found set and assigning a global field to the found set, and then using that global as a condition to break the found set out, but I'm still lost. If anyone has any advice, I would be very appreciative. Thanks.

[Vaughan]

"I am trying to take a found set and create a portal into the found set. It seems that that can't be done"

Stuff like this can be done, but it has limitations.

One method puts the unique ID numbers of all found records into a global field (this can be done using the Copy All Records script step) then uses the global field in a self-join relationship to display the records in a portal. The big limitation here is that text fields in FMP 6 and earlier are limited to 64,000 characters, so the method is limited to displaying only around 8,000 records (based on the key field string being 7 characters plus 1 for the carriage return: fancy long key fields will reduce this number correspondingly). In FMP 7 the text field limit is extended to 2 GB so this method becomes much more robust and reliable, but it is important to remember it still has a limit. The data from this global field could then be stored in a record to keep a permanent record.

Other methods involve setting a flag field in the found set of records and using a self-join relationship, but any method that uses flag fields will break very easily in a multi-user environment.

One issue with any method that attempts to save found sets is the case where one or more of the records have been deleted. Another is the case where new records are created that would match the criteria. These "edge"onditins need to be thought about and trapped for accordingly.

[bobearl]

Thanks for the advice. I am dealing with a huge number of records (750K), so I think that I will have to go with the flag method. I have tried to make the global flag field work, but I clearly don't understand something fundamental about self-joins. After trying essentially every permutation of linking self-join table, the closest I got was after using a multiple relationship that set the flag equal to the global and the primary keys equal. This gave me all of the records, which was not the hoped for results, but the records which were not in the found set had empty fields. I'm still baffled as to why this was, and I lack an intuition as to why this multiple relationship does something that, say, setting the flag equal to the global without the primary keys would do. Do you know if anyone has put together a tutorial for self-joins or addressed a similar newbie question in the past? Thanks very much for your help!

[bruceR]

You are on the wrong track. What is the point of putting a gazillion records in a portal?

If you are running into text field size limits you most certainly don't want to flag and unflag 750K records.

[Ender]

Is the found set likely to be large (thousands of records)? In this case a portal won't be very helpful anyway. If the found set is small, you can do a multi-key relationship to the matching records. This is much simpler than flaging and unflaging 750,000 records. But there is a limit to the number of keys in the multi-key (I don't know what that limit is though.)

Another idea is to use a filtered portal to act as the find mechanism. Putting several criteria in the relationship will quickly reduce the number of related records.

[Ender]

(I don't know what that limit is though.)

Luckily Vaughan knows!

[bobearl]

This is probably not the way to go, but I'm using a complex find I modeled after Matt Petrowsky's tutorial which seems to work well to easily create a query. Specifically, I take the 750K records, create value lists which capture all of the different types of values (in this case, they are documents which have an identified Author, Document Type, and location entered). After I do the find, I take what is not a much more manageable 10K or so records and use the portal to generate lists of documents for a given day (or week or month) and perform a count. I can then export the count of documents by month to Excel to graph how the trends are going for a given Author, Document Type, etc. As it is, the complex find takes about 2 minutes to run, but because I haven't figured out how to work on just the found files without deleting the 740K others (importing of which takes about an hour), the fastest solution I have found is to Export the found set, and then reimport it into a new database. Bizarre, clunky, unwieldy for sure - I have just cobbled this together, but I definitely think there must be more straightforward ways of doing it. The portal may be dumb or unnecessary - I just picked it because it seems handy to be able to coast over to Aug 12 and look at the complete list of documents from the found set that were done that day. If anyone has some advice on how to simplify this mess, please let me know. Thanks!