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

People-----------<Readings

           -----------<Sessions

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.

Thanks

Steve

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

 

Thanks for responding @webko.  That's what I ended up doing.  But I do want to revisit it tomorrow and consider your script. My script is a mile long!  Yours looks much more compact

It's a simplified script, and there is also the setup on the VL table to read out the data that's not shown directly in this example....

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

Thanks

Steve

script.pdf

Edited September 1, 20169 yr by Steve Martino

The virtual list technique can be quite handy of course; but I'm wondering why you didn't just treat this as a standard sub-summary report?

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

@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 September 2, 20169 yr by Steve Martino
typo

@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...)

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

Steve

I haven't wrapped my head around repeating fields, but 'repeating' variables to create an array I'm fine with.