Populate field by calculation

[Fred in Thailand]

I'm Back

I have a DB for Condo projects I manage.

Have a table calle Units

Have a table called Owners

When a unit is sold, A new owner record is created. When I go back to the unit Layout I need to see the current owner. What i get is the first Owner of the Unit. I don't think the LULast trick will work. Would like to do it by a lookup but can't seem to make that work. Any suggestions?

Thanks again all.

[mr_vodka]

I think you should have another table that serves as a join table since an owner can own more than one unit. This could be a transaction table and you can have a bought date in it. If you have the relationship to it or a TO of it sorted by Transaction Date Descending, it will always display the owner whose tranaction date was latest.

[Fred in Thailand]

John,

Actualy I just put a portal to the Owner table sorted by OwnerID and that solved the problem with seeing the current owner.

Now the real problem.

Another table is used to invoice the owners and tennents for utilities. (Water, Electric, etc.)

When I create the invoice, It needs the most current owner. Same problem different answer?

The Person billed needs to get the information from the most recent Owner/Tennent.

Let me see if I can make this a little clearer.

I have a database used to manage a number of condo projects.

This was the first Filemaker DB I had done. As you all can well imagine, I made some basic mistakes in the initial design.

Such as I did not take into consideration the fact that the units would change ownership, some units would be rental properties… you get the drift.

Now I am in the process of fixing the early mistakes. I have added a table that tracks ownership and a table for tenants.

I bill the units for utilities.

I am having a problem getting the current owners/tenants into the invoice table.

As I said in my last post, I can get the current owners or tenants to show up in the units table, but can’t seem to get that information into the invoice table.

I am sure it is some kind of calculation but cannot figure it out.

Thanks again

Edited July 12, 2007 by Guest

[mr_vodka]

As I stated earlier, I would create a new join table. I do not believe that a seperate owner/tenant table is needed, but rather one table with a field designating it as an owner or a tenant.

All utilities would be associated with the Unit itself and I would have a field that would designate each unit to be billed to the the owner or tenant. Depending on the choice, the most recent tenant or owner's address would be on the invoice.

[Fred in Thailand]

John thanks for the suggestion.

I understand the idea behind the join table, but am unsure as to the relationship.

Is the join table related to the unit or the invoice or both. I agree that only one table for Owner/Tenant is needed. Figured that out last night. I guess I am a little unclear as to the use of joins.

Thanks

Fred

[mr_vodka]

Here is a demo on a join table.

JMO's Join table example