This site uses tracking cookies. By using this site, you agree to our Privacy Policy. If you don't opt in, some parts of the site might not function.
Blog
Home / Blog / FileMaker / Using the Virtual List Technique – Part 1
04Nov 2015

Using the Virtual List Technique – Part 1

About the Author

Jeremy Brown Jeremy Brown

Jeremy is proud to be a certified FileMaker developer. He has worked previously at a charter school network where he built the student information system used for three schools. As with many other developers, Jeremy stumbled into the world of FileMaker and has become fascinated with solving business needs in the platform.

Comments (15)

Kirk - November 6, 2015

Suggestion: When I create the virtual list, I do a FIND on the data, and a Get (FoundCount) returns the number of records I need for the report. A couple more steps, but dynamically allocates the number of required records, rather than an arbitrary number.

Reply
    Jeremy Brown
    Jeremy Brown - November 6, 2015

    Hi Kirk.
    You’re right. That’s a good alternative. I wonder if it could significantly slow down a report if you have to create 200 rows each time.
    Blank records are cheap in terms of space and time and caching, so it probably doesn’t hurt a system to have those rows already in there.

Chris Andersson - November 7, 2015

Thank you for this. I love the process diagram, it makes the logic very easy to follow.

Reply
    Jeremy Brown
    Jeremy Brown - November 7, 2015

    You’re welcome Chris.
    I’m a teacher by training so I tend to want to spell things out clearly (at least I always hope I do). I use this picture to remind me of the process as well. 🙂

Miguel Ángel Fernández - February 28, 2016

Hi Jeremy,
I use this technique for virtual lists as well as for dynamic value lists.
I Set the ID field as an stored calculation field with the formula Get ( RecordNumber ) instead of Numerical field, auto-increment serial. I do so in case a virtual list’s table record is accidentally deleted.
I would like to know your opinion on this aproach.
Kind regards,
Miguel

Reply
    Jeremy Brown
    Jeremy Brown - March 7, 2016

    Hi Miguel.
    Thanks for reading.

    I think your idea is just fine to use. In my cases, the user has no possibility of deleting the virtual list rows. The rows are either in a report that is immediately printed or is in a portal. In all instances, the user does not have the opportunity to interact with the records; its merely read-only. Since Virtual lists are based on unstored calcs already, I wish to minimize the number of other un-stored calcs.

Miguel Ángel Fernández - March 7, 2016

Hi Jeremy,
I do so just to avoid resetting the ID counter before creating records after having copied the virtual list table to a new solution file.
Thank you for your reply.
Kind regards,
Miguel Angel

Reply
Dimitrios Fkiaras - March 13, 2016

Hello and thank you very much for Virtual List technique. I made a small modification

I name my columns “COLUMN01″…”COLUMN99″ and I have this calculation in each field.

So I dont need to change the calculation, but only the last two digits of FieldName for it to work

GetValue ( Substitute ( __ROW_DATA;”|” ;”¶”) ;Right ( GetFieldName ( Self ); 2))

Reply
    Miguel Ángel Fernández - April 26, 2016

    I do so, but with a slight modification:
    GetValue ( Substitute ( __ROW_DATA ; “|” ; “¶” ) ; Filter ( GetFieldName ( Self ) ; “0123456789” ) )
    So you do not have to care about the number length of the field’s name.
    Kind regards,

instig8r - April 25, 2016

Another approach is to loop in the script to add or delete records from the table until it matches the number needed. I also use Miguel’s Get ( RecordNumber ) approach. So much simpler.

Reply
    Jeremy Brown
    Jeremy Brown - April 25, 2016

    Howdy.
    I’ve chosen to keep a virtual list table with a set number of records simply because adding a loop to create records there is just another thing to do in the script. Also if you use virtual lists in such things as portals, showing related summary data on the fly, it would cause further delay to have to create the new records every time.
    As a colleague says: fields are cheap and records are cheap. If you have a table sitting around with empty records, that doesn’t add much to your file, and it is ready at a moment’s notice.
    But adding records to the virtual list table does work. Since there’s many ways to solve the same problems in FileMaker, it is left to us to decide. I’ve chosen to keep the table at the ready.

Mariette Quinn - June 10, 2017

I have a question. I work at a University with graduate students. I have created a database to hold all of the information on my students with a separate table containing faculty info. I started using filemaker 16 only three weeks ago and have been learning as I go. This week, I’m at the point where I must learn about relationships. I am trying to show the relationship between students and faculty. Faculty can be related to students as advisor/coadvisor or committee member. I used a portal on my master layout to define when faculty are advisors/coadvisors to students. I used a join table called studentsfaculty and included four fields, facultyid, studentid, advisor and coadvisor. In the portal I created a value field to get names from the faculty table. Very cool. Worked like a dream. It is sortable and reportable. Now I’m trying to figure out how to include committees…..each student has at least two committee members and they would come from the same value list. I’m stuck on whether I need another join table, use the same studentfaculty table or whether I just need a field in the portal called committee and continue the same portal; whether this will still be sortable. I am currently using a checkbox in the portal to distinguish advisor/coadvisor status. Could i just add another checkbox called committee and call it a day? Id like to be able to report on a faculty member and see how many students they advise and how many they are on committees for. At the same time, I want to be able to see each student and their advisors and committee members. I hope this makes sense. Like I said, I’m a very new newbie.

Thanks for any help…

Reply
    Jeremy Brown
    Jeremy Brown - June 20, 2017

    Hello Mariette, Sorry for the late reply. Let me study this and get back to you.Feel free to email me as well at jbrown@soliantconsulting.com

Leave a Reply