Is this right?

[tomp]

I'm new in FM7 and trying to understand TO's and the relationship graph in general. I'm starting to like it!

My question:

I have a table with 35 calculated fields - one for each day in a 5 week period. Each record in the table is a rental unit. Each of the 35 calculated fields is displayed on a report layout displaying for each day whether a rental is starting, continuing, the unit is available, or out of service

The value of each calculated field depends, among other things, on what is found in a related table for the date/rental unit. The related table contains rental records.

I'm defining 35 relationships - one for each calculated field - from the date/rental unit in the source table to the date/rental unit in the 2nd table.

It looks like I'll need 35 TO's of the 2nd table??

Is that correct?

[RalphL]

I would think that the calculated field should be in the realted table and you should use a portal to view the related records.

You can design your relationship so that it will show only records in a date range.

[transpower]

Tomp, I sure hope you don't need 35 TO's. (BTW, if you're on 7 now, change your profile, please.) I think you need three tables: Renter, Rentalunit, Renter_Rentalunit (the join table). This latter table would have a date range. The Rentalunit table would have options for starting, continuing, availability, and out_of_service (which can be automatically set).

[tomp]

I guess I didn't make myself clear. I do have the 3 tables Transpower suggested (there are actually 28 file in the FM6 solution that I'm redesigning for FM7)

The rentalunit table has a record for each rentable unit. The 'join' table contains 'contracts' for each of the rentable units with start and end dates. For this report, I don't care about the renter.

Using a columnar report layout, I display a row for each record in the rentalunit table. For a varying (user specified) 5 week time window, I calculate a container variable for each of the 35 days that is a block of color depending on whether a contract starts on the day, a contract is in effect for that day, the unit is available for that day, or the unit is out of service for that day. This creates a visual graphic for the schedule/availability of each unit and the overall business as a whole. For this report, other than the start and end of a contract, I don't care about any of the other data related to the contract. There can only be one contract in effect for a unit on any given day.

Each of the 35 days in the report has to look into three fields in the join table to decide what color to display - rental unit, contract start, contract end. I've defined relations for each of the days to extract the info. Using the single path restriction from one table to another, I see no other solution than 35 TOs - one for each of the 35 days in the report. That does work, but it sure fills up the relationship graph. Am I missing something??

P.S. I did update my profile some.

[Ender]

Hi tomp,

A single portal is still useful for data entry or to see the history for a unit or a renter. But for the overview you have described, I think your 35-TO design is correct. You need individual relationships to each relational 'pidgeon hole'. If there is no related record for a particular pidgeon hole, you will see it right away, where in a portal view you will not. Some calendar solutions work in a similar manner (42 relationships to relate to all combinations of days on a monthly layout.)

One thing to note: performance tends to get worse with additional relationships on the layout, especially over a slow network.

Another option that sometimes is useful when performance is a problem, is to store the related pidgeon hole data in repeating fields in the parent record. These repeating fields are updated as needed, but otherwise are very fast to access. With a repeating field overview, you then do not need to have 35 TO's. Instead use one filtered by Week and Day (or something similar,) and loop through the Week and Day to populate the repeating fields.