newbie relating Table Occurrences

[eoc]

I'm sure this is easy but I'm going crazy..

I've got a table (Properties) and each record can connect to a "tenant" table- Each property can have 1 or 2 tenants. Tenant 1 can be selected from a value list which looks at a Tenant1 table occurrence, likewise for a Tenant2 table occurrence.

I know this is a many to many relationship, but for the life of me I can't get a join table to work at all. What I would like to be able to do is show from a portal within the Tenant table which properties the tenant is associated with, whether it is a tenant1 occurence or tenant2 occurence. I've attached a pic of the relationship. The shown join table does nothing, even with "create records" set correctly etc.

I have to admit, the whole table occurence/context thing is just completely baffling to me. Any help would be greatly appreciated. I've tried, really tried to get this from all the books etc. It just seems like it will never make sense!

Thank you..

[comment]

Is this a truly a many to many relationship? Can a tenant have more than one property?

[eoc]

Well, it's for commercial tenants and properties, so I believe so. One property may have more than 1 tenant (say a building with a Starbucks and a drugstore), and one tenant may have more than one property (multiple instances of Starbucks).

So I can see the properties associated with a particular tenant if I am looking at a layout based on "TenantDB1" and that tenant was chosen from the PropertyDB with the value list based on records from TenantDB1, but I can't see that property if it was selected from the TenantDB2 instance from a layout based on TenantDB1..

And I can't see a dang thing from the PropertyTenant join table, even though my understanding is that I should be able to pull from any table that is related. So I know it's 2 issues: One, my utter inability to generate a working join table, and Two, trying to get portals from 2 different occurrences to work on the same layout.

Thank you for your reply..

[comment]

The whole point of a join table is not to have any Tenant fields in the Property table, and vice-versa. The Property table is strictly about properties, the Tenant table about tenants. Anything that connects tenants to properties goes into the join table. If there are 2 tenants for a property, then there are 2 records in the join table for this property.

[T-Square]

Your problem stems from the fact that you have two things going here that are at odds. You have the join table, but you also have the embedded duplicated TenantID fields in the Property table that are linked to the two TenantDB TOs. However, you do not need f.Tenant1UniqueID or f.Tenant2UniqueID. Neither do you need TenantDB1 or TenantDB2. Just get rid of them.

Each entry in the PropertyTenantJoinDB represents a single instance of ONE tenant at ONE property. The join table will show you all the entries that have the current TenantID in them. Looking the other way, so to speak, the join table will show you all the tenants related to the current property, no matter how many there might be (1, 2, 457).

To display all Property info related to a given tenant, on a layout based on the Tenant TO, create a portal based on PropertyTenantJoinDB that includes fields pulled from the PropertyDB and the PropertyTenantJoinDB TOs. I am assuming that there are other fields in the join table (such as lease dates, rent, etc.) that you will want to use. For the properties, switch the directions around.

HTH,

David

[eoc]

Thank you both for your reply. The reason I have the TenantDB1 and 2 instances is I am trying to avoid using a portal for data entry on the Property record (If I could have a portal in a column format rather than a row format I'd be set, for interface reasons).

With a separate occurence I could use a drop-down menu for each instance with a related value list and fill in relevant fields on the Property record using lookup calculations. Just can't put the 2 instances back together in a portal going the other way (looking at properties from the tenant table), and it is looking like a portal and its format is the only way I can accomplish this on the Property side..

Thank you.

[comment]

I am not sure I understand the interface you're describing. But in any case, it's not a good idea to design a car around the upholstery. The tables and their relationships are your engine. Get that one right first. Then you can see how to also make it look and behave the way you want (a picture or a sample file could be useful?).

[T-Square]

There's nothing (that I know of, anyway) that prevents you from making your portal substantially taller than a single line and parking multiple lines of fields in that broader area. Maybe you can get the look you want that way?

David