Hello. I've just started out trying FileMaker and the only database experiences I have from before all use SQL. Now I'm trying to create a little video rental system using FileMaker.

I have a table called Customers with all the information about each customer (including a unique ID number). I also have a table called Movies with all the information about each movie, including an ID number. To connect these two I have a table called Rentals, which uses the ID from Customers and the ID from Movies to make up what I like to think of as the keys for the table. So if a movie has been rented by someone, that someone's ID is in Rentals together with the movie ID.

So that's basically what I'm working with. Now I'm trying to create a layout where the user searches for the NAME of a movie to see whether the movie is in or whether it has been rented by someone, and if so, by who.

Any help is highly appreciated.

You would need a "date returned" (timestamp would be better) in the Rentals "join" table. Otherwise how would you know whether a movie was back? You may also need to know how many copies of the movie there are; but we'll ignore that for now. A movie would be available if the last time it was rented had a "returned" value.

Probably you should sort the relationship that you're looking thru for this date (or timestamp) descending. Then it's easy to get the last time it was rented (which is now the 1st value). If the "returned" field has a value, then the movie is available; if it's empty, the movie is still out.

If the person chooses the movie via a drop-down list of movies, which could be a global movie ID, then you could filter the choices to only those available; or you could let them pick one from all movies, then tell them it wasn't available; neither of the above requires a Find. But, if they want to actually Find for some text in the movie name, or other criteria, then obviously you'd need a Find.

I was thinking that I could do without the "date returned" date, since any movie that currently is *in* is not found in the Rentals table at all. I do not need to keep a history of rentals, I just care about who has rented what and when it should be returned. When the movie is returned the row in Rentals is just removed.

So back to my original question... How do I find out who, if anyone, has rented a specific movie?

I want the user to be able to do the following: Search for "spid" in a field to find "Spiderman" and immediately who has rented it. This would be pretty simple in SQL, but being a newbie at FileMaker, I need your assistance. :

On the Relationship Graph, you need a relationship between Movies and Rentals based on the Movie_ID. Then draw a portal on the Movie layout (could be small in a List view). Put the word "Out" (or a graphic) in the portal.

The logic is that the contents of a portal only show if the relationship is not empty.* So nothing will show if the movie is not in Rentals. You can make the portal lines the same as the background (or near enough, in case of alternating white/light grey rows).

You could also put a button "Return" in the portal, which would run the script step, Delete Portal Row.

You may want two different layouts, the user layout has the "Out", the admin person has the Return button. Or just use one layout, but toggle the text on the button, using an unstored calculation** or conditionally formatting (requires 9, haven't done it myself yet), so that it says "Out" for one "privilege set" and "Return" for the other, with a script that only runs for the administrative account(s).

As far as who rented it; you could put them in the portal also. Reach across to a table occurrence of the people table, thru the Rentals "join" table to get their name (their ID is in Rentals, but not their name).

* Known as the "Visibility" trick

** Case ( Get ( PrivilegeSetName ) = "admin"; "Return"; "Out" )

Result, Text; Storage, [x] Do not store

"admin" being whatever you name the privilege set, could be more than one with this access, in which case use "or Get ( PrivilegeSetName ) = "manager", etc.).

Thanks a lot for the help! I really appreciate it. Hopefully I'll manage to get something useful out of this.

If you run into trouble, post a zipped example or a Save as Clone file. It's really pretty simple, other than the bells and whistles.