! Need help with my first FM db

March 22, 200619 yr

Hi guys.

I need help please.

As for my first FileMaker Database I am trying to create a simple database for keeping apartment inspection records in order. I need help with the relationships and value lists.

So far I have the following entities:

1. Property (can be one or many)

each property will have:

2. Staff (can be assigned to any property)

3. Suites (every suite will have one or many inspections)

4. Inspection ( I HAVE A PROBLEM HERE)

I need to be able to add/create new Inspection (for the suite) by:

1. Selecting property from Value List from Property table. (no problem here)

2. Selecting suite that related to the previously selected Property. (by value list) and

3. Some fields of the selected suite must be looked up to Inspection form

I need help with step 2 and 3.

If I select property A I need to be able to select suites belonging to property A and a lookup must copy the selected suite details to the Inspection form.

Please see the attached file for more details.

Any help will be greatly appreciated

Thanks in advance

pm_test.zip

March 22, 200619 yr

Author

Ok,

I think I figured out how to do 1 and 2. Now I am puzzeled with a lookup.

Here is what I am trying to acomplish:

In the Inspection form I select Property (Property details are looked up and stored in Inspection) , then I select Suite related to the property (Suite details are looked up and stored in the Inspection).

But what I am getting right now is when I select Property lookup grabs property details along with the first related suite details and puts it into Inspection.

How can I controll this process?

Thanks

pm_test.zip

March 22, 200619 yr

You are using an ID for Suite; which is good idea, in case they change the numbering scheme of SuiteNo (unlikely but possible). You then want to lookup the SuiteNo. But you are trying to do that "thru" a TO (table occurence) that is related via the PropertyID. As you saw, you only get the 1st SuiteNo for that property.

What you need is a directly relationship from Inspection to Suite, via the SuiteID; which you've already chosen. This ties the Insp. directly a specific suite. Hence it can get both the SuiteNo and the Tenant.

I also slimmed down your value lists. You only need 1 Property value list, for all PropertyIDs. You don't need a separate value list for each of the different TOG (table occurence group; the disconected groups of TOs on the Relationship Graph). They can use the same one, as it's not tied to a relationship.

I used a slightly different naming scheme for the TOs. Yours may differ. I put the TOG abbrev. first. Then I put any other TOs which are being passed thru (if any). Then I put the target table, in proper case (or camel case if a join table). Lastly I may put a field(s), prefixed with a tilde ~ (this is not an official naming scheme, but close enough for me }:( -).

pm_test_fej.zip

March 22, 200619 yr

I would suggest that you NOT bring in lookup values which are merely duplicates of information belonging to another table. I would simply display their value via a relationship. That way you are not storing the same information twice.

Here are a few changes I would suggest:

• Delete the address out of the Inspection Table. It is not needed as it can be displayed via the relationship with Suite any time you want.

• Delete the suite_no out of the Inspection table as well. It belongs to the Suite table and can be displayed via relationship any time you need it.

• Start a Tenant table with IDs and store any reference to the tenant as an ID rather than a name (which can change, for example by marriage).

• In the inspection table store the staff by ID, not name - (same issue, names can change).

In general I would only use lookups when you need to preserve correct historical information about an event.

I hope this helps.