How to create a historical db?

[worx3d]

Hello,

I'm a little shy about asking, I'm new to this so please be patient with me. Okay, I made a small database to manage our little business (movie rentals). I have two files, one with our movie inventory and one with our client (members) list. I would like to know how to create a historical of the movies our clients have rented. Right now, when a client rents a movie I made a simple script that fills the records of date of rent and 'rent due', it also shows me who's renting in the movie inventory (I made a relationship to bring the info of the client from the client file to the movie file). But I have no idea where and how to store what movies have every client rented. Can anyone help me? I've only been able to enter, in the client database, what was the last movie he or she rented, but this field gets overwritten every time the same client rents a new movie. I want, either a new db file that creates a new record every time a client rents a movie with client number and number of the movie rented (I have no idea if this is possible or how to do it), or a way to store all the movie codes (every movie has a unique numeric code) for the movies a specific client has rented in 1 field or wherever possible.

As you can see I'm totally lost, I have been using FM for 1 month or so, and my friends, who are Access experts, have no clue as to how to do things in it.

thanks, I'll appreciate your help.

Please excuse my bad english, it isn't my 1st language.

Wrx

[kenneth2k1]

Hi:

Don't worry, your english is excellent. If you hadn't said it was not your 1st language, I would have never known.

Anyways, I searched on fmfiles.com and found a video rental database: http://www.fmfiles.com/enduserbus18.html

It's not great, but it looks like it will work. The files are .fp3, so they will have to be converted.

Notice the system uses a line item file. Basically the idea is to have a "join file" that holds rental info for each customer record. Usually the design is like this:

Customer file

Invoice (transaction) file

Line Items file

Products (or movie, in your case) file.

This process is pretty complicated to explain here quickly. Perhaps if someone else is up to it, they can help. However, I have some learning material that would help explain this. It's not that it's hard to do, it's just confusing at first. But it sounds like you said that you have two files, so hopefully you are already familiar with relationships.

E-mail me if you would like me to send that material.

ken

[John Caballero]

OK, first make sure that each Member record utilizes a unique serial number. Go to define fields and create a number field called nID and set it to be an auto-enter serial number (have it increment by 1 for each new record). Place this field on your main layout and then click on it once to enter the field. Then go to the Records menu and select the Replace option and select the option that lets you update the field with serial numbers.

Do the same thing in your Movies database.

Now your Member and Movie databases have unique IDs.

Create a new file called Rentals.fp5. In it you'll want some important fields:

nCustID (number field)

nMovieID (number field)

Now define a relationship to your Movie database. In the define relationship dialogue box, you'll select the Movie database. You'll then see two lists of fields: on the left are fields in your Rental database (the source database) and on the right are fields in the Movie database (the target database). Click nMovieID from the list on the left and click nID from the list on the right. You can name relationships anything you want, but for now name this one Movie_Lookup

Now create a new field (again in the Rentals database) called Title and define the field to auto-enter it's data from a lookup. In the lookup dialogue box select the Movie_Lookup relationship and then select the Movie Title field that hopefully already exists in your Movie datbase. What this will do is enable the movie title to automatically be entered into your rental history database whenever you enter a movie's ID. As you can perhaps see, there might be other fields you would want to have look-up (price, for instance).

OK, in your Members database, define a relationship to Rentals.fp5 where nID is chosen from the list on the left and nCustID is chosen from the list on the right. Make sure the auto-create related records option is selected. Name this relationship Rental_History.

Next go into your main Customer layout and enter the layout mode. From the tools on the left, select the Portal tool. After selecting it, drag a fairly good-sized rectangle onto your layout. Have it show about 5 rows and select the "show scroll bar" option. If you had more than one relationship, you'd need to specify which relationship your portal references, but since you've only got one, it will default to that relationship. Now click and drag the Field tool on top of your portal. When asked to specify the field, select nMovieID. Do the same for Title and any other fields you created in Rentals that you'd like be able to see from your Members layout.

Enter browse mode. Click once on the nMovieID field in your portal and enter the ID for a movie then hit tab - something pretty cool should have happened.

Go to the next row in your portal and do the same thing, entering a different movie ID.

Play around with that until you get a feel for how it works. I predict you'll eventually come back with some questions about how to refine some things, but for now that should get you going.

[worx3d]

Thank you both Ken and John... I will try your suggestions right away John, I think I understand what you're saying, I'll give it a shot.

Ken, yes, I would love to get that material, please email it to me to joruiz ' at ' worx3d.com if it is free, or I'll email you to see how to get it. I have spent about a month with the program and I like it but I guess it could be a little easier to use (I've seen my Access friend making relationships in a graphical way, one to one, one to many and many to many, but I'm not sure we have all those options with FM).

thanks both, I'll come back with my results and perhaps more questions.

Joel