Subtitles of the Movie

Let us take a look how you can use relationships, complex relationships to find a data. Now, I have my little invoices table here and I am going to go into table view, so that you can see all of the data. It is just a few records here. I am going to park this over here and open a new Window onto this table. That is one of the nice things about FileMaker 7.0. So, I can have the same table open by the same layout open in multiple windows. So, we will be able to look at this one in table view and see the data. I will park it over here. Now, I am going to define another table in this database. I have got my invoices table and I am going to have an another table and I am going to call it the controller. I will create it. and , I am going to place for now one field in the controller. I am going to put a cutoff date. And, I will make it type date. Now, I am going to go and create a relationship between the controller and the invoices table. And the relationship is going to be between the cutoff date in the controller and the date in the invoices table. and, I am going to say that the cut off date in the controller will be greater than or equal to the date in the invoices table. Now change this so you can see I am now looking at a non equal join. I am going to close this out and come back to field that is because in the cutoff date in the controller, I want to make certain that, that is a global. I only need one value for all the records or I could limit it to only one records in the table. But it is little neater just to make it one value. And, now if you look at the relationship graph you will see this little symbol here, the vertical line shows me that it is a single value, a global here relates to many possibly many invoices. I will close that and now what I am going to do is going to layout mode, I am going to create a new layout, I am going to call it date selection. It is going to be a blank layout and I am going to show the records from the controller. And, I need one field here which is the cutoff date. And now what I am going to do, I am going to create a portal here based on that relationship and I am going to show the related records from invoices and I am going to move all of the fields and click ok and I will go back into browse mode. Now, if the cut off date is, watch the data here let us say, if the cutoff data is 531204 I have got the two records that precede that 52204. I will sort these by date. 122203 and 52204 are the two records. If I say I want everything, notice I have some records here in June 23 and 8. Let us say I want everything before 63204, I have got 4 records. I am not showing this one the 68 record, record number 5. List them by invoice number you will see. So, I can use this relationship from global field to using a non equijoin relationship to find data remarkably quickly instead of doing a find. Now, I can further more come in here, sorry I did that in the wrong window, I am going to do this window and this view I will go into layout mode. Double click on this portal and I can choose to sort the portal records by anything I want. I am going to choose to sort them by let us say by an amount. So, now when I go back into browse mode, if I choose the cutoff date of 6/3 notice that I have got them sorted by an amount. They are not in record order, if I go back to my first choice which was 12/ 31/ 2003 I only have one record there. If I go here I can go to let us see 5/31/ 204 and the records are sorted by amount. So I can use this global relationship from a global field, from this global field to the table, to find data and sort it very quickly.