Jump to content

! Need help with my first FM db


This topic is 5731 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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


Link to comment
Share on other sites


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?



Link to comment
Share on other sites

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 }:(-).


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Thank you guys for your input

I think finaly figured out the TOG.... (i hope)

Books do not talk about TOG in great details }:(

Can you recomend some reading material on this topic?

I did read FM7 Key Concepts / Michael Harris and i got some good edea of what is it.

here is my final version,

i think the Inspection TOG is right.

what do you think?


Link to comment
Share on other sites

This topic is 5731 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.