New table new problem

[Fred in Thailand]

I hope this is not considered a double post but here goes.

First off, thank you all again for the help you have given me as I learn Fiilemaker.

It is greatly appreciated. I am still amazed at the level of expertise and willingness to share that exists in this community.

Now for the problem.

I have designed a solution that I use to manage a number of condominium projects. With this solution I bill the units for utilities, maid service, and other miscellaneous fees. This was working quite well. When I first designed it, I did not take into consideration that the units would one day be sold to other owners or that some owners would use their units as rental properties. Because of that I put all owner information in one table… Units. Now I have added a new table “Owners/Tenants” that will store that information.

The problem I am having is getting the information from that table to properly display in the related tables. I can get the information to display in the Units Layout and can get it to display in the invoices layout but how do I get it to print properly. If I use a portal and sort properly it shows the correct information but it won’t print. Mr. Vodka suggested using a join table which I have done before in another invoicing solution and also in this one. But that doesn’t seem to do what I want. Maybe I just don’t know were to put it in this situation.

All I am trying to do is get the information from the new Owners table into the Unit and the invoice table.

Any Ideas are greatly appreciated

[bcooney]

Shouldn't the relationship btw Units and Owners be by OwnerID? Also, can one owner have many units? If so, that's where you need a join table.

Also, do you invoice Units or Owners? I'd tie Invoices to Owners by OwnerID.

[Fred in Thailand]

Thanks for the reply.

I put the join table in a related it to the units by UnitID. Related to owners by owner id. The problem is that I still end up having to use a portal to get the correct owner displayed. I would realy like the Owner field in the Unit table to be automatically entered when the owner changes. Right now all I get is the first owner of record.

Thanks

[Fenton]

Sort the relationship(s) descending by the serial ID or a creation date. Or use the Last() functon on an unsorted relationship. That will get you the latest owner. But you'd have to do this in both places, Owners from Units and Owners for Invoice from Invoices. This means that ALWAYS the latest entry in Owners for a unit wins.

But it seems to me that you need a join table between Owners and Units. What if someone owns 2 Units?

And/or what if 2 owners own 1 unit, and both are current? That kind of messes with automatically getting the "owner", unless you flag those who are no longer current, which is not hard to do. I'm assuming you want to keep a history; otherwise you could just delete the join table record.

Invoices 2 is a poor name for a TO. Also, for aesthetics if nothing else, please collapse your TOs to show only key fields before posting. It makes your Relationship Graph much prettier too :-]

[Fred in Thailand]

Fenton

Thanks for the feedback.

I have already figured out the sort situation. Where I am having a problem is with the Join Table. Yes an owner could own more than 1 unit. As a mater of fact I have owners that own as much as 5 or 6 units in more than one project. From other posts I realize I need a Join Table and have put one in. I have used a join Table to print invoices before but just can't figure out the usage in this case. It seems that I need two joins one between Owners and Units and one between Units and Owners for invoicing. But what would be the relationship? Also would I need all the fields from the Units table on the join. I am having a real problem with the join concept.

Thanks

Fred

[Fenton]

A join table usually has very few fields. The minimum would be 2 foreign keys, one from each of its parents; in this case UnitID and OwnerID. Then it would have any fields that are particular to that "join"; such as date the join became effective (a creation date may be sufficient). Sometimes a join table would have other fields, such as its own auto-enter serial ID; but that would generally be for internal operations, and is not always needed.

Record 1:

Unit ID: 1

OwnerID: 1

Record 2:

UnitID: 2

OwnerID: 1

OwnerID 1 owns two units.

Record 3:

UnitID: 1

OwnerID: 2

Now Unit 1 is owned by 2 owners (if that happens). A join table can handle both of these conditions (though the multiple owners at one time becomes a bit more trouble; like what happens with Invoices, costs, etc.).

The reason you need the Owners for Invoices table occurrence is because even though the relationship between Units and Invoices is on Unit ID, a Unit could change ownership. Therefore over time a Unit may have many Invoices, some of them to a previous owner. Therefore there is a connection between Invoices and Owners independent of the one between Units - join - Owners.

Whether or not a join is needed there is dependent on whether you send 1 Invoice and expect it to reference 2 owners at once; which is a little awkward, but business often is. I don't know how complex the ownerships are, so can't say. But it is possible that simple Invoices could tie directly to Owners without a join.

[Fenton]

In fact if I was designing it I would break the current connection between Units and Invoices. I would move Invoices down to be its own Table Occurrence Group (TOG). Then create another instance of Invoices, to hang off of Units, named "units_Invoices". This is called the "anchor-buoy" method. It keeps both your Relationship Graph and your drop-list of Table Occurrences (TOs) cleaner; it also keeps your logic more straightforward, in my opinion.

In this case you have "properties," which are physical entities, owned by people. That is the Units TOG. Then you have Invoices, services rendered, which is more financial. You'll likely end up duplicating some of the TOs, to have one in each TOG; but that is really not a problem, in my opinion, because they will be separated into "related" and "unrelated" in the drop-down lists (where it matters).

With this method every major table has its own TOG, with its main layout based on its "anchor" TO (which is why I say break the connection and move it, rather than create a new one to use as the anchor; you've already done the layout).

Edited July 15, 2007 by Guest

[Fred in Thailand]

Fenton,

Again Thanks... this has been a "AH HA!" moment... I think I finally understand the Join table.

Also, I also see the point of the anchor-bouy method. I have seen it referenced in other post but have not had the time to explor further. I will now.

I will implement you ideas and let you know how it turns out.

Thanks Fred

Ok, have added join table. The only problem is that I don't know what layout to use to add records. If i add new record in the owner table it doesn't record in the join or show in the Unit table. Should i be using a portal in the Units mabey on a owners tab and entering the info through the portal?

Edited July 15, 2007 by Guest
Added New Image

[Fenton]

You often add records to a join table via a portal in either of the parent tables, based on the parent's id, with [x] Allow creation of related records turned on in the relationship.* Often also [x] Delete related records.

Be SURE you check the box on the JOIN table side of the relationship, NOT the parent side (very bad to delete parent record if you delete a join record). To "unjoin" an entry, you can just delete the join record/row in the portal. It will then not show from either side (as it's gone).

Then you just need to choose the other parent in the portal to create a full join; ie., in the portal in Units, choose the Owner; in the portal in Owner, choose the Unit. The latter makes the most sense however, adding a unit to an owner. So maybe don't turn on the "allow create" option for the relationship from from Units to the join.

*Alternatively you can script this, with a button to create new joins, going to a dedicated layout and returning. But in your case I assume not a lot of units, so an entry portal may be sufficient.

[Fred in Thailand]

Fenton,

Thanks for the help. it is greatly appreciated.

This was the first Database I ever built. I have no programming or developement training. Everything you see is from trial and error. Most of my knowledge has come from this forum. I am continually amazed at the willingness of the community to share their knowledge. I know that some of the layout design is a little over the top but you should have seen it before the rework. Still have to try and figure out the invoice side. I will start on that tomorrow. I have attached a cloned file. If it is not too much to ask, when you have the time I would greatly appreciate any comments you might have on what I have done so far.

Again, thank you, you have been an imeasurable help.

Fred

Billing_Software_V3_Clone.zip

Edited July 15, 2007 by Guest
Forgot the file

[Fenton]

OK, here's a few criticisms, since you asked for it :-]

1. Why R1, R2?

They look a lot the same. Why R1, R2 in the Unit ID? (read on)

2. Why Residence I, Residence II, etc.?

If these are multiple units for an owner, they should all be shown on 1 layout, in a portal. That's what the join table is for. They would be viewed from Owners.

It seems you're trying to use Units as Owners. You don't even have a decent layout for Owners; whereas I see owners as one of the main data entry and navigation anchors.

Whenever I see item1, item2, item3, I suspect inadaquate relational design.

It may seem initially more difficult to design relationally. But your design seems to have more tabs, layouts and scripts than it needs; which is (a lot) more work.

3. Why is the Owner, phone, etc. still in the Units table?

The join table handles connections to Owners. If you must have the Owner in Units, then you'll have to take responsibility to keep it current.

3. Why/what is the Occupied table?

4. Why is Unit_ID in Tenants?

That's the point of a join table, to keep foreign keys for multiple items out of the parent table.

I'm assuming you're using a join table to keep a history, in case a tenant moves from one Unit to another.

5. Some of your relationships, Yacht Allowance, etc., have [x] Allow creation turned, but on the wrong side of the relationship. They should not have it on the Units side; at least I don't think so.

6. All those [ 101 ], [ 102 ] buttons, running Find 2 (poor name) with fixed parameters.

If this is to Find Units, it should be just a portal, with a Cartesian self-relationship to show all the units.

A single step, Go To Related Record would go to the Unit record.

These buttons are clunky and a lot of work.

7. Why is Notes tied to Invoice Notes via the Notes field? This is just wrong. You need to think about what an Invoice Note is, and what you want to see in Notes generally, and from where.

It is possible to have a single Notes table, which has notes from BOTH Invoices and Units, which can be seen from either. But I don't know what you really want.

8. Why is Picture1, 2, 3, 4 in Notes? You might consider another table, or even file, for Pictures, to keep the size of your main file smaller, for backups, etc..

Once again, use a related portal. You only need 1 Picture field.

[Fred in Thailand]

Fenton,

Let me see if I understand what it is you are trying to say. “Fred, leave the developing to the pros, YOU SUCK!”

Seriously though, thanks again for the input. I will try and address your comments and see if what I have tried to do makes more sense.

First let me quickly define the Business model. We build condominium and small housing developments. After construction the projects become single corporate entities (Like a homeowners association) that are responsible for the day to day operation of the project. Units, in my opinion, are the most important entity in the equation. We do all billing for the administration of the projects to the units. The only reason to track Owners and Tenants is to have a body to send the bill to and to receive payments from. Also to let keep track of who and when the Units are being occupied. The Owners and Tenants are really only for record keeping purposes.

As I see it the basic structure of the database should be as follows.

Projects) : (Each Project has many Units)

Units) Main Table

1. Owners) Child of Units (Can Own Many Units)

2. Tenants) Child of Units (One Tenant per unit)

Invoices) Main Table Used to bill

1. Maid service

2. Water

3. Electric

4. Service

5. Telephone

6. Annual Maintenance fees

7. Etc.

This started as a simple invoicing solution to bill the units for water and electric. As with all things like this, the Owner of the company kept saying “Fred make it do this and this and this and this”. Now it has become a nightmare of additions that I am now trying to correct. I think I may have to start over from scratch and build it the way it should have been in the first place.

I hope that helps in your understanding of why we are where we are.

Now to address your comments.

1. Why R1, R2?

They look a lot the same. Why R1, R2 in the Unit ID? (read on)

As I said above, the units are the most important entity. When I first designed the database, we only needed the Unit ids for two projects, R1 (Residence1) and R2 (Residence 2). It was my attempt to Use an ID that was the combination of the Project name and the Unit Number. Made it easy for finds and for display of info.

2. Why Residence I, Residence II, etc.?

If these are multiple units for an owner, they should all be shown on 1 layout, in a portal. That's what the join table is for. They would be viewed from Owners.

It seems you're trying to use Units as Owners. You don't even have a decent layout for Owners; whereas I see owners as one of the main data entry and navigation anchors.

Whenever I see item1, item2, item3, I suspect inadaquate relational design.

It may seem initially more difficult to design relationally. But your design seems to have more tabs, layouts and scripts than it needs; which is (a lot) more work.

These are the actual names of the Projects. Each project has multiple Units and Owners and each Owner could have multiple Units. Again the units are the main entities. Should I Include a Project table for each Project with the Units as a field in those tables? Should I have a projects Table as the Parent with Units as the child related Parent fk_Units_ID to Units PK_Units_ID. I simply do not know.

As for as the scripts, new guy with a new toy syndrome. I see something I think is cool and try to use it.

3. Why is the Owner, phone, etc. still in the Units table?

The join table handles connections to Owners. If you must have the Owner in Units, then you'll have to take responsibility to keep it current.

:oops: Just haven’t gotten around to removing them.

3. Why/what is the Occupied table?

Good Question. Put it in to use as a value list because I didn’t know any better. Will remove it.

4. Why is Unit_ID in Tenants?

That's the point of a join table, to keep foreign keys for multiple items out of the parent table.

I'm assuming you're using a join table to keep a history, in case a tenant moves from one Unit to another.

:qwery: Thought it needed to be there. When I use the script “Change Tenant” I have a set field step that sets the Unit id from the current record in Units. Are you saying I don’t need it? Keeping track of the tenant’s history. An owner rents the Unit the tenant is responsible for all fees during the time of occupancy. Renter leaves, the Owner id now responsible.

5. Some of your relationships, Yacht Allowance, etc., have [x] Allow creation turned, but on the wrong side of the relationship. They should not have it on the Units side; at least I don't think so.

Don’t have an answer to that. Will look into it.

6. All those [ 101 ], [ 102 ] buttons, running Find 2 (poor name) with fixed parameters.

If this is to Find Units, it should be just a portal, with a Cartesian self-relationship to show all the units.

A single step, Go To Related Record would go to the Unit record.

These buttons are clunky and a lot of work.

My attempt at making it easy for the user. Again the Unit is the important entity. I have used your suggestion in two other solutions after this one but if I did that here I would have a portal a few hundred rows long. By going with the buttons I can keep the find limited to the project that is being looked at. Kind of like a filtered Roladex.

7. Why is Notes tied to Invoice Notes via the Notes field? This is just wrong. You need to think about what an Invoice Note is, and what you want to see in Notes generally, and from where.

It is possible to have a single Notes table, which has notes from BOTH Invoices and Units, which can be seen from either. But I don't know what you really want.

I don’t have an answer. As you see the note field here is a global. It goes on all invoices. As such it doesn’t even need to be related. My mistake. Will change.

8. Why is Picture1, 2, 3, 4 in Notes? You might consider another table, or even file, for Pictures, to keep the size of your main file smaller, for backups, etc..

Once again, use a related portal. You only need 1 Picture field.

I agree. I will change to that. Picture Table Related to Units through a Join table Viewed as a portal in Units. Viewed Horizontal. Right?

Hope this all makes sense. Remember I am a 59 year old man with a very steep learning curve.

Again, thanks for all the time you have contributed to this. You have earned your place in Filemaker Heaven :yourock:

[Fenton]

It's late, after midnight (and we're not going to let it all hang out :-). Quick answer to #4. My real opinion is: use a join table between Units and Tenants, with Unit ID, Tenant ID, Date In, Date Out. Otherwise you cannot keep a historical record, if that matters.

If history does not matter, then I guess you could put the IDs in either parent; but I think I'd put the Tenant ID in Units. But I wouldn't; I'd use a join table. It gives you the flexibility to handle anything a tenant could do, in past or the future. Example: Tenant 25 is moving from Unit 200 to Unit 201 next month. I want to enter that data now. How? Easy with a join table; in current join, put date to move out; create new record, with date to move in. Data is ready to go for next month, history is preserved.

About Projects. Link it up to Units properly. Put the ProjectID into Units. Use that for Finds and filters.

Edited July 16, 2007 by Guest
Removed rambling