There are times, when the number of child records of a parent  becomes large, that it would be useful to be able to search, sort and aggregate the child records without changing the set-up of the view in the parent.. As an example I might want to search for all customers in a customer group from a given town, or all orders above £1,000 from a customer

 

This turns out to be very simple, and takes its form from a technique in FileMaker called a Virtual List. The table containing the list has no fields of its own (with a slight caveat below), only a table reference to the child.

 

Step 1. Create the Virtual List table, I will call it List. From this table add a table reference to the child table, eg Orders. Set this to Show as Embedded

 

Step 2. Add a button in the parent table with this code

 

do as server

            delete (select List);

            for a in Orders do

                        let b := (create List);

                        b.(Orders := a)

            end

end;

openTable("List")

 

Step 3. Run the code and set up the columns in the list as you wish, using the fields available from the child

 

Having more than one Virtual List

 

The same List table can be re-used for all your Virtual Lists. In the list table

 

Step 1. Add another table reference as above.

Step 2. Add a tab for each reference and place the reference field underneath its tab

Step 3. Add a number field for each reference. Make this hidden.

Step 4. Use the Display field only if… to make each tab only visible if its number field is set to 1.

Step 5 Add a new view and give it a name, say Orders.

Step 6. Modify the above code slightly

 

do as server

            delete (select List);

            for a in Orders do

                        let b := (create List);

                        b.(Orders := a;

isOrders := 1)

            end

end;

openTable("List",”Orders”)

 

isOrders is the number field in Step 3. When you run this code it will go to the named view and the record will have a single tab available showing the correct child record.

 

Making it Multi User

 

In the List table add 'Created by' = user() to the Readable if section so that all the above is only affecting records created by you.