Main meeting discussion thread:
DIGFM: Virtual List on Steroids (5/10/2018; Santa Clara)
Link to video: DIGFM: Virtual List on Steroids (5/10/2018; Santa Clara, CA) - YouTube
What this session will not be: A long winded introduction to virtual list, though we will spend about ten minutes reviewing the basics at the beginning.
What this session will be: A fast-paced, advanced-level session looking at a variety of ways virtual list can transform the way you present information in FileMaker.
- Flexible framework accommodates complex challenges
- No need to tamper with table or graph schema
- Easily combine data from unrelated tables
- Faster development time
- Speedy run time execution
A major selling point is that you can design complex reports and charts (including ones that would be "impossible" using standard FM reporting techniques) without defining additional fields in your data tables, or adding extra TOs to your graph... this of course is huge if you work on live systems. Heavy lifting is done at the script and layout levels.
While the overall focus will be on reporting and charting, I'll be showing some other uses of virtual list as well. We'll also take a look at the "Multi-Find" technique and why it can often be preferable to "Fast Summaries", ExecuteSQL and other array building approaches.
About the Speaker: KevinFrank
A frequent presenter at PauseOnError, DIGFM, FM-DiSC, and other user groups, Kevin Frank has been developing FileMaker databases for nearly 30 years. He hosts the popular FileMaker Hacks blog, and resides "behind the redwood curtain" in Arcata, California.
Sorry about the audio & video quality this time. If you like the availability of this video or find it helpful after watching it, let us know. A few people went though some trouble to help provide it. (Notably vince's valiant efforts)
Some useful video time signatures below:
- KevinFrank started shortly after 7pm and wowed us.
Much of what he covered is in his most recent blog posts on virtual lists:
Virtual List on Steroids | FileMakerHacks
Virtual List on Steroids, part 2 | FileMakerHacks - 0:01:16 Virtual List Benefits
- 0:02:00 Virtual List in a nutshell
- 0:04:00 First Example
- 0:08:30 Admitting There's a Problem
- ... Other examples ...
- 0:25:25 He showed a great trick to get ever growing report widths to fit on one sheet of paper without having to redesign layouts:
Define a custom paper size (once on the developer's computer); produce a PDF on any user's client or server even; magic.
PDF Trickery | FileMakerHacks - 0:35:00 Typical Sub-Summary Report Vs. Typical Virtual List Report
- 0:36:00 Fast Summary technique
- 0:39:33 Multi-Find technique
- 0:44:50 = @7:50pm: Played with Wolfram Alpha data. See wolfram | Search Results | FileMakerHacks
- ... Other stuff ...
- 0:55:15 Break. vince.menanno laughing while juggling recording devices. What fun watching Vince run out of space on his iPhone and then turn his laptop around to face Kevin and the screen projection so the camera would see them. Kevin stops in the middle of what he was saying and says: "What are you doing?"
- @8:15pm — In relation to virtual lists, covered Thinking About JSON, part 1 | FileMakerHacks
Formatting JSON will increase processing time. E.g.: 28 seconds unformatted vs. 38 seconds formatted. - 0:56:20 = @8:25pm — Script trigger to clear big global variables for building virtual lists: OnLayoutExit
- ~1:03:00 — Perform script on server advantages: fast and you can fetch data with you don't want the user to be able to see in entirety.
- ~1:13:00 — Don't let FileMaker auto-assign JSON object elements.
- ~1:19:00 = ~@8:45pm — Virtual lists for custom sorted value lists!
Check out "Byte Order Marks"
Virtual List on Steroids | FileMakerHacks
It’s Sorta a Value List Thing | FileMakerHacks
Credit goes to Soliant. - ~1:30:00 = ~@8:55pm — See the auto-adjusting one-page rows in Release Agreement[new]
- ~1:37:00 = ~@9:05pm — "display a customer’s artwork in a separate “lightbox” window in either four-column…"
…or five column views.
For these results, see Virtual List on Steroids | FileMakerHacks
Look for the JSON joke here: Thinking About JSON, part 1 | FileMakerHacks
I noticed I can search for a lot of these topics on Kevin's site and find examples or explanations:
Timestamp hyperlinks generated by YouTube Topic-Timestamp Tagger (YTTTT):
a few facts about me 30 years experience certified as clients I'm the technical lead on new millenniums Genesis transactions product I have a blog is that like a Rubik's Cube it's like a looks like it's fun and I have been using virtual lists for about 10 years which was before it was invented because I was doing something I didn't know his virtual list my goals for this presentation everyone gets something they can use soon I'm gonna keep it fast-paced because there's demo files and I've already written extensively about almost everything I'm going to show the except for JSON we're gonna do that the second half so we're gonna look at some stuff and then we'll take a break at about midpoint for like 15 minutes and then when we come back I'm gonna do the virtual list with JSON I'll kill there no filler show don't tell and so start with a very simple example just kidding okay virtual list benefits flexible framework and we'll see some examples of this so it's fast and it's especially fast if you use it with VSOs on a wham this is big and all of these things will make more sense because we're be looking at examples of these but so one of the big benefits is you don't have to touch your schema in your main data files combine data from unrelated tables easily and in some cases that reports themselves will be faster to develop than traditional FileMaker reports virtual lists in a nutshell locating data you're looking for data locate parse your data present your data locate parse render ok populate some Barratt one or more variables and then view it okay to suit bear this in mind because there are many ways to do some of these things how many people saw the movie full metal jacket thirty years ago Stanley Kubrick anyone same one remember it it's basically two movies maybe the first half is utterly fascinating it's set in boot camp hey the second half is this really cheap really generic Vietnam film but I often think about that because we're going through this step especially it's like a whole different realm of knowledge and challenges than the rest of it and I don't necessarily care how you locate your data just get it parsed and virtual list will display it so bear that in mind I'm going to gloss over some of this because I've written really extensively about it but I will show methods might be SQL it might be fast summaries if you're familiar with that technique if not I'm going to show a little bit of it soon and also there's a technique called multi find that in many cases is faster so the way Kevin just to interrupt yes I'm recording this I hope that's okay with you yeah III audio and video okay so I think what I'd like to do actually is um assure you that I sort of blundered into virtual lists but I didn't before it had a name and before Bruce Robinson no never mention anything but all I saw was a really narrow application for it I didn't have any idea that it had a wider a wider application out in the big world so what it was was I was working for some real estate attorneys and they had these massive leasing contracts and they wanted to pick and choose which paragraphs would appear under certain circumstances and these things were like 60 pages long but they had a template stored in a FileMaker file in a single field well the problem was we used to have a hundred announced four hundred and forty inches but it used to be one hundred and ten and a hundred and ten and well divided by eleven you've got ten pages right so they were trying to print a sixty page document based on the contents of a field but they could only print ten pages because of the layout length limit so I said and of course I can't show that so what I can show though is a simple little demo I built so I said what if we had something that was really long like this public domain book from Project Gutenberg and what if we could turn that into a document we could print what would we need to do okay so I'm just gonna go into preview minute as you see that was very quick and now here's all the pages and you know eventually if I pull down there we'll eventually get to the end 42 and so what's going on behind the scenes was what if we took that and threw it into a variable then we'll just call it a double dollar send virtual list there it is so it's the exact document it's the contents of the field but now it's in a variable and then what if we had a table called virtual list let's get out of and what if that table had a whole bunch of records in it quote more than you'll ever need so say ten thousand or it looks like twenty five thousand is what I did and what if we had a serial number start with one up to 25,000 okay we could find the number of records we need based on the value count of the paragraphs the value count of what's in the field or in the in the variable okay and we could define the paragraph - this is just some fancy formatting to get the title to work but basically we could say give me the value of the paragraph corresponding the serial number in the table okay so the story of the door turns out to be the sixth paragraph we're just counting hard returns right so paragraph could be one word or it could be you know hundreds of words okay and so and this worked out perfectly this is this is how we so this is how we solve the problem for the attorneys does it lose the formatting I had to do some CSS tricks no it doesn't lose most the formatting when centering is the strange one the alignment alignment is the oddball because our normal design functions don't work for alignment so when used to get his CSS which is and I think long documents in FileMaker and so is that the first time I ever wrote about virtual lists but I didn't know that it was virtual list it was just a way to solve a problem yeah so there was some little get a CSS trick going on here and I just had to do that was just to get the title to work properly so but all the other formatting holds just fine anyhow so that was uh that was my first brush with it and then I promptly forgot about it and then when Bruce Robertson started talking about vertical list as iPod name I need this I don't understand it's good but then I started finding times when it might come in handy so I've got this file here called the admitting there's a problem and so here the deal is something like this actually I'm going to open up one other final first very good okay oh and the reason you're gonna see updating sample data a lot is I try to keep the data current I have a script that just creates records to so that it's always now it keeps the old stuff and it keeps adding new can you zoom in thank you are zooming one more level or when you would like - Omid magnify oh yeah perfect okay so we have a simple sales table is that shy going further that's good okay so a simple sales table just a few fields and some of them are foreign keys and we've got a simple summary field just just to sum the amount and on the graph it's about what you would expect sales yeah it's real simple the types of the zones sort of related but so we're just storing IDs there and the chance but I wanted you to see how bare-bones that is now the challenge was to create a report that looks like this and obviously is multiple ways to skin this cat but when you go to do that in FileMaker if when you as soon as you want to start adding parallel columns you often find that you're building helper fields or relationships to make this happen and you know Beca I'm gonna zoom back in when anyone asks me to but I'm gonna I'm not able to see everything I need to see if I do that so so this file called step one admitting there's a problem shows a sort of traditional FileMaker way of solving that problem which is well first off let me show you that it works ok so there's there's there's a little report right and to make that work I created a whole bunch of calculated fields I created parallel summary fields for them I had to make a year field so that I could do some sorting on that and so I basically more than doubled the count of fields just to make that one simple report they're not stored no no but still tampering with there's just something feels very wrong to me about tampering with the schema in your main data tables to have to do something like that also if you do live development you'll know that's a big no-no if you users are actually in the system by the way if you do live development it is table specific so if you go after your virtual list table to do something although if you set it up right it won't do it very often but you don't have to worry about messing anybody up it's specific on record creation and record editing that if you do blog development and you go in and change a field definition or add a new field Tomica blocks the table and then if a user tries to create a new record at that moment that step will fail so I was in a situation where I was doing some live development so I was incentivized to find another way there are some other things I was incentivized for first but in terms of quote admitting there's a problem I just wanted to that's the purpose of this demo it doesn't show any solution it just says we had to add these fields to make this report work now the reason I'm in the LR part two instead of part one is part two is just a superset of part one and VLR stands for virtual list recording so so let's let's run that report again and I'm going to talk about what's going on behind the scenes actually 21st show my virtual this table so this is more complicated than the table we saw a minute ago because it's got a whole bunch of repetitions it's also got a couple different field types in it it's got a number and it's got text I'm going to first I'm going to first show the report running and then we'll get back and look at that so just move that over okay so report report one and by the way so is a single virtual list table and so what you can see what's happened is these fields are popular the reason that populate is they're unserved calculations and they're looking out at variables when I did this I decided to use single dollar sign variables so they would clear themselves alright as soon as the script ends that means I go into preview mode and our I got positive script maybe I generated PDF and then as soon as the scripts done running they will be gone so those might hang around till I click you can see them and so when I'm gonna run it again and if we take a look in the data viewer we do that well I guess I have to display the date of your first okay so what you can see is I just generated a whole bunch of individual variables and I will point out that the call that the column number represents the repetition here because the repetitions in the virtual list table are columns okay in the virtual list table the rows are actual records and they have a serial number that starts with one so in the virtual list table I've created quote more records than I'll ever need in this case I've got 10,000 and I could always add more if I needed to but but that's enough for these demos and then so I've got this 150 repetition cell Nomar and I've got this 150 repetition cell text R and the the repetition you're seeing on the variable corresponds to the row so column 5 equals cell number 5 here okay and list it so that's so now let's take a closer look at the definition the definition is the same on sell text our and sell num artist the reason I have both is sometimes I just want to use field formatting on my report so it can be useful if you need to format something as dollars to just use standard FileMaker number formatting on the field and we're gonna look at the report and layout mode in just a second but is that yeah alright so so what we have happening is quite simply I'm using the evaluate function and I'm saying okay you've got some variable and it starts with dollar sign Co al right and then I don't know if you guys know this but repeating calculations can be aware of their own repetition number using the get calculation number function so this is this is really huge because it what it basically means and then over here I D it's worth referencing the serial number to know the row okay so essentially I'm able to treat this virtual list table as sort of like a spreadsheet and I can think of each individual rectangle as a cell and so the cell has a unique address that corresponds to its calculation repetition number and the row that it's in in the virtual list table okay does anyone know why I've got that bracket one there repetition it's the first repetition ID is not a repeating field but this calculation has a hundred and fifty repetitions so I could have said extend ID right because that's the purpose of the extend function is to make non repeating fields show up to all repetitions in a repeating field but I just prefer array notation so that's the equivalent that's just the same as saying extent okay but the big thing is I don't care which repetition I'm in of this calculation I still wanted to go look at the non repeating I I feel to figure out which row it's in so so that's the definition and I've got one and it's result type is text I've got another one and it's result type is number the caps identical it's just the result types different they're on stored and it's very important that they be on storage sometimes I'll do a date also if I if I'm showing dates because I might what I did the reason I'm doing this is for formatting I want to I want to have my cake and eat it too I'm going to be off in this esoteric virtual list realm but I want to be able to use my final egg or tricks as often as possible so what do I mean by that I'll show you I'm going to go into layout mode for someone close that okay and in thank you so and actually by coincidence this report is all numbers it's just numbers there's no text in this report so I took cell num and I put repetition one of Selma on four-year and I put cell no repetition to total sales three four five so the question might come why are you making this so darn complicated why not just use separate fields and the simple answer is I might want to change the definition and I want to do it 150 times I would do it once so that's the main reason actually another thing is if I want summary fields on some of these I can define one summary field with a hundred 50 reps pointed at cell Nomar and every single rep will sum the column correctly so and in fact I've done that in this file which is right there right so this comes this this comes back to so to be clear we're not looking at a summary field they but if I do want to summarize the data I can use a summary field this is a filemaker table I can use a lot of my standard reporting tricks in this table so questions is that yeah so so if I'm going to use well then we'll see this but if I'm going to use cell text to that I'm not going to have anything in so not because remember it's the same variable it's either text or a number yeah let's take a look at Oh and just pointing out in my so I did all that the virtual miss table if we go back to the sales table over here it's quite lean right I will say this a lot of times you will go on a standard summary field for because that's gonna feed what you send to your virtual list table but every every field that's here with the exception of the summary field is there to support the data okay and so I don't think that has rules here but my my preference is to not pollute the schema however like a lot of us I'll say oh except when I think it's okay let's let's take a look at another one this is to so this is the same table of data but now I've build up my total I've got this and and now we have the total down now we have the total of each column also so there's a couple interesting things going on here it's still we have so much showing in text this is all pure numbers what I'd like to show you is number okay so there's your cell non-repetition one you're selling the repetition - what's down below here is that summary field I mentioned a second ago so here I'm going to trailing we've got a trailing grand summary here it's just in other words even though this is virtual list I can still treat it like a FileMaker report I can put summary fields on it I can put trailing grand summaries but your standard model for virtual lists will be that you have a body once in a while you'll still do some trick where you've made a virtual list but then you also just put a summary part and don't bother with the body but generally speaking you parse the data it's sitting out a bunch of variables and now you're going to render it using the body of your virtual list table so yeah so here I've got you know cell number three on through whatever the rightmost one is and in each case I've got the corresponding summary right so when I go back to the manage database and the field options here I just want to point out summarized repetitions individually right so each column is it's why I had to define one summary field I've got some table as well so I have an average or a count or some other things also what you can do in the same thing in every case you can just say summarize them individually okay so another thing I want to point out about this is notice how on most of the column headings I just put a piece of tax system as the label but over here I put a emerge emerge variable right and the reason I did that would scroll over but let me turn the reason I did that was let's let's look at our data table for a sec I've had two different tables of data in here two main data tables ones sales data and the others went visits and so so far we've just looked at sales data if we come over here to wet visits it's a completely different table it's just hypothetical these are a number of people in each state who visited us visited our website and so if I go to report number three what you're going to see is it looks exactly like a report number two but notice total web visits sorry it's been that up notice this now says total length visits and before it said total sales so the upshot is I was able to use the same exact report the same exact layout even though the data came from two completely different tables because I was doing the same thing with it another thing I want to point out is have you ever had a situation where you've built this wonderful report and you managed to fit everything on one page maybe you've got a client who really likes to see everything on one page and then they ask for another column right and they complaining that it doesn't print about this so if you follow me perhaps you may already know this trick but this what we've done here this this was a problem fitting all these columns on so what I did was I define a custom paper size eight eight and a half by twelve instead of eight and a half by eleven okay when you when you define a custom paper size it's pretty interesting that FileMaker does okay so first off I know that there are zoom settings you can do in your printing also but a they don't work on a PC they be one of the things I like what I'm doing is I like in my filemaker layouts to be able to show the page border at the right and know that it really is the right-hand border of the of the layout okay so I'm file instead of my notes uses the custom paper size trick landscape sorry landscape eight-by-twelve custom paper size so here in the print setup doesn't matter what your printer is okay and then oh you know you can set this on a Mac I'll just say it I'm not gonna I'm not going to belabor the point but you will set up a custom paper size using the tools on your OS to do that and then and then once you've done that FileMaker internally just keeps track of the of the coordinates so what that means is your users don't have to have a fake custom paper size on their computer of 8 by 12 in half by 12 but and you it's completely portable once you've set it up you can do that so you define the paper size once and then you can apply it in your in your print setup and a it will be respected in preview mode order to look like that but be when you output it to PDF this is I tell my clients we're not in the print business we're in the PDF business when you output that PDF it'll look perfect but when you go to print it on eight-and-a-half by eleven paper it'll really nicely scale it down so that's been a godsend for me because I've got certain clients that continually ask for more data but keep it to one page so and before you we've all been there right okay I'll just take it from six point type down to five point type you know and then I'll just scrunch everything or not used to dance Texan island so and this has nothing with virtual list reporting of course but I do think it's a it's a good little trick to have in your tool kit if you make PDFs custom papers science is saved with your file I think it's just that the print setup step says here are the coordinates internally regardless of whatever UI you use to set up the I think custom paper size is a convenient metaphor if you will but internally FileMaker just seems to be saying these are the dimensions that you're gonna print out because you can distribute that file anywhere Mac or PC and it will work and those people you give it to aren't gonna have that custom paper size that you set up but to get it started you have to create the custom paper size within on your development machine then give me an 8 by 12 piece of paper right yeah but it's not like unchecked by default and by default done right but very elegantly yeah it looks it looks clean but it is true that is an option in people who turn it off and if they did the trick wouldn't work but I've never seen anyone turn it off and my clients love it and it's really allowed me to just kind of do do things say yes to request the loads that say I don't want to spend two hours doing this you know now it's just like okay new custom paper size okay now I've got an extra inch to play with boom and just run with it so it's been tremendously liberating you mean yeah I think what he means is if you generate the PDF on the server like export as PDF on the server now I haven't seen a problem I'm not sure if I put it to the test but the I think it's not being a problem because it's still it's internally storing those but in that case you're not making a PDF you're actually talking directly to a label printer that's correct yeah or what internally when follow makers tries to do is it interprets even though you're playing with the margins it'll still make some interpretation where the and it will automatically do the cutoff and we'll print fewer rows and you have labels so you fake the paper size feed it the real paper size just to keep to make FileMaker print out all of the rows and everything prints out just right hmm so this is basically I think a take technique like that with the PD oh yeah that area yes yes although we can say I'm going to the OS but the truth is I'm so staying in FileMaker opening a print setup and then clicking through to the place where you make a custom paper size I'm not having good right but yeah and I think that's why I didn't readily see it here because I set it up on the Mac okay so you know I was I was gonna say I I haven't ever asked Bruce Robertson why he called a virtual list because to me it should be virtual table the point is I now have this magic table and I do all kinds of wonderful things with it and like so it's absolutely my go-to if you know like some weird requests comes up and okay virtual list yep yep so and yeah so if you go to FileMaker hacks there's a page with like massive number of links probably the most hyperlinked page I've ever built in my life yeah virtual table but I don't think I think virtual list is going to stick is the name and so it's so a couple of the things I just want to say about virtual list I'm going to show a lot more examples but you know you get to do the heavy lifting with script maker which to me is the most powerful tool under toolbox I think that's really mean I be not exclusively the spear maker but you were doing a huge amount of the lifting there as opposed to defining a bunch of helper fields which is how we often would have saw these problems before another thing I'll mention is everything I'm gonna show in the first hour is single dollar sign variables so they auto clear but we'll look at some things with double dollar sign variables and why we might prefer those under some circumstances so let's see yeah I guess what else did you say if you've ever talked to if you're a music buff and you talk to the music buff and you're like wow you like Miles Davis I love Miles Davis and then you find out you're talking about different decades and you have like zero interesting common except that it's Miles Davis and similarly there are so many variations on this that you know I could show a variety but I just wanna say this is really a collection of techniques I think maybe the one thing that we can all count on is there is a virtual list table okay and and there's probably a ton store caps in there so let's so I'm gonna so so so far I haven't really sure no I'm locating the data and I think that's worth doing so you know fresh it up I'm going to show a little slides here okay so this is a this is just sort of an overview of how you do a typical sub summary report right oh yeah I got the feel of it alright help her relationships - you know how many times we clogged up our graph to make our one report so yeah we I think that's pretty self-explanatory but yeah we do a lot of heavy lifting on the design side and then the execution is typically fine the data sort and show you a report right typical virtual list report the first thing you notice is one step class but you create the report layout based on the virtual list table you write a script and then during the execution you locate parse the data that's being push it into one or more variables and then render that on the layout the reason I say these steps may repeat multiple times is this is where I'm going to talk a little bit about two different techniques one's called fast summary how many people use fast summary anyone okay and the other ones called multi fine which I'm guessing very few people use so I'm going to show them in a in a demo file and what I did when I built this demo was I built a whole bunch of versions of it with different records and the reason is the performance is partly dependent on the size of your found set honest the other thing it's dependent on of course is local hosted on a lamb or running server side with PS OS so by the way there's a technique in here about global relationship it's really limited because it doesn't handle arbitrary found sets very well so I'm not even going to show that this it's it's kind of a one-trick pony for when you want to report on everything at your table I just threw it in there mainly because I wanted to bench but so the weight fast summary works is how many people use to get summary function okay so using get summary it's gonna jump from group to group you're gonna sort and it's going to jump from group to group once per group rather than having to loop through all the records that's what makes it fast but of course if you have a lot of Records or if you're on a LAN it's still going to take a while to move through those so I'm going to generate this report using fast summary and what this is going to says it's actually based on a on the demo that I've built more than ten years ago mikael Edison he this he was when he came up with this and the other thing I like guys just the idea that you can build a report and put in a text field and so what this thing is done is it's it's basically gone through and just looping and using get summary but it also figures out where it's going to go next and I'm not going to be laboring it but I'm just going to say the nice thing about that is you can just have a bunch of data sorted and then point fast summary at it and it'll jump from group to group grabbing the aggregate each time of course it needs to have a summary field to look at and it uses get summary to to then counted so I actually had two summary fields one being the amount and the other being the count okay so and I on the website have all different amounts of file sizes but here I just fights or arbitrarily grab the ADK so what's nice about that is you don't need to know a whole lot about your data you just need to know you just need to know what field you sort it on because if you use again summer you know that's going to be your break field okay the multi fine technique first let me run it I may be getting some additional slowness because of the this the zooming tool our music thing so this is this has basically crunched the data come up with the with the same answer did it did it faster and what it does is it builds a list of the IDS of the customers and then just finds each one it iterates through the list and just doesn't find so it finds and then it doesn't need use get summary because it's got a found set it just checks the summary available and that's part of what makes it's also enough to sort so so multi find under certain circumstances can be faster and one of the interesting places where it can be faster have you have you look sorry benchmark no no have you updated it with silica are you getting the unique values of where to like go find which customer because you have a flattened list of which customers so let's say out of 80,000 records you have like 30 rows that you end up creating because out of all that data there's the unique index of you know this customer that customer that customer so how are you getting the unique values is what I'm asking okay so well first off we might be more likely to say hey I'm starting from whatever found said I've chosen to construct it's arbitrary it's not he's not the whole file right right so I would probably have a summary list field okay okay and summer a list of summary field pointing at the customer ID Owens and then I would use unique values no file maker function to get that down in just a unique list right that would be found stead of where okay then so but the upshot is and but the upshot is is that you you make a list of those IDs and then you loop through them and you know if there was other criteria you needed to us to respect than you would but this this technique under certain circumstances is considerably faster including now I benchmarked in 14 but notice the gray is the fast one and notice what's happening server side right multi find is far faster than the other methods on if you're gonna use PS OS hmm so I'm just sort of skipping over that because I think this is pretty well documented in blog articles I've written and the demo files are also available yes so the no-no if you've got a found set you just look at the summary field and it will give you the answer for that found set yeah yet so yeah because it's a report right we're not breaking we're not sorting so did no not at all not at all that's the beauty of it because picture this right let's just say you've got two customers right each of them at 50 records right so you've got the orders with just two customers a hundred order entries right but so you do a find on the first customer ID now you've got 50 records and the found set right the summary you just look at the summary field that gives you the the total is only indication where you have a real summary part and you don't need a summary or I'm not saying the only time that you really trying to render it on a layout directly in a more traditional fashion you have to have the right to summary right it's because the sorting gives you the opportunity to break it by each sort group whereas he's doing that by finding and you're getting just that blob the summary represents all the found records is that does that make sense yeah yeah so another way to put it is this right is it if you've got a hundred customers I'm gonna do a hundred fines but it's fast to brief my breaking rates but fine this also means that this also means I'm not even need to sort my virtual list report right I've already got the data sitting off in these variables and what I just lay out the body robe on is gonna find the things in the corresponding variables etc right okay so I'm actually going to skip to to the final demos in here because they're a little different did anyone see this story the other day okay so we were six now we've surpassed Great Britain that's because so one of the cool things are a little from alpha is it's you know it's sort of like Google foreknowledge and it gives you things in a very useful format and once you've got them on screen you can click this funny little button down here it says plaintext copy of a plaintext and then you can get that into your clipboard and do within FileMaker if you pay them you can actually get an API which probably uses JSON and then and you know get that directly you know from inside filemaker but if you're just looking to grab some data to you know do a kind of static report on throw it into a table you can copy it and then use it so what i did was i got their list of states and i got their list of countries because we've got a it's funny almost everything i'm showing you has come from the real world but i'm making up this example the hypothetical boss says i want to see the ranked list of top 20 economies in the world and the data has to be current right so so here what we've got is a state's table and with the data courtesy of Wolfram Alpha and Wikipedia and we also have a countries table okay and I'm also part of the GDP is in billions on both of them okay so we've got the populations I've got the GDP you might look at two different tables right but you're looking for is a report that looks like this yeah I know I took the states and I made them read so they would stand out we take a look at what's in the well it's very cool no so why don't let me step through with a debugger this this is kind of interesting just because this really illustrates that the data can come from different places and then you can mix and match it the way you want so so I made this thing called wrong list and Rob lists this so execute SQL can have its place sometimes it bogs down when the court wrote a good article recently about when it's appropriate to use it but when you've got a total of less than 300 records in your state and your countries table combined it's probably a safe bet for execute SQL so so the first thing I do is I as I grab I since Oh the challenge was to show the top 20 so I grabbed 20 from the country's table and I grabbed a 20 from the state's table and I'm doing the sorting with SQL I'm using fetch first to just get 20 and if it's a state I'm also foot making it red with the text color function so now I've got the same column list and raw list is going to contain 40 entries okay but it's not properly sorted by economy it's sorted first the top 20 states are the top 20 countries followed by the top 20 states so it's not quite what we want but it's it's it's a good part of the way there and so we're still in the debugger [Music] so it was in Iran list and I and I parsed it into simply column one in column two right column one for the state name and column two for the GDP also oh the reason the reason it if you could see that divided by a thousand up there the reason that it was that was that they wanted it in trillions on the report even though it was stored in billions so I'd like sequel do the math okay so then what I'm doing Ravi see is the raw that is the but great value count of the raw list which was the original 40 entries so now I'm saying in remember I got ten thousand records in virtual list so this this is actually a huge part of the virtual this technique is doing a search and saying give me the records that are less than or equal to a certain amount so like I'm only going to show a maximum move I'm going to end up with 20 but I've got to start with the first 40 because I haven't sorted them correctly yet so so this idea that ID by the way is the one indexed field in the virtual list table okay so there's a number of tricks we can do with it but I wanted to point that one out in particular so I've got to get in the correct found set of Records so I'm gonna start with 40 so that idea is this it's a sequence number yeah yep do not use a UUID there okay now I'm going to descending now this is pretty cool right this is in virtual lists I'm gonna do a descending sort on the on the column with the GDP ah Report BC is it was saying let me have that I only want 20 in the final report that's what's in the report BC variable which is there so what I'm doing is I'm saying okay basically go down and record 21 and then the old it will meant a whole and impossibly large number to get rid of everything else because where we want to end up with is just those 20 so first I rendered it into virtual lists not properly sorted then I sorted the virtual lists table so once again where's the grass greener and sometimes something like I'm saying what's virtual list but I'm still gonna treat it with all my FileMaker reporting tricks so yeah just go ahead and sort it there so that's what's happened and as you saw it's very fast and so now we know for sure but what the answer is just in case there was any doubt because absolute data is on store accounts right which appear local even though you're in the same record and working with the same record you're not actually entering the record right capsule is stored so each user's got their own and yeah [Music] through anything else we should say about that are there questions I know I'm kind of zipping along so I'll just point out that in the locate right I used to execute SQL this time instead of a multi find or a fast summary all right well I'm going to show one more then why don't we take a break so I have to say I started doing this a long time before JSON so I'm now doing it with JSON and we're gonna see that in the second half but I very consciously needed to make potentially lots of variables and as you guys know FileMaker doesn't give us a set variable by name step so there's still but you can still dynamically instantiate variables you can still invoke a variable with a programmatically determined name you can use it do it using let they let double dollar sign go out a block or I wrote a custom function because I was doing it so much and it was specifically for virtual lists I was doing it in virtual lists I was finding that I was having to just sort of I was already in a loop it didn't make sense to hard-code the variable names it made more sense to have a counter somewhere in Santa Fe there's column column and counter is one then the next column in increment the counter and just keep going like that so the customer ID and I have a blog posting called set bar by name per set variable by name but anyway it's just it doesn't matter what's going on inside of it but I will say this about it if you've ever done this with let one thing you'll find is you'll earn a lot of back slashes and escaping things I purposely brought the custom function to minimize that so if you want to say something and then you want to put one plus one in there you just put one plus one you don't have to late escape it etc so it does I do find that most of the time I can just throw the syntax into it in a very FileMaker like way and get what I want out of it so I've always a little leery when people are like use my custom function I'm like but no I want to understand it if I use your custom function I won't understand but in this case it saved me a ton of time so I'm glad I did it okay [Music] actually I'd like to take the break down if that's okay yeah all right so then we'll look at JSON and also using double dollar sign variables and why we might want to do that at the calculation in the virtual disk table it's not that much more complicated than what we saw before today but I'm using JSON yet element remember the system repeating right 150 wraps lots of rows and this calculation is you know the part that we already looked at and then we've got using JSON get element to essentially extract just what it needs and you can see get calculation repetition number minus one to get the zero but we didn't need to do it on the row because that starts with soon so I'm so now instead of making hundreds of variables or thousands I'm simply making one right for the the JSON array me and another thing I'll point out that's kind of cool about this about all these demos that that say JSON in their title is you know the big problem the blocks I'm variable you call those global variables is you have to do garbage collection all right all right they don't clear themselves right and so so what we've got here is a well first off let's let's show the data viewer so here's the data viewer and what we've got is three variables for each report once the sort order and one switch field we're sorting on and once the array okay so that's so there's nine I've just highlighted nine variables in the and we're on the current tab now watch what happens when I close the window there's one there goes the next one and and the third one I'll show you this trickery so essentially the us with trigger and the script trigger says trigger clear windows specific double dollar sign bars and bigger rear window specific bars and it's basically just reading only no name and clearing the variables associated with their name oh one other thing if you do with their names there are certain characters that are forbidden in variable names such as a high ISO minus sign is a big one so but I'd like to use hyphens essentially so so it was I just found like an end - or something and swapped it and using the caret function and so just be aware of it is if you do use window names or any time you dynamically declare a variable there with certain characters that aren't allowed and you can see this if you ever just use the regular set variable thing and you pointed put in the - for instance it will pop up a dialog with about 20 things you're not allowed to put in a variable name so just if you're going to dynamically instead like you're right upset bar by name just be aware there's certain ones that you can't use but the only one that I'd routinely trip over is trying to use a - so I started using a whatever option in with V on a Mac instead the scores are okay okay I think it was the - I think I like my window needs to be pretty look July - I see that you see that that's an end - because I couldn't use I okay so what's the trigger so it's in the demo file but but it's probably trigger clear window specific bars vallejo trigger only an exit I think yeah Oh glad you asked that I think it is only have exit but if you like go into layout mode and then close the window the trigger roll around so that's fine if we're talking about three bars but if you used this technique and you've got hundreds of bars and you're expecting them all to clear you can end up with the big mess hmm yeah right just close the file obviously but the so the question was what is the trigger on layout exit there it is yeah boy one of my favorite things about this dead zone outside the active part of the layout is just to put notes networks your customer uh yeah I'm using because I'm dynamically here in the bar right I don't know what its name is it's just whatever the window name is so yeah oh this is kind of interesting so notice how I've got this little note here it says rep 9.6 can you see that okay okay that's because the one above it is six but what happened there was to get the mark up it was easy enough to get it on every individual row but to get it on the tonal was taking too long and what I was trying to do was to grab that you know the summary value and then divide by the other summary value but um but what I found was if I had a lobby when my virtual list before it got long than to do the math especially like on a wham no true in any rate regardless instead of my original plan was take this number development take this number divided by this number okay and that would be my markup and that would be technically true but it was taking too long so what I did instead and I think this is just a good it's not so much about this specific thing is just this general idea that what I did instead since I was a multi finding or anything or whatever I was doing sorry I'm having trouble seeing it there we go there are these two can everybody see that mmm okay so what I did was um I just kept it running in total and so that's you can tell that's multi fine boy because I'm doing a fresh find at the top of each at the at the top of each and then so I just built my running total in memory and then and then I set it in two not the the array position corresponding a repetition nine and so that's that's the general principle would be sometimes in your virtual list reports you'll have like columns one through six or whatever visible all that report well then you'll do something out there and some further out there we got 150 reps to play with or whatever number you choose to define you'll do some other trickery out there and then just stick one one thing on there from like column nine or something just in one place that's fine right there's no there's no rule that says you have to use them all in order that's fine def gaps and sorry okay so I apologize too much caffeine it Suzanne small if you like me with high-octane stuff [Laughter] [Music] let's get to number three number three is posted in FileMaker cloud hey now we're talking about something running on a wham okay and as you guys know well you may know if you ever deal with landhaus the databases certain operations can be much slower when you're on a web okay so I don't want to waste our precious minutes necessarily showing you these things but for example to show all my customers from July of 2017 took 153 seconds using the scripts that we've already looked at right so okay so 153 seconds that's two and a half minutes and to do just the internet customers was 55 seconds okay so that's just if I just did what we've already seen but now we're up on the cloud all right so by internet july 2017 generate report and it's singing you want to perform the script server side or client side if I did the clients I could get 55 seconds mm-hmm what happens if I get server that wasn't nine seconds I think it's time from the beginning of your script so it's six seconds so that's pretty dramatic right yeah that's amazing okay now if I were doing all these separate bars right how would I get 300 separate bars back from server you wouldn't want to write Ehsan little JSON array right one thing returned is the script was off from the server right so I'll also point out right so this virtual list right I I am what I want to say about that okay it's fast there's another reason to do this on the server and it's not in this example so much but here's where it is let's say you've got an accounting system or some system you don't really want your users opening but and maybe it's multiple files but you know you've got some digital assets in FileMaker you don't want your users opening them client-side but you'd like to get a little bit of information out of it and show it to them alright well with PS OS you probably know whatever your credentials are on the client side are what are used when you open something server side but there's nothing to stop you from Ramadi in server side all right so you could rely in with like the reporting privilege without find your data and return it back so this is actually been a big thing for me in the last month or so with one of my clients is doing stuff for now users are allowing like for instance they decided that certain users were allowed to see the balance on to bank accounts but they didn't want them just going through the accounting and looking at all the bank accounts right and then we didn't want them to pull it open the accounting files which are in FileMaker on the on the client side so we just go off do it all on a server and if you I don't know how many of you have used PSO us almost every one about math 2/3 ok so if you've used PS OS you will know this but if you haven't this will be a little stumbling block for you the first time you use it and so I'm just going to show you you can almost tell a PS OS user because they will have this little test at the top of their startup script ok and so what's sorry that's a that's the hallmark of a PS OS friendly system right there the reason being when you Coffee SOS it's going to open the files it's going to run Leon open script or the startup script whatever you call it right so the point is you can't prevent it from running it but you can ensure that it immediately fails out and the consequence of not doing it is a lot of times you know with PSUs you have to manage all your context like tell it where to go and you know maybe recreate a fountain set etc so um but uh so you don't want your normal water open script you can really screw things up and things they're not from the wrong context and that actually reminds me of the other I hadn't been saying I thought there was another benefit of using virtual list with PS OS in this scenario and that's quite simply this you're gonna find the data on the server now in a standard filing a report how would you return that information like would you like somehow like recreate the found set client-side to run the report see what virtual list we got to do that we just returned the JSON JSON comes back as a search result just as if we in generating the JSON locally except way faster if wrong man so I find this really timely right JSON virtual list Bob Iger cloud or any plan hosted solution right that's really compelling because reports take a long time right here talk about summary reports and aggregating data they take a long time to run on the web but if you elect direct is strictly a server-side operation but I I thought that you can't pull stuff from other files unless you've already had them open like yeah I'm not true no like I'm not doing it now okay so I mean you know I and soon Genesis account he files this like 20 files and I've got users that I do not want opening any Genesis account but I need information from Genesis County and they need it now they don't need last nights that I harvested and prepared they need it now so that's that's the place for a certainty that's not the only place I use it but you know because it's other kinds of important aspects just sometimes people have a need to know a really narrow little right piece of it right I mean you you I guess you have to ensure they're right because you read logs we get you right yeah that's when I will point out every time you call PSRs it opens everything so you are taking time to open things on the server but that's a lot faster than opening from across the web let's see um are there questions the questions okay already yeah so what happened there was my auto clearing that did its job and cleared it but um but those variables were just the same that's what we looked at a minute ago exactly the same right mean so I might just um just point out one when I'm doing the so I got my report called customer summary and then it gets to a certain point and then it decides if it's gonna run it server side or client side oh that's the weird thing about that be blog and a lot of times when we're debugging these scripts when we're debugging what's going to be a server-side script will instead just simulate it locally so we can do the real log in there but that's just for testing so I don't really care we're just voting out is one thing to do a Remagen on the server and then everything closes it compares it's different if you just did the reblogging client side because you were testing worried like you screw up and then let them do it yeah that's not going to be good so but that's fine because the whole point is this is the so this thing oh by the way so I had to pass three parameters to the subscript so I use JSON for that also and so they said I intend when we gonna talk about JSON objects our JSON arrays this is a JSON object okay and it's got name value pairs year month and tight and I've already put them in variables which is why they're exactly the same here oh well why shouldn't that be looking at this because I want us to point out something else about JSON so if you do a number but it's blank like you've pulled in JSON number it will come out as 0 not nothing right well I've got fine criteria and I'd like to just say hey take them on let us all there's no month number good night set this to JSON number it was looking for you for months 0 so that's why I made that JSON string and then I put a comment to the same way the other thing I'll say about theses my first thought was I don't think eyes know but if you leave that blank file maker will Auto assign the JSON type mm-hmm and so first I thought what what could possibly go wrong with that okay well what can go wrong with it is FileMaker does an extremely unique lackadaisical job and so for example if your if your string is 1 2 3 Main Street it will come out as just 1 2 3 if you let filemaker ottawa signings it'll go oh i see a number it must be number just like Excel yeah yeah so the - the upshot is I would be proactive right don't don't I've seen people have published technique files and then you just look in there encoding their JSON with quote there it's gonna come back to haunt them so massively so I mean most thing if you're feeling lazy just make everything a string cuz then finally it's going to consume it ultimately it's gonna get to be what it needs to be but don't just end a string as code one by the way a number it's true - those are the two I mostly use hmm yeah they're gonna encoded an array with in JSON so we just number four which was JSON they're ready but yeah so a lot of people you will see they'll just pull one when it's a or two when it's a number and one when it's a string that's fine I mean that's that's part of that's part of the definition of how it works on the other hand why not be explicit that you won't have to wonder what you were doing so the point is I was passing the script I was passing three sub parameters so I just encoded them here the script on the client side just turns them back into variables right it just looks at the JSON at all there's three things here Google defines them on the client side that are on the server side is whenever I encode it so shouldn't should we look at the server side script sure why not right so that's that's what I was just talking about right you come back the other side right I know that I'm gonna get your month in tight I'm expecting that so I just use JSON get out let's say hey give me give me give me the year from the script forever give me the month okay because those are name value pairs so and then so all I'm doing is back here on the server side I'm reconstituting those three variables that existed on the client side when I set it up because that's where I entered my criteria this is about context management right server side doesn't know what your found set is it doesn't know what you're serving layout is it doesn't know about any of that right so you have to manage all your context on your server side script okay so on if there are any further questions about that there's two places we could go I've got 15 minutes left just it's just you know it's it's uh yeah but you know what's what's cool about it right is it's a way to pass state value pairs yeah right I mean I'm sure a lot of us would have engineered and experimented with various methods to get multiple sub parameters within a single sprite there's only one script for Hatter but we can do what we want with it but now Jason gives us a very clean way and everybody's going to learn it and I just want to point out whenever you're using JSON you're not just solving your current problem you're building knowledge that you're gonna use for the rest of your life and I'm not exaggerating so let's report the script above it report summary customer summary as that's the column strip that's the Cohens okay because JSON strings that could come back have you ever stored them not yet but it's a great idea right because I have to say when we think about apart from I have is a lot of times I'll work as part of a team and maybe there's somebody else building other stuff so I can't always trust that they've got the integrity to never let old data change right but you know like in a lot of systems you can say hey if it's more than a year old it's not changing and I can just store JSON or some other static representation when people go to mine that data we don't have to you actually rerun and ask the question because the answer isn't changing yeah yeah reserve your report you can do that happy if you just save a PDF it you're going to say yeah just save the JSON and run it back into virtual list you can say mom baked the heading into not even but whatever you want JSON right and it's I mean it's rigorous it's not you know it's not sloppy so so I have a ton more different kinds of virtual this things that aren't reports that I could show in the 13 minutes or where we had left or we could talk more about JSON so does anyone's so I could go either way okay that was the advertised purpose of the yeah of the premiere okay um well this was I'm just gonna show you some of the things I did this this is what I did very recently I blog about it but um so if you already read this sorry how many people saw this blog article it was called a custom field base value lists anyone okay so this this is this was actually a real-world need to solve a very specific problem which was the client wanted to put their text they wanted to have a setting screen where they could enter some return delimited things but they but they cared about the order so what happened was like here in this season's field right if we view the index it's going to go alphabetical right and of course we know value lists are based on indexes right so but the problem is fall spring summer winter isn't the correct order of those seasons right and I know that one could actually make a separate value list table and then store things and then proper IDs or use byte order mark tricks or something that but let's but let's take it that we actually wanted to have a single column valueless just based on the field not a two column valueless where they hidden ID right okay and so that one so that so the problem was and then here with the years he wanted to see them in reverse order not in other words if we look at the index it's going to be a sending of course mm-hmm right but or here from the phases ready aim fire not aim fire ready okay so so here we've got some dropdowns and they're actually in the desired order they're mirroring the fields how on earth so if I caught this table virtual value list and only gave it a hundred records all right so what this is doing is whichever sorry whichever one I click on it's storing two versions of the text one is raw which is the actual text that you'd want to store in the field the real the real text with no tricks the other is using byte order mark do you guys know what those are okay some of you do okay so it's a zero width character and because it's zero width you can shove 50 of them whatever number you want in front of the value and no one will see a thing hmm if you then sort by Unicode you can then like foot so in my case ready aim fire I've got one bite over here in column two I've got one byte order mark there it's correspond to the ID we've got two here and I've got three here so so I'm there force forcing the sword to go the way I want it okay so one thing it's kind of interesting about this is let's see how much if I if I click on a different if let's click on the season oh that's funny if they didn't completely update there it goes so what I've got is I've got a little it turns it out like the fastest thing in FileMaker if you're trying to get something interpreted by the calculation as quickly as possible is hide object it's like the first thing that evaluates is the layout drawers so quite literally I've got a hide object Cal on these three dropdowns not because I'm going to hide any objects it just as an excuse to invoke the variable and so the I'll just I'll just show the one on the phone show the one on the first one on oh you're way over here and the height object count is let the array doubled outside because we want to work in browse mode right without a script running by the way is no script okay it's just a hide object count and I'm not dynamically naming the variable it's always called double dollar sign array right and then I guess pointed it to whichever of those three value list fields the the original text fields I just pointed it whichever of those I want okay so so the other two hide objects caps are identical except this would be low to 403 the reason I got a zero here is I don't actually want to hide anything right enough so I mean completely hijacking the intended purpose of hide object just to give me an excuse to populate a variable and so so what's interesting about that is just how many credits remember there's no script right it totally works I come over here I'm getting this one I come over here it's like on-the-fly swapping out what's in that variable and then on the back end you finish okay and and even though it's not redrawing until I click it really has to be drawn so then the first question is what the heck's going on I call them to text right problem one text is column one text is give me give me the value from the array corresponding to the ID at my virtual list so in this case one two or three sorry I can't see it at the moment column two text says runs a custom function called prepend bomb which just says give me this many bombs and put them in the front and there is our ID so in the first row is 1 in the second row it's two right I know that you can download the demo and see it I know it's a little esoteric like worse right because we've got the original order that the person typed in that gets thrown into the variable so now we've got rows 1 2 3 they correspond to the ID numbers and the ID numbers tell-tell this calculation to prepend the bomb right so the value list is the as and it's one valueless virtual and it's just saying give me common one which is what I'm really gonna store but only show column two okay because call them to swen I'm going to display in the value list that's important because we want if the user clicks back into the list for them to see the checkmark we don't want to store some bombs we don't want to leave our data by storing bombs but it works and you don't even and we don't want to strip them back out with it with a with a strip trigger because then we won't see the checkmark we will check mark to be there right so and it renders what I mean by the check mark we talked about when I made a choice here well I've got a check mark showing me right so I really there are no bombs going into here because I'm storing all in one but I'm showing column two okay so anyway so that's that's us steroids you see yeah right so I could do this but I mean you you unless you needed assorted for a particular order you don't need to use the byte order right but you know it's funny I have like these presenters they give math workshops around the US and Canada the owner's wife needs to come first time this but our name isn't first that's we're doing the fight order market rate same thing story the ID but the display has to byte order mark so put her make sure she's at the top but yeah a standard value list we do what you want because you wanted alphabetical by name right so you're a store the ID and then just the index will naturally put the names we don't need virtual list of byte order marks or anything right right so I want to I want to store that and what did it click on ding oh right so what gonna do is you make that you call them by listing only share second column is base it on your customer list or whatever whatever those things are coming from perfect yeah what character using for the Bible remarks let's see what the definition of custom function is it's something pretty high you know I tried some other ones because there were other things that are resumed with but then they leave artifacts hmm so that was some lion who came up with that by the way I can trim each guy it was but prepend bomb and 275 702 ah gee whiz yeah same technique to dynamically build value lists like there's a really smart Belgian developer named Andres Halen and he like back in 2012 her biggest article for Mike gave me a demo file and which I put on my blog is from magic value list the difference is he wasn't getting the sorting that I'm getting here he was just but he was able to create value myself like one value list and boom it's just all over the place dynamic calculating on the fly hmm using the same technique yeah he's in the same technique as the high no no but I'm saying he's in the same technique of using the piggybacking off the hide calculation and it works here's the demo check it out this was a located stable oh okay so I wouldn't post the showing demos but I wanted to show a real definition so I'm using virtual lists a lot of when I'm an extremely demanding bookkeeper and she's got very spoiled because for ten years I've always said yes to her so the anyway I just want what this is her virtual list table this is the actual definition of it and I'm really using JSON on this project yet but because this is a lot of legacy stuff but I just want to point out right what we do in the J's in our virtual this table can be laid out specific right there are custom functions or you can only need a custom function to calculate to get the internal layout ID I have one going to see if layout ID but there's it's not hard to get right 500 design functions will give you the layout ID so although it's a little more there is no get layout i.t function which acquired into the custom classroom but um but the upshot is so that means that like different layouts I connected for virtual listicles remember it's the virtual this table but I've got lots and lots of layouts based on that table right so so the point here was not to understand exactly what's going on but the point is simply to say you can make it very complicated and specific to individual layout IDs or whatever other criteria you might want to use and or maybe it's a portal you know maybe you've got a portal pointing at your virtual list table that's you know that's another possibility there too [Music] this was kind of cool I think this is this is not the fanciest thing but but it was so kind of interesting so the reason why is it seems like it shouldn't it seems like there shouldn't be a problem that needs to be discussed all right so it's a release for photographers and videographers and they've got a they just want to show these columns right can you everybody see that it's okay they want a form right but these four columns on it like this but they want the amount of text at the top to be something they could type in setting screen or something whatever it is they want to have the text followed by the form followed by the gross okay but then only they only want one page so if they put a lot of text they'll just have fewer of us right so you I think it most FileMaker developers if you said that they'd be like yeah how hard could that be sure I'll do that for you the problem does anyone know why it's a problem you can just pull and there's no trick that's the problem okay so that whole shrink you know also shrink in closing apart hmm doesn't work right so here we go if I do it on a standard header it's gonna look like this okay first we've got sorry it's going to look like that and then down at the bottom maybe I'll get a row if I'm lucky my good I left a lot of room more than a half for both texts right so let's let's let's put some more text in there okay so now we've got four paragraphs all right so on the standard header once again we have all the text right but that's not working so that's that's a cui you know if we take the same exact layout I know I've looked at the layout yet we Chinese internet layout what we do something different to it but some of you will know what it is okay this is the shrinkable heather version and now voila there it is right with the four with the four things I just put in there right so in this case I just thought you know I need a table with some rows I'll use my virtual list table I'm not doing anything with virtual list except it's just a dummy table so I can have some rows okay so this isn't exactly virtual list thing but is just virtual list was handy I guess the point is it's found like my Swiss Army knife I got a problem hey there's virtual list let's see if we can use it so um so what's going on behind the scenes is quite simply we've got not a header but as okay we've got this thing this release text which I just threw in a variable it's set to shrink as you can imagine but it's not a header it's a sub summary and I just sorted it by a constant because I don't care all I need is some excuse to make the sub summary drop I have a constant field in the head by virtue of this table that's positive so creative negative constant how about that right equals one right so I just sorted on that so I can get the sub summary part of leaving some summary to draw because sub summaries do shrink so when thinking a header then the over then that was so that that was most of the technique the the remaining piece of the technique was when we saved the PDF right so here I am if I pass the parameter one go to the standard layout the broken one otherwise here that to the good one and notice in that case it's it's a constant and it's a sub summary I'm having to sort sorting on my constant okay and our preview but now if do we want to make a PDF yes when we make the PDF option specify options records being browsed specific pages wanna okay because that's all they wanted was a one-page they mean so you know I might have 40 records showing my virtual list table which would normally make it spill over onto a second page every but I don't know how long it's gonna be because I don't know how much they put in there special text but I don't care I just do more than I can use and then I just say save save the first page right right you can use specified there so you can send in a variable you don't have to hard code it oh sure sure but in this case you know that our use case is route in nature taking pictures and then there happens to be some family and you might use those pictures in advertising so we try to get there to sign the release just look and see if this one where I've got some pretty cool stuff here and I know we're just about out of time you guys have been very patient yeah I'm going to show you this last thing here because um I think it's pretty cool this so years ago somebody posted a question of like okay I've got these customers can I make a three column value list right or three sorry recall them list view and it turned out to be really easy using virtual lists and since we not that I don't know how many versions back it was made around 14 or 15 18 to 13 we got the ability to hide the current record indicator at the left of the list so I got this thing you know but so that's done that's done with virtual lists and when I did it I was just thinking of this really narrow use case so I like hard-coded it for three columns but essentially what I did was I just made it that there were three keys each one was multi-line pointing at different customers and that worked but then I got a request and they said you know we've got these artwork and it's sitting here in a portal and we started it I'm sorry that's but all we've got is these tiny thumbnails wouldn't it be nice and Here I am gonna zoom back down would it be nice if we could have a lightbox and you know for fun let's have it be four or five columns and when we click it up pops our artwork in either the four or the five column view with some additional metadata if you go some data about the image so not just the image but some some data also and yeah and so and what I realized was that that technique I was already using for the one thing for the three column customer list could be very easily adapted to this and made more dynamic so so what's interesting about this is I'm gonna sorry let me let me let me let me bring it back up and let's take a look at the variables and then we'll be done I think that's sure the data viewer first okay all right so I think that's leftover from the customers I'm getting punchy guys so this is the last one all right so I pop up do that let me show you the graph instead all right okay so right I get it now so what I ended up doing was I made virtual columns and from here I then have relationships either to my artwork or my customers he has necessary right so each of those virtual columns will just or a stack of IDs corresponding to that column position right so I'm using virtual lists now not to hold all the data I'm using virtual lists to just store selective stacks of keys corresponding to columns and if we look at it layout mode we've got the four column light box here and as you can imagine right we've got the relationship right relate just the related image from the different relationship depending on which column it is are those in portals for this event so numbers I always say that's a virtual this row oh right okay we're looking at seats the body mm-hmm what we're looking at us even though it's based on virtual earth this days I call that virtual column virtual call but what we've got there is the related data and it works fine I mean so so if we were to look if we were to look at the virtual list right our tools data viewer and take a look at let's see [Music] all of one right then we would see a single ID for whichever road we happen to be on and for whichever of course whatever column they are so soon so I made these dedicated fields column I do one and I'm only using the first five and each one is the primary key for a relationship but the point is they're populating themselves based on variables that it said and so it's reading and virtual list so the upshot is in this case where I'm using virtual is to render the data directly we're just using virtual misses to hold the correct set of keys so that it can use relationships and show their related data and I think on that happy note I'm done so the conclusion is arrays are cool and JSON is cooler thank you [Applause] |