Virtual List - Example using Styles

[imoree]

Hello everyone;

i am trying to figure out the virtual list technique by BruceR, but all i see is a writeup on FMLAYOUT.com. Which to be honest with you doesnt make too much sense.

questions from html page i have: = from site http://fmlayoutmode.com/wp/?p=1799

1. Then you need to do a replace on the index field for those records, using a serial number, starting from 1: "Ok, but are you gonna be doing this everytime or can this be automated, if so, scripting?"
   
2. In Browse Mode on a layout for the VirtualList table (not the printout one we created), you need to create a whole pile of records. I created almost 20,000 records — well, okay, I created a script to do it for me. "Ok, thats fine and dandy, but that means the field; displayVariable cant be a calculation, it will have to be a text field that has calculation enabled?
   
3. I am just very confused by this article in general. Perhaps it is not for new persons coming to filemaker! Perhaps.
   

Can someone either give me some links or perhaps BruceR's direct comments.

thanks,

-ian

Edited December 6, 2011 by imoree

[Ocean West]

ian

Check out http://seedcode.com/next/

also i have a few files in the download section that utilize the virtual list technique.

[Fitch]

Then you need to do a replace on the index field for those records, using a serial number, starting from 1: "Ok, but are you gonna be doing this everytime or can this be automated, if so, scripting?"

You only need to create the virtual list records one time. But you could create only as many as needed as part of your process, and add to it as needed, or create from scratch each time, or whatever. The article describes one typical approach, that's all.

In Browse Mode on a layout for the VirtualList table (not the printout one we created), you need to create a whole pile of records. I created almost 20,000 records — well, okay, I created a script to do it for me. "Ok, thats fine and dandy, but that means the field; displayVariable cant be a calculation, it will have to be a text field that has calculation enabled?

What makes you say that? It is indeed a calculated field (that's kind of the whole point), the author provides the calculation in the article, and you can download his file and see for yourself.

I am just very confused by this article in general. Perhaps it is not for new persons coming to filemaker!

Definitely not for noobs. :goodluck:

[bruceR]

Hello everyone;

i am trying to figure out the virtual list technique by BruceR, but all i see is a writeup on FMLAYOUT.com. Which to be honest with you doesnt make too much sense.

questions from html page i have: = from site http://fmlayoutmode.com/wp/?p=1799

1. Then you need to do a replace on the index field for those records, using a serial number, starting from 1: "Ok, but are you gonna be doing this everytime or can this be automated, if so, scripting?"
   
2. In Browse Mode on a layout for the VirtualList table (not the printout one we created), you need to create a whole pile of records. I created almost 20,000 records — well, okay, I created a script to do it for me. "Ok, thats fine and dandy, but that means the field; displayVariable cant be a calculation, it will have to be a text field that has calculation enabled?
   
3. I am just very confused by this article in general. Perhaps it is not for new persons coming to filemaker! Perhaps.
   

Can someone either give me some links or perhaps BruceR's direct comments.

thanks,

-ian

The Virtual List technique can be used in many ways.

The general idea is that each virtual list record can display the Nth instance of some collection of data.

The collected data can be generated by a script; or by putting return delimited data into a global field; or into a global variable. (etc)

You describe creating 20,000 records; but you don't say why.

What you are trying to do with the technique?

The technique is an adaptable solution to a lot of problems; but it does have limitations.

I published two copyrighted articles in a now defunct magazine and so will have to create new articles describing the technique.

[imoree]

Thanks for all the responses everyone.

BruceR. Thank you for your wonderful technique as well. What i am trying to do is figure out your technique based on an article written by PaUl Spafford on fmlayout.com. he created 20K records and manually did a replace field contents,etc using your technique.

I was told i should look at this technique in order to learn more so i am, but the article was too hard for me to understand. NOw, Ocean West[stephen Dolenski] gave me John Sindelar's file to decipher so i am trying.

BruceR :: So sorry your article was lost in that now defunct magazine.. : -(

The thing is for someone like me who wants to learn , they are so many different places with loads of techniques, but there is no one repository yet for all the learned techniques in FMP.

-i

Edited December 7, 2011 by imoree

[imoree]

Can Someone Please explain this file?

http://fmforums.com/...desired-layout/

FOr me . I see a relationship from zStyleID(global)- --> StyleID

These are 2 tables i believe were added to perform the Virtual List technique

Vlist Table

has the

1. Index
   
2. Display ( Calc-- GetValue($$array; index)
   
3. zStyleID ( global ) - I think used to sort the list
   
4. zArray - DONT KNOW WHY THIS IS USED
   

Styles Table

1. StyleID
   
2. Description
   
3. Style ( Auto enter date ) Value=style
   

Gather Data

Go to Layout [ “TaskListByPerson” (Tasks) ]

Set Variable [ $$array; Value:"" ]

Set Variable [ $people; Value:"" ]

Set Variable [ $tasks; Value:"" ]

Set Variable [ $this_person; Value:"" ]

Sort Records [ Specified Sort Order: People::Person; ascending Tasks::PersonID; ascending Tasks::Task; ascending ] [ Restore; No dialog ] Is this necessary?

Go to Record/Request/Page

[ First ]

Loop

Set Variable [ $person_task; Value:GetSummary( Tasks::TaskCount; Tasks::PersonID ) ]

Set Variable [ $person_line; Value:Case( $this_person <> Tasks::PersonID; People::Person & Char(9) & $person_task )]

Set Variable [ $people; Value:List( $people ; $person_line) ]

Set Variable [ $this_person; Value:Tasks::PersonID ]

Go to Record/Request/Page

[ Next; Exit after last ]

End Loop Show Custom Dialog [ Message: "Show tasks by task name or person?"; Buttons: “Task”, “Person” ]

If [ Get( LastMessageChoice ) = 1 ]

Sort Records [ Specified Sort Order: Tasks::Task; ascending ] [ Restore; No dialog ]

End If

Go to Record/Request/Page

[ First ]

Loop

Set Variable [ $task_line; Value:Tasks::Task & Char(9) & People::Person ]

Set Variable [ $tasks; Value:List( $tasks; $task_line) ]

Go to Record/Request/Page

[ Next; Exit after last ]

End Loop

Sort Records [ Specified Sort Order: People::Person; ascending Tasks::PersonID; ascending Tasks::Task; ascending ] [ Restore; No dialog ]

//Here i believe the magic starts:

Set Variable [ $$array; Value:Substitute( $$bold; "style" ; "PEOPLE" & Char(9) & "# TASKS¶" ) &

Substitute( $$indent; "style"; $people ) & ¶

& Substitute( $$bold; ["style" ; "TASKS" & Char(9)

& "ASSIGNED TO¶" ] )

& Substitute( $$indent; "style"; $tasks ) ]

Set Field [ VList::zArray; $$array ] //We are now placing the $$array data (all into the field on our Vlist table?

Go to Layout [ “VList Report” (VList) ]

Enter Find Mode [ ] //is this another step ? we have to find the record and then sort it

Set Field [ VList::Display; "*" ] // Is this showing All FILES?

Perform Find [ ]

Exit Script [ ]

[LaRetta]

Hi imoree,

I cannot help you with that script (even after glancing at the thread). But once you grasp the concept of using a virtual table, it will be easy so here is what I wish I was told when I first looked at this technique:

You first create records in the virtual table for the largest number of records you will ever report on at any given time. You can include sub-script to test if number of records in your found set is too large and handle it by telling the User to decrease their found set (maybe more records in that particular report would be too slow and management does not want Users going over 50,000 records) or you can (behind Freeze Window), create additional temp records so you have enough before continuing your script. So now you have a table with IDs 1, 2, 3, 4 (be sure there are no holes in the numbering and never delete these records). You do this only once.

Now when script runs (maybe on a set of records in the Companies table), it loops through your record set, writing the information to variables. It is important that you do not have any holes as you write so if any field might be blank, test for it and write a single space as a placeholder. So to write $$id, you will end up with each ID in your found set as a line in the list. Your primary key won't have blanks so you don't need to test for adding a space but you might want to write other values while you are there and you can write them as well. As you loop, set the $$id and also set the $$type or the $$salesRep. Sometimes it is easier to write to several globals (on the single record as you loop) while you have the proper relationships established in Companies but it is not necessary. You can create a table occurrence group around your reporting table also. It is really a concept and not a technique.

Now create a calculation in the virtual table called cID with GetValue ( $$ID ; ID ). This makes the calculation unique to the user running the report because it is based upon the specific $$ID on the line indicated by the ID number. The ID on the fifth line of $$ID will match its corresponding Type on the fifth line of $$type and its corresponding SalesRep on the fifth line of $$salesRep. We use the virtual table's ID number to determine which value (line number) to use ... 'GetValue from $$ID list and get line which equals the ID number itself.

Use a separate file so closing it will clear the global variables or create a script which cleans them when done.

I hope this was helpful,

LaRetta

[imoree]

Very Precise & thorough LaRetta. Will try with what you have written.

A beautiful concept but wrapping ones head around understanding it is the trick.

-i

[LaRetta]

Here is a very, very simple file showing it in action. Turn on debugger and run it through. In the sample, I showed both how to set two variables with information (the id and salesrep) and also, if you attach an occurrence of companies directly to the reports table based upon the cID, you have everything you need just as a regular relationship.

modified file ... had an incorrect Set Field[]

SampleVirtual.zip

[imoree]

Very simple indeed LaRetta, but effective. The thing i think has me stumped in my prev. post was based on some sort of formatting that i just have to learn.

The Basic idea i think i understand though. Seems the context of the Report layout is based on that Virtual Table and those $$GLOBALS populate the id or other fields you need populated in order to run a report i suppose.

The OMit Record i am not sure i like that particular technique, but alas the concept is what's important.

Now back to learning. THANK YOU ALL Who helped me to understand and continue to understand this valuable technique.

-i

[LaRetta]

"The OMit Record i am not sure i like that particular technique,"

I cannot imagine why you are against using Omit ... they are invaluable script steps. Well, if you do not omit the report records which are not in your report, then how else would you suggest omitting them so your report only shows the records you want to view? ;-)

[imoree]

i am not against Omit, perhaps i am just not "Well versed yet". I am not thinking in Virtual List terminology!

Perhaps i am just thinking of relationships and using a $$GLOBAL.SORT.BY e.g

Again, i am learning and not an expert in any way, shape or form. JUST A noob as they say. I have some ideas in my noggin which need to be "practiced and tested" then i can answer that question OK!! : )

-i

[imoree]

Jon Sindelar is my hero: what an amazing way to do Virtual LIst. OMG!!

here is what i have done as a test and it works beautifully to grab data into a virtual list. dont know how / why this works, but it is amazing.

Contacts

# Function: Loads contacts into Vlist Portal

#

Perform Script [ “Clear Vars” ] # # # Draw a new offscreen window Freeze Window

#

Perform Script [ “Helper Window” ] #

Set Variable [ $$var.Mode; Value:"Contacts" ]

# # context First

Go to Layout [ “Contacts” (Contacts) ] # # Find the records

#

Show All Records

#

Go to Record/Request/Page

[ First ]

Loop

Set Variable [ $$content; Value:$$content & ¶ & Contacts::firstName & " " & Contacts::lastName ] // *pain in the but for about 1/2 an hour forgetting teh ¶ character(VERY IMPORTANT )

Set Variable [ $$varIDS; Value:$$varIDS & ¶ & Contacts::id ]

Set Variable [ $$Entity; Value:$$Entity & ¶ & "Contacts" ]

Go to Record/Request/Page

[ Next; Exit after last ]

End Loop

# # Clean uP!!

Close Window [ Current Window ]

Refresh Window

[ Flush cached join results ]

# #

December 8, 2011 16:33:38

vList_example.fp7 - Contacts -1-

VList Table has the following

number --> indexed ( not serial ) just allow indexing

Count --> Calculation ( Unstored: = ValueCount($$content) )

displayCalc --> calculation ( Unstored = GetValue ( $$content; number ) ) *uncheck to always evalyuate

Thanks again JBante & LaRetta and BruceR & Stephen.

[imoree]

testing9.fp7.zip

Hello Again everyone;

I have tried numerous ways, but i just cant get this to work and i cant figure out how i would put the information from teh virtual list into a set of repeating fields . i get the portal way < but omit records with what i have uploaded doesnt work at all as well. Can someone please take a look & tell me how silly i am, hours trying to "SEE" what i am doing wrong.

thanks

-i

[LaRetta]

1. Your virtual table’s ID should be type number not text
   
2. I have no idea what purpose RecNumber serves.
   
3. When you first clear your $$ variables, you are setting them with a space. They should be set to blank “”
   
4. You can drop the Refresh Window[flush cache] which isn’t needed and it is a very harsh action which should be avoided and only used as a last resort.
   
5. Your virtual list does not have an ID for each record; instead, you have written a multiline of numbers into the one number field.
   

It is best to name your fields closer to their purpose. The virtual table should be ID or VirtualID and it should be auto-enter serial. The first $$variable you create should then be the Customer's ID and your first calculation in the virtual table should then be translating this customer ID. I assume that is what the DisplayCalc is supposed to show but it should be GetValue ( $$ids ; number )

[imoree]

LARetta-SEE.fp7.zip

LaRetta; And here Laretta you will see no auto_enter Serial NUmber & it works just fine. I guess it just is what it is. So many different things that break the virtual list. I am testing various different ways to work with the virtual list technique and i will get better , just practice makes perfect.

i

[LaRetta]

In THIS file, you have the virtual ID number as a record (one number per record), as I described. You do not have it set up as auto-enter serial but you should. In the testing9.fp7.zip file, you had one record in the virtual table with a multiline of numbers and that simply would not work. So yes, I SEE.

Now I am not sure if you are asking another question nor am I sure whether I have even helped at all. ;-)

LaRetta

[imoree]

In THIS file, you have the virtual ID number as a record (one number per record), as I described. You do not have it set up as auto-enter serial but you should. In the testing9.fp7.zip file, you had one record in the virtual table with a multiline of numbers and that simply would not work. So yes, I SEE.

Now I am not sure if you are asking another question nor am I sure whether I have even helped at all. ;-)

LaRetta

Just saying the field only has to be indexed and NOT AUto Enter Serial ..

[LaRetta]

Ian said, "Just saying the field only has to be indexed and NOT AUto Enter Serial .."

Oh. Okay. ;-)

And I'm just sayin' that it cannot be a multiline and it should be serialized (one number per record in the virtual table) no matter how you get the numbers into the field. It is this virtual number ID which determines the values in all of the calculations which look to the $$variables.

[imoree]

LaRetta; Finally found the "EUREKA MOMENT" . At last I totally understand Virtual Lists totally now. At least i can replicate however i want to. ONly thing now is figuring out styles / display. Placing the data on the other hand i have down.

Totally kewlllllllll..... :yep:

[imoree]

Hello everyone; I have spent many a nights working / figuring the virtual list technique & applying styles for formatting. I am still just scratching the surface, but here is a test file for you to look at & see what i have done so far.

Happy Filemaking!!

-ian

VList2.fp7.zip

[Lee Smith]

Automatic message

This topic has been moved from "Community Resources → Articles, Tips, Techniques & Solutions" to "Database Schema & Business Logic → Calculation Engine (Define Fields)".

[imoree]

I am now walking down my portal , grabbing the data, placing it in a virtual list and displaying automagically for user to see up to date information via a Global FIELD>

This technique is REALLY AMAZING>>

-ian