I think I'm virtual listing wrong

[ddreese]

Hey guys I came back from devcon this year looking to try my hand at some virtual lists to possible make our solution a little more elegant.

What I want

What I'm trying to do is populate a single portal with a client's activity (that comes from multiple tables) and ultimately make the portal records "click through" so I can then jump to a particular record . I thought I could possibly do this with virtual lists, but now I'm not so sure it's a doable solution. Currently on the client screen we have a bunch of portals showing their activity in each table, which is really clunky and an eyesore to be honest.

What I did

I have a Client table, and initially it was related to my temporary/scratch table set up for the virtual list records by a cartesian join so all the records would show. I had the temporary table setup with an index field (1 to N basically) and the unstored calc for the virtual list to pull the data from it via getvalue ( $$virtuallist; index ). There was an onRecordLoad script trigger running the script that went to the different tables and performed the finds and then populated the global variable (my virtual list).

Oh crap that didn't work right

The problem I immediately caught on was that this is in no way a multi-user solution. Since it's a temporary scratch table anyone looking at a client's layout would be shown whomever the data from the client who was the last person to go to that layout. Not a good solution.

A bit of duct tape later

So my workaround was to have the temporary/scratch table be linked to the client by the client ID, so that client's information is the only thing that get's deleted/reprocessed instead of the whole table. The unstored calc is now gone and it's an auto-enter calc field to add the info at the time and leave it in there. It works, but I think I'm somehow missing the point of the virtual lists? Not to mention I still have to stuff an identifier in there so I can then do some somewhat complex parsing trying to figure out where it came from and how to find the right activity table to start with.

Tail between my legs?

This really get's me thinking, should I even bother? It seems like maybe the virtual list technique is best used for displaying text on the screen (or for printing), but not anything that you want to interact with. It's like filemaker needs an entire global table where it's temporary/scratch data for just that user.

Am I missing some core functionality? Am I missing the point of virtual lists? It seems to me, for my specific purpose, that it would be easier just to build a "dumb" script to:

goto services A > search for client activity > loop > copy IDs and dates and descriptions > goto scratch table > paste current service type, activity information > loop if more than one record for that activity > otherwise goto next service table and repeat process

But it seems that I'd be losing the speed advantages of using the virtual lists by doing this, though I haven't tried it out yet.

[comment]

Well, perhaps it would be better to put all activities in the same table - possibly with a satellite table for each sub-type.

To do it with a "virtual table", I believe you'd need to populate a variable with a return-separated list of all IDs (can be done without looping by using the List function). You also need to count the number of related records in each table, or - to make it simple - populate another variable with a return-separated list of the records' table name.

In the virtual table, have each record get the two corresponding values from the above variables, and use them to retrieve the real data from the correct table.

[ddreese]

I oversimplified the different "activities" for sake of a brief explanation, the different tables I'm pulling from are all very different.

More background: We're a social services organization that is fairly unique in that it offers mental health, mental retardation, and children and youth services all under one roof (and different levels of each). You can imagine that each of these has their own data requirements and structures so one table wouldn't really cut it.

Thanks for your input and the virtual table recommendation, I think I'm going to have to read it a few more times to see if I understand and it accomplishes what I'm trying :)

[bruceR]

Hey guys I came back from devcon this year looking to try my hand at some virtual lists to possible make our solution a little more elegant.

What I want

What I'm trying to do is populate a single portal with a client's activity (that comes from multiple tables) and ultimately make the portal records "click through" so I can then jump to a particular record . I thought I could possibly do this with virtual lists, but now I'm not so sure it's a doable solution. Currently on the client screen we have a bunch of portals showing their activity in each table, which is really clunky and an eyesore to be honest.

What I did

I have a Client table, and initially it was related to my temporary/scratch table set up for the virtual list records by a cartesian join so all the records would show. I had the temporary table setup with an index field (1 to N basically) and the unstored calc for the virtual list to pull the data from it via getvalue ( $$virtuallist; index ). There was an onRecordLoad script trigger running the script that went to the different tables and performed the finds and then populated the global variable (my virtual list).

Oh crap that didn't work right

The problem I immediately caught on was that this is in no way a multi-user solution. Since it's a temporary scratch table anyone looking at a client's layout would be shown whomever the data from the client who was the last person to go to that layout. Not a good solution.

A bit of duct tape later

So my workaround was to have the temporary/scratch table be linked to the client by the client ID, so that client's information is the only thing that get's deleted/reprocessed instead of the whole table. The unstored calc is now gone and it's an auto-enter calc field to add the info at the time and leave it in there. It works, but I think I'm somehow missing the point of the virtual lists? Not to mention I still have to stuff an identifier in there so I can then do some somewhat complex parsing trying to figure out where it came from and how to find the right activity table to start with.

Tail between my legs?

This really get's me thinking, should I even bother? It seems like maybe the virtual list technique is best used for displaying text on the screen (or for printing), but not anything that you want to interact with. It's like filemaker needs an entire global table where it's temporary/scratch data for just that user.

Am I missing some core functionality? Am I missing the point of virtual lists? It seems to me, for my specific purpose, that it would be easier just to build a "dumb" script to:

goto services A > search for client activity > loop > copy IDs and dates and descriptions > goto scratch table > paste current service type, activity information > loop if more than one record for that activity > otherwise goto next service table and repeat process

But it seems that I'd be losing the speed advantages of using the virtual lists by doing this, though I haven't tried it out yet.

The virtual list table is not temporary or scratch. It is a permanent table and would be used as the basis of your portal.

Since the data displayed from the virtual list is unstored and is based on the global variable, it IS multi-user safe.

[ddreese]

The virtual list table is not temporary or scratch. It is a permanent table and would be used as the basis of your portal.

Since the data displayed from the virtual list is unstored and is based on the global variable, it IS multi-user safe.

Wow, yep, you're definitely right. If I follow correctly I just want to put a bunch of blank records (except for the "index") into the table, and that number of records pretty much equates to the maximum amount of data that can be shown. So if I think there will be people with 100 "activity" records, I'll want 100 records in there, correct? And those 100 records will never be added or deleted. That thinking is what led me down the opposite path, I believe.

Thanks, that definitely makes much more sense

I guess all that's left is I'll need to make a parsing script so that based on the portal row that is clicked, figure out which table it should be in and how to get to it. Excellent :)

[comment]

the different tables I'm pulling from are all very different.

They can't be completely different, if you want to show them in the same portal. They must have some fields in common - and those fields could be in the same supertype table. It's actually the same thing as having a "virtual table", except it's not virtual and doesn't need to be produced ad hoc.