date due calculation

[staypuffinpc]

I have a file, CheckoutList that allows people to check items out from another database. ItemDescription contains the description and stats for each item, such as its name, length of checkout, etc. CheckoutList has a field, DateCheckedOut that is automatically entered. ItemDescription has a field "CheckoutDateLength". In Checkout, I created a calculation for a field "Date Due" that looks like this:

DateCheckedOut + "ItemDescription::CheckoutDateLength"

This should return a due date that equals the actual time plus the CheckoutDateLength determined in ItemDescription. The problem is that the calculation always uses the CheckoutDateLength from the first record in ItemDescription. e.g. if Hyperstudio CD is the first record in ItemDescription and it has a CheckoutDateLength of 7 days, it reports correctly in Checkout. However, even if someone checks out another item, the date calculates using Hyperstudio CD's length of 7 days. How do I create the calculation relative to the record?

[LiveOak]

You need to use a relationship in the calculation that is "relative to the record". If your relationship points to more than one record in the related file, the calculation will always use the first matching record. Just a case of the calculation doing what you tell it to, not what you want it to!

-bd

[staypuffinpc]

Excuse my ignorance, but how do I determine "relative to the record" inside of a calculation? In this same Checkout File I have already created two fields, ItemName and ItemNumber. ItemNumber brings up a list of numbers, 1 thru x, depending on what Item from ItemName people choose. In this case, I was able to use only relative values for ItemNumber because I made a value list and choose from those values. I can't figure out how to create this same type of relativity within my calclution for the date field.

[LiveOak]

You need a second relationship which points to only the single record you wish to base the calculation upon. Exactly how to set this up depends upon a lot of things about your design you have not described. You might have the user click on one item in your list to select it and have an attached script set a global field to a to the Item Number Field. This global can then be used on the left side of a second relationship to point to just a single item number in the related file.

Once again, the key is to create a second relationship which will point to ONLY one Item Number and make your calculation:

DateCheckedOut + "RelationshipByItemNumber::CheckoutDateLength"

-bd

[staypuffinpc]

I'm not sure I know how to use the global for the left side of my relationship. Maybe explaining a little more how things're set up'll help. In ItemDescription there is a portal to a file called ItemList. This list creates a record for each item determined in ItemDescription. Hence, if there are three Hyperstudio CDs, Item list has a record, Hyperstudio 1, Hyperstudio 2 and Hyperstudio 3. Hyperstudio, the item name, is a lookup from ItemDescription, but the number is actually a field housed on ItemList. This allowed me to make a relationship to a file called CheckoutList in which I could choose an item, such as Hyperstudio CD, from a list and NumList showed the numbers of Hyperstudio CDs in ItemList, in this case 1,2,3, each on a different line. Currently, CheckoutDateLength is housed on ItemDescription. If I changed its parent to ItemList, would this give me the needed relationship I need to determine the date based upon the item? I know this is probably a really messy way to do everything but this is my first real project in FMP, so I'm open to suggestions. Thanks for your expertise.