Need help with a formula (I'm an idiot)

[Greg Parks]

Hi,

I'm making a DVD Library database with a second related database for users so I can track movies they've borrowed. On the library DB I've got a field called "Availability". I want it to show either the text "Available" or "Checked Out", depending on whether that particular title is listed in any of the 10 borrowed title fields on the user's DB (the user's DB is called "DVDLibUsers" and the borrowed title fields are called "Title01" through "Title10". These fields use a pop-up relational value list from the field "Film Title" on the library DB).

I've tried using the "If" and "Case" functions, but to no avail. Example:

If(DVDLibUsers::Title01, "Checked Out", "Available")

I also have a field named "Return_Due_Date" on the library DB that I want to reflect the value of any one of the 10 fields "DateDue" from the user's DB-- if the movie is listed there.

Thanks for your help,

Greg

[Geeksharka]

Hi Greg,

I would recommend adding a third table (Rentals), with each record in it representing when a DVD is rented by a Customer. Each Rental record would be linked to the DVD table, and to the Customer table. It would also have a Rented Date, and a Return Date.

That way you can see which DVD's are currently being rented, when which DVD's are due back, and who has them.

Likewise you can see the DVDs any given Customer currently is renting, or has rented. If you attach a Cost field to the Rental records, ideally linked to a Status(CurrentDate) or Today function so that if they're late, it automatically increases the tally.

Anyway, the Rental table, which is a join table between DVDs and Customers is the key. You could at that point always know how many DVDs any given Customer is currently renting, and in the Add New Rental script perform a check prior to giving them more DVDs.

HTH

[Greg Parks]

Thanks for the suggestion. I think I've done something similar, and my reasoning is a bit different than what I've perhaps implied. To explain-- my goal is to put a DVD Library database of my own private collection on the web so that friends and family can browse and borrow movies from near or far.

My main db is the library ("DVDLibrary"), and secondary db is for the users ("DVDLibUsers"). In the library db I have a field "Availability" to show whether a film is currently available to borrow. This info is both for the users and myself.

On the users db I have 10 fields for titles of borrowed films ("Title01" through "Title10"). These fields use a value list from the library db's field "Film Title".

I figure I could make the field "Availability" a calculation function that looks at all 10 of the borrowed title fields on the users db, and-- depending on whether the title is listed in any of the records, it will return the text "Available" or Checked Out".

I've gotten it to work with the first titles listed (in field "Title01"), but it doesn't work in any of the other 9 fields. Here's the formula I've used (testing just the first two fields):

Case(

DVDLibUsers Titles::Title01 = Film Title, "Checked Out" ,

DVDLibUsers Titles::Title02 = Film Title, "Checked Out",

"Available"

)

I'm guessing that it might have something to do with the way the relationship between the two db's is defined, but I don't know why or how...

A second problem I have is to get the film's return due date to show up in a field on the library db-- if it is currently borrowed.

On the users db--along side each of the 10 borrowed title fields-- I have corresponding "DateOut", "DateDue", and "DateIn" fields. If a film title is listed in, say, "Title03" of the borrowed fields, I want the value of "DateDue03" to show up in a field called "Due Date" on the library db.

I'm probably just I'm just clueless, but I've been at this for about 12 hours now-- reading the manual and two other books-- and I'm pretty frustrated...

Thanks!

[Keith M. Davie]

Hi, you've posted a question which would have been more appropriately put on one of the internet forums. That said, could I suggest that you may be beating your head against a wall unnecessarily. While portals are fine and can be used in browser solutions, they really seem to me to be more appropriate for peer-to-peer solutions. What you are trying to do can probably be accomplished using the inlineaction tag. I mention this as an option for you to consider.

As to the "formula", have you considered a field (could be radio buttons in a peer-to-peer solution) which has a valuelist of the two entries you desire?

What you are discussing is probably more a matter of design than anything. I think you need to decide whether your design will be for a browser solution, a peer-to-peer solution, or some combination. You might consider the design of a pure browser solution where you write format files for your administration as well as for guests.

[Geeksharka]

I still think you need to re-organize your table structure, and use a Join in between the Library and User tables to track individual rentals. This applies for whether the system publishing to the web or not.

On the other hand, one reason your current setup is not working is that while you have 10 fields, the relationship is only set up for the first field -- which is why it will only work for that first field. You should create a relationship to each field.

Then again, unless I am completely missing the point here (definitely a possibility), making the extra relationships really is a waste of time. you'll have much better system flexibility if you create a Join table instead to track all the 'Rentals' (which DVD, who rented it, when, and its return date, etc. etc.)