Calculation Update based on One to Many Relationship

[pcjimenez]

FM Pro 11 Adv

 

Relevant Tables:

tblStudents

tblTechRental

tblTechInventory

 

Relationships:

tblStudents related to tblTechRental via indexkey field

tblTechRental related to tblTechInventory via netID field (computer's name)

 

Table data:

tblstudents - 1 record per student

tblTechRental - multiple records per student

tblTechInventory - 1 record per tech item (in this case, one record per computer)

 

Layout connected to tblStudents

Contains portal connected to tblTechRental with only values from that table

 

When we check out a computer to a student, a new record in the tblTechRental is created, auto inputting date, status, student name, price, etc.

 

I had the status in tblTechInventory update as well; however, a second related record will not override the update from the preceding record.

For example:

Sam checks out computer #5, record is created in tblTechRental; status in tblTechInventory for computer #5 is updated to "checked out".

Sam checks in computer #5, record in tblTechRental is updated with "Returned Date"; status in tblTechInventory for computer #5 is updated to "checked in"

 

John then checks out computer #5, record is created in tblTechRental; status in tblTechInventory for computer #5 does not update.

 

Status field in tblTechRental is Calculation Unstored, =If ( IsEmpty ( DateIn ) ; "Checked Out" ; "Checked In" )

Status field in tblTechInventory is Calculation Unstored, =tblTechRental::Status

 

I want the tech item's record in the tblTechInventory to update based on the most current related record in tblTechRental so when we go to an inventory layout, we can quickly see what items are currently checked out.

 

Thoughts: I was hoping I could do some sort of loop in a calculation, but I haven't found a way to do that. As I type this question, I am starting to think I should have a comparison calculation, if the ability exists, to compare dates of checked out or in and evaluate based on the most recent date of the checked in date; although, there will not be a date checked in field if the item has not been checked in.

 

Thanks in advance for your thoughts!

[Techphan]

Have a DateOut and DateIn field in each record.

 

DateIn - DateOut + 1 = DaysRented

 

I don't think you need a loop calculation, just any record with a DateOut and not a DateIn is out rented. You can use a portal for each machine based upon MachineNumber.