Jump to content

Creating a Virtual List Report


Steve Martino
 Share

This topic is 1794 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

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

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

Thanks

Steve

SS1.jpg

script.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
typo
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

Steve

Link to comment
Share on other sites

This topic is 1794 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
 Share

  • Similar Content

    • By DPaquin
      Hello,
       
      I do want to upgrade from Filemaker Server 12 to version 16.
      At first I've wanted to uninstall v12 but cannot access to the console. 
      I get screen saying "Cannot start the Consol, managing  Admin Server not available?
      Does anyone have an idea of why I cannot start the FMS 12 Consol.
       
      Thanks!
       
      FMS-ERR.tiff
    • By candell
      FYI I tried to add this to the Filemaker community page but don't think it worked hence adding here also.
      I have been tasked with creating a website for a charity, part of the website will be a searchable archive of all of their historic data. This data is stored in Filemaker Pro 12 on the historians machine.
      He has provided me with 4 .fmp12 files, I downloaded a trial version that I found here http://www.filemaker.com/redirects/ss.html?file=PRO12MAC however none of his files will open.
      The website runs on Wordpress which is php based, the database is a maria database although I am happy to use whatever method I need to get this data on to the website and searchable.
      I was expecting to open the files in the trial version, export as csv and create and import in to the maria database.
      Is this possible with filemaker, do you think I would be better downloading a trial of the latest version, will his files open ok in the latest version?
      Unfortunately I have no experience of filemaker so am struggling a little.
      Thanks for any advice anyone may be able to provide.
    • By JH Howland
      Filemaker Pro Adv 12 running under Windows 10.
      Trying to "Send Mail" with two (2) attachments.  The specified attachment files are defined as $Path1 (from_map.png) and $Path2 (to_map.png).  Both files are located at C:/Users/{user id}/AppData/Local/Temp/S<n>.
      The Specified files are entered as:
      $PATH1
      $PATH2
      However, when Send Mail is activated only $PATH1 is shown as an attachment.
      Can't seem to get past this problem, help please.
    • By Kishan_Canoo
      I would like to have options to enter either fields (selling price or margin) and the other should be automatically calculated for instance:
          
           Cost Price = 100 (Available)
           Sell Price  =  Calculation field or ?? Input amount
           Margin     =  Calculation field or ?? Input percentage
          
           If i put 160 on sell price it should automatically put 37.5% in margin or if i put 37.5% in margin field it should automatically fill sell price for 160.
      File uploaded
      Login name: admin
      No Password: 
       
      Thank you in advanced.
      TEST.fmp12
    • By John Link
      I have FileMaker Pro 12 installed on four computers on my network, and FileMaker Pro Advanced 12 stored on a fifth computer. There is a shared database stored on one of the first four computers. When using the shared database from the fifth computer and I attempt to check any of the checkboxes for the field Events, I see the message shown in the attachment and I am unable to check the checkbox (This behavior occurs only on the fifth computer). If I then do a search, after doing the search I am able to check any of the checkboxes in the previously problematic field. I've restarted the fifth computer and the problematic behavior stills occurs.

      Any suggestions?

  • Who Viewed the Topic

    1 member has viewed this topic:
    ejay7650 
×
×
  • Create New...

Important Information

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