Subtitles of the Movie

In this lesson we're going to talk about how to search related data. How do you perform finds using criteria instead of related fields? We're going to start this lesson by opening up our FileMaker solution asset management. Make sure you're using the copy that's inside the 0808 file. Let's look at our relationship model. We have three base tables. First we have a table that contains all our asset items. Now, each item has a certain amount of quantity. So that when our company buys an item, we specify how many quantities we bought or how many are in stock. Now, our company has many different locations and each location has its own record in the location table. When we buy an item or we have a stock asset item, we distribute those items to our different locations and we place those records inside the location asset table. So for every item, we could have multiple different locations where some of those items are distributed. Inside our location assets, we have the asset ID and the quantity that were distributed there. We also have the location ID so that we can tie over to what items are at what locations. Now let's say that we wanted to do a find or search based on related criteria. Well, first of all, we need to create a layout for this specific function. Let's go ahead and look at the layout that we've created for this. It's called web item locations. What we did is we placed a portal on this layout. This portal is tied to the first hop over which is location assets. So we're going to show all the related locations for each item that we have. However, one of the fields on this portal is from the third hop over location so that we can grab the name of the location and specify it in the portal. Let's see how this looks in browse mode. We have, for each item, all the different locations and the number of quantity at those location in the portal. But we also have the location name because it's pulled over from the third hop over which is our location table. Now, when you find, based on related criteria, you place a search parameter inside of the related field. We're going to search from the third hop over, the location name. For instance, let's say that we want to find all the items inside our asset management or asset items table that are located at supply central. We go into find mode and we search on supply central. We have eight items at supply central. Or eight item types. The actual quantity are specified inside the asset location's quantity field. To perform this same search using the API, let's open up our items underscore list.php file. Again, let's make sure that we're working out of the 0808 project folder. It's very simple to perform this search. First of all, we need to correct our layout name because we're now working with item locations. That's the name of our new layout. And we need to specify the field. Well, the field we're searching on is this field right here. When we specify it, we need to use the table occurrence name and the field name so that the API knows which field we're searching on exactly. Even though it's on a portal that's usually in the location's asset table occurrence, we're going to use the actual table occurrence name of the field we're searching on. So in our API or PHP code, we'll specify a location and two colons and then the field name. Then our find criterion. We're going to comment out our different sort parameters and additional find criteria from the previous lesson. When we did our find here, we found eight records. Let's open up our browser and perform a search in the 0808 item list.php. Here we have our eight items that are all located at supply central. As you can see, finding based on related data is very simple. Just use the table occurrence name, two colons and then the field name. It's the same way even if you're not searching on a portal. So I've copied this field that we've searched on over to the actual layout. And we should be able to delete portal and perform the same find. Let's refresh the browser and see our results. And there you go. So you use the same methodology whether you're working by searching on related fields in a portal or just related fields on the layout. Obviously, if the related field is not on that layout, or you don't have permissions to search on that related field, and permission is granted to the PHP user that you're using, you're going to get an error. This concludes our lesson on searching related data.