Jump to content
Server Maintenance This Week. ×

Creating a Virtual List Report

This topic is 2789 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hello Forum.  I had some basic questions (for starters) about a report I am trying to create:

1.  Can this type of report be created?

2.  Can you point me in the right direction.

I have the following tables: People connected to both Readings and Sessions with the usual primary key-foreign key relationship



I would like to generate a report for a person, with 1 Reading record, then Session records after that Reading record timestamp, then another Reading record, etc.

So basically it would look like this:

                                    Person Name

          August 1 9:00:00 am (Reading record)

                   August 2 10:00:00 am (Sessions Record)

                   August 3 10:00:00 am (Sessions Record)

                   August 4 10:00:00 am (Sessions Record)

                   August 5 10:00:00 am (Sessions Record)

            August 6 9:00:00 am (Reading Record)

                   August 6 10:00:00 am (Sessions Record)

                   August 7 10:00:00 am (Sessions Record)

                   August 8 10:00:00 am (Sessions Record)

                   August 9 10:00:00 am (Sessions Record)


I've researched and used the VL technique, but didn't see an example that matched this.  I could do it with 1 Reading record and multiple Sessions records, but not multiple/multiple. 

If it is possible, I just don't know where to start.  I've considered another TO where all the records reside, basically creating them in the order I need them for the report, but that's more like the VL.

If I want them all to line up it would be easier, or do I just do a VL report, and as I'm appending the records, if they are Session records, just add a tab space?

Just need some pointers.



Link to comment
Share on other sites

The simple BFI approach would be to run an Inner and Outer loop - Outer loop to capture the Reading records, then an inner for each of those records to capture their related Sessions records.

For presentation, I would put one set of data in one data point in the row, and the other in another, then use two fields on the final VL report.

Below is a simple version of a script to create an array $$data[$i] to be read back on a Virtual List layout - it has two data points for each row, one in position 1 for the Reading, and in position 2 in a new row for each associated Session record

Hope this helps


Screen Shot 2016-08-31 at 8.31.03 am.png

Link to comment
Share on other sites

Here's what I ended up with, and the resulting report.  The script is long and ugly, so if anyone wants to offer any suggestion I'm always willing to learn.

I can never figure out how to go from the script to the forum.  If I try a screen shot its never readable, so I attached it as a PDF





Edited by Steve Martino
Link to comment
Share on other sites

It does look at first glance like a sub-summary report from the Sessions context would work, but the Reading record is the problem.

Link to comment
Share on other sites

@BruceR . I tried a sub-summary, but I couldn't figure out how to pull from the main table and the 2 related tables (I didn't think it was possible) and get them in this order.  Mainly because the report can start with a record from either related record table, but needs to be sorted continuously, by timestamp field.

@Fitch. Yeah that's where I got stuck.  After lots o' googling, most seemed to point to the VL technique. 

Thanks both for responding. 

Edited by Steve Martino
Link to comment
Share on other sites

@Steve Martino - I think that script can be cleaned up a bit - your global vars are each for an entire Column - which can go astray if there is a missing value somewhere down the list.

Thanks to people here, I tend to use a row approach based around $$data[$i] that gives one row of data for every iteration of $i, that then gets split into the individual values in the final report.

Which then also allows the formatting to be applied at a field level in the final report, rather than embedded in the data, as you can decide exactly where in the row certain data points actually exist (i.e., you can pad data into the 4th or 9th or 21st cell, and then format that cell accordingly...)

Link to comment
Share on other sites

Thanks @webko.  I did consider the missing value situation.  I copied this from my main business database and solved the missing value by putting in a "." with a case function for each time I appended, then conditionally formatted the field to hide the ".".

I'm also aware of one of JMO's favorite mantra's about (paraphrased) "...If you find yourself trying to solve a problem and you keep making your solution more and more complicated, there's probably an easier way...". 

I did see the blogs and post about VL w/repeating fields, and dug into some samples, but wasn't able to get it to work for me.  It's still a little over my head and I'll dig in deeper and pick thru the sample files over our long holiday weekend.

This DB is just for fun, as I am just an enthusiast.  But I do like learning the best way (or a better way) to accomplish a task and I like exercising the mind-sure beats the mind bending and aggravating tournament chess!

Thanks again


Link to comment
Share on other sites

This topic is 2789 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.