A Faster Way to Build a Related Found Set?

[FMDuck]

I have a solution where I may have to list all invoices for 10,000 companies. Currently I loop through all the customer records and collect their key field into a single global then use the global to relate to the foreign key field in the invoice table. This obviously is slow.

Is there a better way of doing this?

[Reed]

Could you create a value list of the primary keys and place that into a field using the ValueListItems() function? That would make a return delimited list of all the companies or just the related ones, depending on how you define the value list.

[jrb12]

Jeff,

Here's a few more ideas to speed up the collecting of keys.

1. If every invoice needs a customer, why not sort the invoice table accordingly and use it directly for your report? Or use the Customer table directly if your report needs to begin there.

2. Use the 1 on 1 relation from FM6 to match every record in the Customer table with each one in the table you are using to hold the global.

3. Go to a Customer layout with just the Customer key field on it and do a Copy All Records with no style. Then paste that into your global field. Filemaker can surely do this quicker than your script.

Cheers,

John.

[FMDuck]

I worked through the VL option a little but I don't think it will work. The VL can be based on a relationship, but the keys would still need to be collected in advance to build that relationship.

Unless I'm not getting part of it :

[Fenton]

If it is the Loop that is slow, you could use the Copy All Records step instead. Go to a layout with ONLY the CompanyID, Copy All Records. Go to a layout with the global. Paste. Commit. Go To Related Records [show, global to ID field].

There is a glitch with Copy All Records. It either requires Export privileges. Or, better, run it as a subscript, with "Run with full access". It also wipes out the clipboard, unless you save it somewhere.

You're talking about using this with version 7, right? The 64,000 character text field limit in 6 may get you with 10,000 records. 7's limit is one billion I think ( I once used Copy All Records to put 17MB in a global; it was not fast, but it worked).

It may also be that your Loop could be optimized. View As Form will make a Loop many times faster. You can switch back to List when you're done. Freeze Window first also helps.

[FMDuck]

This is on 7. I want to avoid using the clipboard. Interesting about the Form View. By default, I always went to a list view if anything thinking that would be faster but I never tested it.

Thanks much.

[bikergeek]

If I understand what you're doing correctly, it sounds like you should simply add a calc field to your invoice table that concatenates the unique customer key, a newline and a global key. Then change the relationship from matching the invoice::key field to the invoice::keycalc field.

This way, you can match any single customer's invoice set by using that customer's key, or all invoices at once by using the global key.