 Hello and welcome everyone. My name is Ryan Manook. I'm a Solutions Consultant here at FileMaker and I'm really excited to be your host for today's Understanding Relational Concepts webinar where you're going to learn the best practices and possibilities for relational database design in your FileMaker solution. Today we're joined by Jeremiah Hammond, Senior Technical Lead at DB Services to lead you through today's topic. But before we get started I have some brief housekeeping notes. So for the best experience we strongly recommend that you participate in this webinar with at least a broadband connection. So if you have any problems or require online assistance at any time, please contact Citrix Technical Support at 888-259-8414. Now during today's presentation you're going to have the opportunity to type in and ask questions so let's talk briefly about how to do that. Just go to the control panel, click on the question section, enter your question and click on send. Now we're trying to answer as many as time allows at the end of our presentation but remember you don't need to wait until then to submit a question. And with that I like to introduce you to Jeremiah. Thank you Ryan. So I'm Jeremiah Hammond. I'm a Senior Technical Lead at DB Services. I'm certified in the past six versions 15 through 10. I've been working with FileMaker for almost a decade and I'm also a trainer for classes at DB Services. So the assumptions today, I am assuming that you all have created flat files in FileMaker and need to go beyond by connecting them together, linking up multiple files and tables. I've also assumed that you've read the FPS or at least you know where and how to create tables, fields, etc. Okay so topics today, we're going to go over identifying relationships and modeling relationships with keys. We're going to be creating relationships in FileMaker. We're going to touch on ERDs, talk about context and how important that is in a FileMaker relational database, and then we're going to briefly evaluate a couple popular graph methodologies in which to emphasize context. Okay so in this section we're going to discuss how to identify entities, attributes, and relationships from an example problem and then we'll touch on modeling relationships with keys. Okay so the example problem here. ABC Automotive needs an app to track their orders. You visited their shop and you gathered a list of requirements for what they need the application to do and here's the list. I need to enter and track orders with the ability to assign customers and products to an order and they need to track the status of an order, manage multiple addresses for a customer, be able to track the date of a customer's first order, have a dashboard showing actual items, and the ability to use an application on mobile devices. And so once you have this list of requirements together, you need to then determine your data model and that's what I mean by identifying your entities, attributes, and relationships between them. So we're going to go through some steps that you can take to get that list. So this step one here is identifying possible entities and this is pretty straightforward. All you do is take your list requirements and you identify the nouns. So I've done that here in blue, highlighted all the nouns. And then the next step is to revise that entity list. You need to determine which items represent a distinct type of thing that the app should track. That's your first step. So should the app track this or not? And there are some items in that list that we don't need to track. So you would remove those. For example, you wouldn't need to track a dashboard. That's something that's inside your application, that's not something you track. But you wouldn't need to track orders because they want an order entry system. So once you've gotten your list of things that you want to track, you then would need to determine which entities and which are attributes. So entities are, the way I like to think of it, they're a type of thing versus attributes which are the characteristics belonging to that thing. And so that, you know, once you have determined, you know, that distinction for each of those nouns, then you've got your revised list. But when in doubt, you should check with your client. Ultimately, in our case, ABC Automotive is going to know which things they care to track, which things they don't care to track. So that's going to be your end-all be-all. One thing you can do to help you with the distinction between attributes and entities is to leverage the spreadsheet metaphor. And I have a picture of that here. Now there's three different types of, there's three different types of terms, spreadsheet terms, data modeling terms, and database terms. The ones that I've been talking about so far, entity attribute and instances of an entity are the data modeling terms, and that's what we're using for most of this presentation. The terms that FileMaker uses are on the right, those are database terms, table, field, and record. And then the spreadsheet terms are on the left, so worksheet, column, and row. Now how can this help you with entities identifying entities versus attributes? When I was first starting out in FileMaker, I had been, you know, I had come from a world of spreadsheets growing up, and so my mind thinks that way. And so I would kind of go, okay, is this something that could be a spreadsheet itself, or is this something that would just be a column in a spreadsheet? And if my answer was, oh, this is just a column, then that's an attribute. But if it could be its own spreadsheet, then that's an entity. So for example, an order, an order could be its own spreadsheet. You could have an order, a spreadsheet that has a list of all your orders for the past year. So that should be an entity. But something like a date, like an order date, that wouldn't make sense to be its own spreadsheet. That should be a column inside of an existing spreadsheet. So definitely utilize the spreadsheet metaphor as much as you can to help you think through uncovering, you know, the difference between which things should be entities, which should be attributes. Okay. So here's the revised list from what we originally started. And you'll notice it's a lot smaller of a list. There's a lot less nouns than, you know, we had, we originally had highlighted. The ones that I had struck out, dashboard, actionable items, application, mobile devices, those are all either like environmental, those are either environmental things, or there are things that just don't need to be tracked in the application. So they were taken out. And as far as, again, if you're unsure, you can always ask your client, you should be able to take a pretty good guess, though, yourself, because the problem space, the application and the problems that you're trying to solve, should give you a good indication of whether these things should be tracked or not. So here's our list. And here I've marked in parentheses, which are entities and which are attributes. And so that's step two, we've got that revised list. So step three now is taking all of the entities and determining a list of attributes to track for those entities. If our revised list just had two attributes on it, an order date and a status, that is not going to be enough for the application to be useful for ABC Automotive. So you need to work with them to figure out exactly all of the individual attributes that you want to track for each entity. You can actually come up with some attributes yourself, again, knowing their business and knowing the problems you're trying to solve this application. And certainly you should go ahead and put those in there and then confirm with the client. But this is the next step after that. So yeah, those are the three general steps that you would use to take a list of requirements for an application and determine the data model, at least when it comes to entities and attributes. So to recap, you can see the recap here, you find the potential entities and attributes, you eliminate anything that's outside the problem space, just figure out which ones are attributes entities, and then flesh out your attributes list for each entity, and then you just need to revise and revise and revise. Okay. And so once you've got your entities and your attributes, the only other missing component to a data model is the relationships between entities. And unlike what we just had went through, which was a pretty systematic way to identify entities and attributes, identifying relationships is not an exact science. There is one approach you can take that will give you some relationships, it won't give you all relationships. So the approach you can take for some of them is to compare your list of entities and attributes, and then if you see an entity show up as an attribute in another entity, there's a relationship there. So the example I have is if you take a look there, let me go ahead and go to the screen here to show you. This is the same thing we saw before in step three. If you look, the customer is an attribute of order, and even though customer is also an entity. So what that tells you is that there's a relationship, a natural relationship between order and customer. So you can do that for a handful of relationships in your data model, and that can get you a good start. But there are some relationships where that does not work so well. For example, there's a relationship between order and product here. It's just, it's not here in the attributes. An order can have many products, and a product can be on many orders. So there's what's called a many-to-many relationship, and that's a valid relationship. So no matter what you do when you're trying to identify relationships, you do need to think about all your entities and how they relate together. These things will get easier over time as you do more, as you get exposure to more data models, but that's pretty much the gist of what you have to do. So that segues me into the type of relationships that you're going to run into. These are the two main types. One-to-many, which we have already got one with customer and order, and then you have many-to-many, which we just talked about with the ordered product. There's actually a third one, two, a one-to-one that's less common, and I'm not going to go into any more depth about that, but that one exists as well. Okay, now that you have your entities, attributes, and relationships figured out, the next step is to work your way to be able to implement those in a database application and to be able to implement these relationships unique keys. And there are two main types of keys. There are primary keys and there are foreign keys. So primary keys is a field in the table that uniquely identifies that record in that table. And in FileMaker, there's two main ways to make a primary key. There are serial numbers and there are UUIDs. The big thing about primary keys is that they should never change. Once that record is made, it should have a primary key and that should be its unique identifier for rest of its life. And primary key should never, never, never be empty. Best practice on primary keys, you should put a primary key in every table. It really, really helps for troubleshooting among many other things. And then the second type of relation, excuse me, of key that you will need to make relationships is foreign keys. So a foreign key is a field in a table that's the primary key of another table. And a foreign key plus primary key, those two things, allow for one to many relationships. So an example of a foreign key is a customer ID in an address table. So if your application needs, in this case, our ABC Automotive, they need to track multiple addresses for a customer. To be able to do that, you need to have a customer ID foreign key in the address table. All right, so revisiting the many to many relationships, you can't set up many, many relationships properly in a database system. And there's a reason for that. So to quickly go through it, based on what I had just talked about with foreign keys, primary keys and foreign keys, how could you get one order can be on many products? I'm sorry, one order can have many products and one product can have many orders. How can you set that up properly? Well, you could put foreign keys, multiple order ID foreign keys in the product table, like order ID one, order ID two, order ID three, but you're artificially limiting the number of orders that that product could be associated with. And then the flip side, you could try to do the same thing where you put multiple product foreign keys in the order table, product one, product two, product three ID, same problem. And so how do you fix this? The way to fix this is you insert a middle table into those two many to many tables. And this is the classic textbook example. You would insert a line item between an order and a product. And that would, instead of having one many to many relationship, you now have two one to many relationships, and then you've solved your problem. You now can have as many products as you want on an order by making line item records. And a product can be on as many orders as it wants to through line on records. Okay. All right, so now that we've got the relational design down, we've got our data model down, we know about keys, and we know about how to handle many to many relationships. Let's go ahead and talk about actually creating relationships in FileMaker itself. So I'm going to go ahead and jump into I have a template here. It's actually our free template that we offer called FM Quick Start. So I'm going to open that up for a second. Okay. All right. So the first thing we're going to do is we're going to pretend that ABC Automotive came back to us and said, hey, I want to add one more requirement to that application. I want to be able to assign or attach multiple documents to a customer. And you're like, okay, let me go through the whole data model part of this relational design part of this and figure out what these attributes that need. And you determine that, okay, we need a documents table, because that seems like that's an item, a thing in and of itself. So let's go ahead and create that document table. So I'm going to do that. And then we know that there's a one to many relationship between customer and document. So we're going to set that up with keys. But before we do a foreign key in here, we need to create a primary key because I just created a table and every table needs a primary key. So I'm going to do that. Don't worry about the naming convention. Everyone has their own naming convention. Just realize that's in our template here, that is this is how we name all the primary keys. Okay. So I'm going to create a UID. So I'm going to do an auto enter calculation here. Okay. So I've created the document ID, the primary key. Good to go. Every table needs primary key. And now I'm going to create a foreign key to the customer table so that we have the foundation to be able to create a relationship between the two tables. So I'm going to do customer ID here. And again, naming convention. This is just how we do things here, Deep Service. And we actually have an article on our naming convention if you ever want to check that out to understand what I'm doing. Okay. So we've got customer ID now in the document table. And I'm going to go ahead and just create a, well it's not called container, it's just called document. And let's make this a container field so that we can store any file that we want to in this field. Okay. So this is like the basic structure for a document table that is related to a customer. I'm going to go and save this and then get back into managed database. All right. So now that I've got that structure in place, got the table in there and I've got my keys in place, I now need to create a, the actual relationship between customer and document. Now, the way you do this kind of differs depending on the graph methodology that you have. And we have what's called an anchor buoy methodology, which we'll go into a little bit later. But for anchor buoy, if I want to make a new relationship, I first need to add a TO to that table. So I'm going to add a TO to the document table. And again, naming convention on anchor buoy, something you can read about on our naming convention article. And so I'm going to make a relationship here to documents. So I've got my TO. So now there's two ways you can go about actually creating the relationship because right now I've got the customer TO here and I have a document TO and I just, I now I need to relate them. One way is to press this little edit or this new relationship button on the bottom left. And then you'd go through and you'd select your tables, which we've got a customer here, your selector TO is your customer on the, on the left. And then we've got that documents TO that create on the right. That's one way. I'm going to get out of this and show you the other way. The other way to do it is to click on one of the fields in one of the TOs and then drag until you connect to the other TO. And in this case, I actually connected the right field together. So, but if I didn't do that, like you could actually like there's some cases where like you have a lot of fields in a table and you could use these little up and down arrows to do it, but that's pretty inefficient. So what I do is I just connect any two fields. I don't, I just connect, you know, it doesn't have to make any sense. The reason I just do, I do that because it automatically makes a relationship between them and it automatically puts the TOs in here that I want. So it saves more time than doing the new relationship with the bottom, bottom left. So, and now I'm going to change these. So in this case, I'm trying to relate customer to document. So I want to do the primary key to the foreign key. So the primary customer, the primary key for customer in the foreign key for customer in the document table. And then I'm going to press change right here. And this is actually where your predicate is for your relationship. And that looks right to me. That's our primary key to foreign key. So I'm going to press okay. That is how to create a relationship in FileMaker, a primary key to foreign key, a one to many using the relationship graph. All right. So other things on the graph. Now you'll notice there's other options in a relationship besides just setting up the predicates here. You've got operators. So there's a bunch of different operators here. You've got your equals, which is the most common. You also have a does not equals. There are certain relationships where you're going to want to do that. And you've got your four comparison operators. You're less than, you're greater than. These are used mostly or most commonly in date or time stamp fields. So like for example, if I wanted to know like the last 365 days, like the revenue for of the last 365 days of a customer, I would end up making a relationship from customer to order, which I actually do have here. I would make a relationship from customer to order. And then I would do my normal foreign key to primary key. But then I'd also add two more predicates to further filter the relationship. And I would be filtering based on the order date. I'm looking for any orders from today back 365 days ago. That is the relationship I can then use to get the revenue for that customer for the last 365. So comparison operators are really useful for a lot of good reporting. And then I have this little X here, which is kind of like the oddball operator. That's called a Cartesian. And all that does is it returns all of the records in the related table. And so you're like, when do I need that? That seems like that's nothing to do with foreign key to primary key. And that's true. The only time that you pretty much use Cartesians is when you're working with what are called single record tables, like for example, this preferences table here. Applications tend to have preferences tables, which it's just one record and a table and it stores preferences like what's the company's address information that's using this application, their logo, that kind of thing. And so if you want access to that data from a particular area in the application, you would need to make a Cartesian to the preferences, which I've done right here under the order group. And you'll see you've got the little X, like the white box here, the relationship box shows you it's an X. And so, yeah, this is using a Cartesian. It returns just that one preferences record. And it doesn't matter what field you use in the Cartesian. Palmaker ignores the field in the predicate. It just will give you all of the related records. So, those are operators. There's a lot you can do there. And then as I showed you in this relationship, you can do multiple predicates to further refine and drill down to get the exact subset of related records that you want. And then we also have, below the predicates, we have these three options here. So, you've got a creation option, which, if you check that, it allows you to create related records across the relationship. The big use case for that is, and I'll show you, the big use case is it allows, when you put Portal on a screen, you can create a new record at the bottom in a blank row if you have that creation option checked. And so, in this case, this is, I'm on the order screen, and I have this relationship to order line on them, and it has been set up for a creation option. And if I did not have that checked, then if I clicked in this bottom row here, I wouldn't be able to click in any fields. And there's times where you want to do that, but by far the fastest way to develop a way for a user to create related records is just to check that creation box so they can use the blank row. And then, for this second option here, you've got what's called cascade deleting. That will, FileMaker will automatically delete all the related records for a record if that record gets deleted. So, let's think about this customer to documents here. I really should set up cascade deleting on this because if I delete a customer, I don't want that customer's document still floating around in the database. It doesn't make sense for those documents still exist. So, by checking this box, I'm telling FileMaker, hey, go ahead and delete those documents when I delete a customer. So, that makes sure that your database is lean and trim, but it also can make sure that you have accurate information for reporting. So, if we go to order line items over in the orders area, I actually have cascade deleting stuff for this because we don't want line items floating around if you delete an order. There are times where you will build reports that are based on order line items and summing up order line items. And if you have orphans in there is what it's called. If you have line items without an actual order, you're going to have wrong numbers. You're going to have incorrect information. So, it's really crucial that you set up cascade deleting. And then, finally, the third option here, we have sort records. This is pretty much what you would expect. You can tell FileMaker, hey, the related records I get back from this relationship, I would like to sort them a certain way. So, like, I may want to sort all the line items, I get back by price. So, the most expensive is first. But you can do pretty much anything you want to there. So, a lot of things you can do in the edit relationship, a lot of different ways you can set up relationships here. And then, finally, one thing to mention that's important is indexing. I'm not going to go into what an index is. All you need to know for FileMaker for your day to day is that you have the fields on the destination side of a relationship or in case of anchor buoy on the right side of a relationship. Those fields need to be indexable. If they're not indexable, your relationship won't work. I mean, that is definitely something when I first started working in FileMaker, I ran into that problem quite a lot. Where I would end up trying to use a non-indexable field on the destination side, and my relationship wouldn't work. But I didn't quite understand what was going on. But ultimately, now that I understand what's going on, all you need to know is that just use indexable fields on the right side of a relationship. So, some things that are not, you can tell which things are indexable. First of all, you can see the word index on some of these. But even if it doesn't have the word index, it doesn't mean it's not indexable. FileMaker will automatically create indexes for you. So, you probably shouldn't have to worry about it. The only thing you really need to know is that unstored calcs can't be indexed, and neither can globals. Those are the two that people are usually tempted to put on the destination side of a relationship. And the relationship won't work if you do that. There's a couple other types of relationships I wanted to go over, more complex relationships that give you more tools in your tool belt to solve these requirements that your customers give you, your clients give you for an application. So, one of them is called chaining TOs to drill through data. So, you'll see that a lot of these are just, you just make a relationship off of the base table here, and you're good to go. But there's times where you need to make relationships that are more than one quote table away. And that's called chaining TOs together. Like, this is kind of a chain, or this is a chain. So, why would you need to do that? So, let me give you an example. Let's say that, let's go look at this order line on them relationship right here. Let's say that I wanted to show the vendor name of a product on the order line on them portal, on the order screen. One way I could do it is I could just create a field in the order line on table that auto winners that products vendor and display that. Certainly the case, but what if I don't want to store the vendors information in the line on them because it's unnecessary. I just wanted to use it as a reference on the order itself. Well, to use it as a reference, you would need to actually show the vendor field from the product record itself. And you can do that on the portal by making a relationship off of this order line on them to product. So, if I do that, if I do that, so it's just going to be another primary key, foreign key situation here. Product ID to product ID. I will now have access from the order screen. I now have access to product information through that relationship. So, I'm going to go and delete a couple things here just to make some space. And then I'm going to copy this. And I'm going to show, I'm going to change the relationship instead of using the order line on them relationship. I'm going to use this new product when I made it. And then I'm going to select a vendor so I can show the vendor for the product tied to this line. And there you go. These are coming directly from the product record itself, not from the line on them record. So, by drilling through, chaining TO's together and drilling through data, it opens up a lot of doors as to the kind of data that you can display. And the kind of data that you have access to in different parts of your system. Another type of relationship, complex relationship that's useful are what are called self-relationships. And so self-relationships are just relationships to the same table. And that happens a lot more often than you would expect. And again, it just depends on the requirements or your application, what you're trying to solve. So let's say that ABC Automotive came back and said, hey, I would love to be able to see order history for the customer on the order screen itself. So when I create an order for customer, I would love to be able to just have it like an area to see all their previous orders in a list. We can certainly do that. All we need is a relationship to order from order based on customer ID. So instead of this being a standard primary key to foreign key relationship, we're actually going to hook up two foreign keys together. And this is kind of a, this is one of those important points. You can relate any two fields together between tables. It doesn't need to be a primary key foreign key. It can be whatever you want as long as it actually makes sense. And in this case, I want to do customer ID to customer ID because I'm basically saying, hey, file maker, give me all of the orders for the customer of the order that I'm on. And that's how you can get order history. So that is a self-relationship totally valid and it solves a business problem. So that's another good one to know. And then there's also another type of complex relationship called a logical or relationship. So file maker's relationship graph here, actually, let me go to a multi predicate relationship. File maker's relationship graph is visually set up to handle and relationships. And you can see that here. By when I add a second predicate or a third predicate, they're chained together with and. What if you wanted to chain something together with ors? Well, you think, hey, can I don't click and change that? No, you can't. But you can get the same end result by, instead of having multiple predicates, you would only have one. And you would end up using what's called a return to limited list on the left side on the starting side of relationship to do that, to get your answer, to do whatever you're trying to accomplish. So I have, I actually have a working example here. It's a little more complex, but it's still the same. It demonstrates the same point. I have a dashboard here and it shows open quotes across the entire application. And you'll see if I scroll through this, I've got a few different types of statuses. I've got some on hold, I've got some pending, I've got some sense. And I have a filter here at the top. So if I don't check anything, it shows everything, right? The filter. The moment I check it, then it just shows me the thing that I've checked. So it's only showing pending quotes. And if I check sent, now it's going to show me both pending and sent. And so the only way I'm able to do this and portal, so portals, I have this portal tied to a relationship that is doing a logical OR and it's using this checkbox set. FileMaker's checkbox set, all it's really doing is when you check multiple options, it's actually storing a return to limited list behind the scenes in that field. So you can then just take that field and use it in a relationship and it will do the OR search exactly the way you expect. So if I take a look here, this field is quote status filter, which is right here. And you can see it's a return to limited list. That's what's actually in the field. And then if I go to the relationship itself, which was right here, again, it's more complex than just the filter, but you can ignore everything else. This right here is my OR. It's taking my return to limited list and it's searching the status field and the quote. And it goes through each value in the return to the list and it does a separate search for each of those values. And then FileMaker combines all of the records together that it finds across all the searches and then gives you back that as the result. So logical ORs also expand the number of things that you can do with a relationship and FileMaker. Okay. So let's get back here to slides. All right. So now that you know how to create relationships in FileMaker, you know, you've got your natural relationships that I've shown you some other more complex but useful relationships, I wanted to talk about ERDs, touching them briefly. ERDs are a way to graphically represent your data model to document your data model. It shows the entities and the relationships between them. And so this is an example of the ABC Automotive data model. It doesn't have the documents requirement in there but this was what we started out with but it gives you the gist. And so an ERD has three main parts to it. You have boxes which are the entities or the tables. You have lines which are the relationships. And then you have these little symbols here on the lines which are what are called cardinality that tells you what type of relationship it is. So, for example, this circuit right here is a zero. This little fork, that means many. And the dash means one. So you've got your zero, your ones, and your manys. So the way to read this is there's a relationship between customer to order and you can read this as a customer can have zero or many orders. And if you go backwards, this says an order can have one and only one customer. So that's how you look at an ERD. ERDs are, for a system that's this small right here, it's really not that crucial but as you have a medium to large size system, we're talking 50 tables, 25, 50 tables or more becomes really, really useful to have an ERD especially when you're trying to on board a developer, a new developer. From my experience, one of the things that takes the longest for a new developer on a new system to learn, well, excuse me, even an experienced developer on a new system they've never worked with, it takes them a while to get acclimated to the data model. So if you have a document like this, they can get a high level snapshot and likely it speeds up their progress on understanding the system and then they'll all to be more efficient through that understanding. The act of creating an ERD can help you when you're uncovering the data model of a problem space. So when we were talking about uncovering a data model in the first part of this presentation, I personally, I go through those steps but I also will create an ERD at the same time. The most useful thing for me, having worked as a volunteer consultant for such a long time, whenever I'm given a existing system that I've never worked in, that's sizable, that's the first thing I do. I create an ERD. I spend time creating an ERD because it really helps me see what's there, surveys the landscape and I get to understand what I'm working with and all the quirks because you definitely, when you're creating an ERD, you definitely can see like all the different sort of like potential issues in a system from things that happened over time, but it just gives you, it just positions you for success if you create an ERD while you're trying to uncover a data model. I also recommend that you do these digitally. When you're first learning ERDs, paper, great. Maybe even when you're going through and making it great, but you should translate it digitally. They're just much easier to maintain and you'll get more consistency across multiple authors over time if you do that. Okay, so ERDs, highly recommend. And so what about FileMaker Relationship Graph in relation to ERDs? FileMaker Relationship Graph is not an ERD. And when people first start out in FileMaker, I think that it's kind of a natural thing to set up FileMaker Relationship Graph like an ERD. I think part of that is because when you create tables, FileMaker automatically puts a TO on the graph to that table. And I think that kind of makes people feel like, this is an ERD, let's connect them all together based on the natural, one-to-many relationships, which that works fine, but as your system gets bigger, I think that and you need different types of queries that are not just the natural relationship queries, you'll start to get, I think, more confused. And so it's just good to know out the gate that FileMaker Relationship Graph is just a collection of queries. That's all it is. It's not an ERD. And the other big thing to know about the relationship graph is that context is critical. It matters where you are in the application when it comes to resolving a relationship. So let's talk about that for a minute. So context. That's another big thing when you're first starting on FileMaker. Not even when you're first starting on, I live and breathe context. When I'm developing FileMaker, it's always in the forefront of my mind. I have to have it in the forefront of my mind. It's the only way to really survive and be successful in FileMaker applications. And the reason why is that context is a broad term and it means more things than just what I'm about to say. But when it comes to relationships, context, I'm going to call it TO context. All relationships evaluate in a context, in a TO context. So the same relationship could give different results in different contexts. So for example, and I'm actually not being 100% technical here, it's really the same TO on the graph can give different results depending on the different context. So like a customer TO, like if you have a graph where you have everything connected, like an ERD-like graph, the product TO can give you a different result if used from a customer screen than it could from a sales order screen. So I'm going to show you an example of that here in a second. So what does that mean then? So we want to get super technical. What is context? Context is the TO that you start on on the relationship graph to resolve what you want to resolve your query. FileMaker, all FileMaker needs to know is where do I start on the graph and where am I ending? Where am I going to? And then it'll walk the shortest path it can to get there and then it'll give you your answer. So the question is what indicates the FileMaker, the starting TO? Like where does it start? How does it know where to start? There's two main places. It's the layouts TO, aka what layout am I on? And you specify that under layout setup and I'll show you that here in a second. And the other place is if it's an auto-winner or a calculation, the relationship graph, there's a evaluate this calculation from the context of. That tells you the starting point for that, for a calculation for an auto-winner. Those are the two main places. Whenever you're talking to another FileMaker developer and you're talking about context, those are the two things that pop on their mind. 80% of the time, the first thing that pops up in their mind is going to be the layout. Like what's the table or what's the TO that this layout is tied to? Okay, so let's take a look at this. Let me get out of this developer layout here. Oh yeah, I have a different version of Quick Start. And the difference, the only difference is that I broke Anchor Booty Convention and I connected the anchors of each of the five tables that are in the ERD for ABC Automotive. So I've connected order to, I've connected customers and addresses and orders and order line items and product. So you'll see they're all connected, okay? And so because they're connected, FileMaker can actually walk between them, which means I can now use those TOs in all the different places. And so I wanted to show you this whole product thing I was talking about. So if I go to Customers, I have created a new tab called Products Purchased. And this portal here is tied to the product TO and the layout itself is tied to the customer TO. So this is your starting point. This is where FileMaker is going to start to be able to give you an answer for this TO right here. And so the answer ends up being all of the products that Tyrion here has purchased across all the orders he's ever placed. And how do I know that? I know that by following what FileMaker would do. So I'm going to customer TO, so I'm going to click on here just to, you know, this is where I'm starting. And then I'm going to like follow the lines. So I'm going to click on here to see what this relationship is. So this relationship gives me all of the orders for this customer. Okay, so I'm on Tyrion's record, so I'm going to get all of Tyrion's orders through this relationship. So now I'm here and I'm going to keep going following the lines. So what is this relationship? All right, so this relationship gives me all the line items for all of Tyrion's orders. All right, so now I'm right here. I've got all of the line items for all Tyrion's orders. I'm going to keep going until I get to the end and the end is product. And I believe that this is the last line I got to walk. Okay, so this is going to give me all the products for all of the order line items for all of the orders for Tyrion. And boom. I've ended up at the end. So FileMaker is going to give me the record that's found. In this case it's going to give me all of those products that Tyrion's ever ordered on an order. Okay, cool. So that's one way to use this product TO. But what if I go to Orders and I use that same product TO? I've made another tab here with the same looking portal and it's using the same exact TO product that the customer is using. But it's given me back a different answer. It's only showing me three instead of five. And that's because if you follow, so now the starting point is different. Like if I go in the layout mode, let's take a look at our starting and ending point. Our starting point is order now. That's the TO tied to this layout. And our ending point is the same. It's product. So if I go up here, so let's start at my starting point. That's order. And we've already looked at this relationship. It gives me all the line items for an order. Okay, so I'm going to get all the line items for the current order that I'm on. And then this guy is going to give me all the products for all the line items, this relationship. And so when I end up over here, I get all of the products for all of the line items for the current order that I'm on. That is a different, a different thing. I just got a different set of things than I did on the customer screen. Like those are just two different queries. But I ended up using the same TO. So the point of this is that context matters in FileMaker. You're going to get different answers depending on where you use something. So you have to be very cognizant of your context. Okay. So one last section here before I close. I wanted to briefly touch on the two different main graph methodologies out in the wild. So we've got spider graphs. Spider graphs are, those are the most natural things for people to do. And again, I kind of talk, I touch briefly on to I think the psychology of why that is. You know, people start out ERD like, and then they just keep connecting things to that ERD like structure. And it just turns into the spider looking blob looking mothership looking thing, which I've got a, this is actually an existing system that we work with here at DB Services. It's a pretty big system. And it's no fun working in this graph. It's no fun working the application. Troubleshooting is a lot more inefficient because when you, when a relationship's not working properly, you got to do that whole walking thing that I showed you. And that takes minutes versus if we go to Anchor Buoy here, the thing about Anchor Buoy is that the, I know it's hard to see here, but you guys saw it earlier. The names like the path that FileMaker walks are the names of the TOs. So when you're reading code, when you're reading a script or looking at calculation, you already know what FileMaker is going to give you back because the TOs are named that way. So Anchor Buoy is what's called self-describing. And that's what makes it so powerful. And that's why it's the most popular convention methodology for experienced developers. It saves a ton, a ton of time when it comes to reading code and troubleshooting. The other big thing Anchor Buoy does compared to Spyder is it makes context explicit. The first name on the left of every TO is the context in which it can be used. So if you look at a TO and it has order as the first part of it, you know you can use that TO in an order context on an order layout or in a calculation field that is in the order table. Yeah. And so, you know, Spyder is not 100%. You know, like Spyder, again, is natural. And for a small system, not a big deal, no problem. Spyder does have one thing going for it that Anchor Buoy doesn't, and that's modularity. Just like I showed you, you can use the same TO in multiple places and you will get different answers and that could end up solving your problem. But again, for the amount of, you know, potential issues that you can run into and the amount of time you spend troubleshooting and trying to understand things, my experience is that Anchor Buoy is the way to go. And that is what we use at DB Services. And we even go so far as to convert Spyder Graphs to Anchor Buoy, which is not an easy task, but, you know, you'll be smiling at the end of that once you get that done, because it's worth it. Yeah. And like I had mentioned earlier, we have a naming convention's article, if you wanted to learn more on Anchor Buoy itself, then here's a link to that. All right, so that is it for the actual content on the relational design part of this presentation. I want to briefly mention a little bit more about DB Services, my company. Our mission is to make organizations more efficient and effective through custom applications. We're a full service filing consultancy offering application development support training and coaching, cloud hosting, and we're an authorized reseller. To learn more, go to www.dbservices.com or call our mainline at 1-888-488-0191. And finally, if you would like to get your hands on a copy of the template that I was using today called FM Quick Start, please go ahead and download it at FMQuickStart.com. It's free to use. So, okay, without further ado, I'd like to hand it back over to Ryan Manook to answer your questions. Thank you very much, Jeremiah. What a fantastic webinar. A lot of great information there. Again, we'll go ahead and open this up to some Q&A. If you haven't already, go to the webinar control panel, click on the question section, enter question, and click on send. In the meantime, to give some time for those of you who haven't asked a question yet, let's briefly cover some additional resources. So, the FondMaker training series is the official training curriculum for FondMaker certification. Even if you're not planning on getting FondMaker certified, these are fantastic resources for building up your foundational knowledge of the FondMaker platform and material that Jeremiah covered today was taken directly from these resources as well. So, there's two books, a basic guide, which is free to download off of iBooks and the FondMaker website. It's a great introduction to FondMaker features and methodologies, and it also walks you through the creation of your first task-based solution. And there's also an advanced guide, which you can get for $19.99 on iBooks, which dives a bit deeper into topics like relationships, scripting, calculations, reports, and FondMaker server. In terms of additional resources on the FondMaker.com, 4-slash-learning-4-slash-webinars page, there are more webinars that you can review for free, and additional instructor-led and self-paced training on the learning-4-slash-training page. If you feel like you're ready to talk about licensing, give us a call at 1-800-725-2747. We'd love to hear about your story and determine whether FondMaker would be a good fit for you. And again, DB services, fantastic FondMaker Platinum Business Alliance partner that you can work with. You can contact them through their websites or give them a call at 1-888-488-0191. All right, so let's go ahead and jump into some questions. First of all, we have a lot of people asking whether you're going to get a copy of this webinar. You will have a link sent to you at the end of the webinar. But, Jeremiah, the first question, we actually have a few people asking, can you re-verify what the term TO means? Yeah, so TO is an abbreviation for table occurrence, and it's literally what it sounds like. A TO is an instance of a table on the relationship graph. You can have as many instances of a particular table on the graph as you'd like. And so the FondMaker parlance that's called a TO. Perfect. All right, the next question. For these new users out here, these users who are just starting to get their feet wet with relational database design, would you recommend that they jump into a methodology like Anchor Bowie right away, or should they start with something more natural like the spider methodology? Um, you know, I'm definitely all about trying natural first, because you might as well go down, people learn best when they make mistakes. So, you know, or I wouldn't call spider a mistake. Sorry, like, you know what I'm saying? Like, it's good to kind of go through and learn why, you know, Anchor Bowie ends up being the best. I try to convey that, but, you know, if you want to try spider and then really deeply, truly understand, emotionally understand, because when you get a big enough system and you start to go, oh no, emotionally understand why Anchor Bowie ends up being better for you. You can certainly do that. But I think that you, you know, we, you know, when we have, we've, I've had plenty of people who are new developers that I've helped train, and they, I had them just start out directly in Anchor Bowie and they've done, done just fine. So it's really up to you. Excellent. All right. So the next question. Is there any particular software that you recommend for creating digital ERDs? Yeah. So if you have Vizio, that's a really good software. But if you're working on a team or we want a cloud access to that ERD, Lucidchart is a really good one that we use over here. All right. The next question. Back to relationship methodologies. Can you touch on the selector connector model and how you would compare that to the Anchor Bowie model? Yeah. So selector connector is, in my opinion, a modified Anchor Bowie model. It's pretty much 95% Anchor Bowie, but you break it and connect every, you connect all the base tables to a, I don't, the terminology here is going to get a little fuzzy, but you connect it to a set of other tables that are used to create records. But the difference here is that you still get the whole self-describing part for Anchor Bowie when you're actually using this stuff. You only use those other tables when you're creating records or you're doing something like building like a way to select records, like a selector is what it's called. And so when, like you can take those selector or those creation relationships and you can use them in multiple contexts instead of having to create different TOs, but you know exactly what the purpose is for each of those. And then you can use your standard Anchor Bowie relationships as normal for everything else. So you still get the self-description. So that's why a lot of people really like selector connectors, that it gives you a little bit more modularity back that Anchor Bowie, true Anchor Bowie loses. That's spider gives you so. Perfect. All right, the next question. Jeremiah, once you have a relationship created, you have a portal on your layout, do you have the ability to place a container field within that portal so you can add files to it? Yeah. Yeah, you definitely can. I would have shown you that, but in the interest of time I couldn't get into it. But yes, you can, that's what you would do with the customer to documents relationship that I had made. Once you created it, you then put a portal on the customer screen and tie that portal to the relationship. And then you would put the document container field in that portal, show a couple rows, make sure it's a creation relationship so that blank row shows up and then you can just right click and start inserting files one by one until you get everything in there for that customer. Excellent. All right, we're at the top of the hour. On behalf of Jeremiah, on behalf of FOMIC, it's our absolute pleasure chatting with you guys today. Definitely hope you learned something and we definitely hope to see you on another webinar soon. Have a great day.