Portals and Calculations

[Protour Travel]

I have a table entitled ACCOMS (for accommodations) which has the fields #rooms, #nights and rate.

I have a table entitled Hotel.

I have placed a portal from the ACCOMS Table to the Hotel table. No problems there.

On the Hotel Table I have a field named Total Revenue.

I would like to place the total revenue of all the accommodations for that day in the Total Revenue field.

Right now I have it set up as a calculation field with the following string;

ACCOMS:#Rooms * (ACCOMS::#Nights * ACCOMS::Rate)

This is entering the total revenue for the first portal entry only and not a total of all portal entries.

When I try repeating this string within the calculation...

ACCOMS::#Rooms * (ACCOMS::#Nights * ACCOMS::Rate)

ACCOMS::#Rooms * (ACCOMS::#Nights * ACCOMS::Rate)

It returns the total of the first portal entry X 2.

What am I missing?

And as usual, thanks in advance.

[Matthew F]

This is entering the total revenue for the first portal entry only and not a total of all portal entries.

When you display a field from a related table, only the 1st related record will show up (unless you are using a portal). Nothing in your calculation tells Filemaker that you want to sum up all of the related records, rather than just using the 1st matching record.

You could try the following:

1. Create a calculation field on the ACCOMS table that gives the revenue for that record (#Rooms * #Nights * Rate).

2. Create a summary field on the ACCOMS table that gives the Total of the Revenue calculated field.

3. Create a relationship between the Hotel and ACCOMS tables based on the Hotel ID and the date of interest.

4. Place the summary field (2) on the Hotel layout.

Does that should do what you want?