Layout advice for better database

[kibber]

I already asked this question but since I piggybacked it onto a previous non related post I'm hoping no one saw it, rather than not having any advice for me.

Eitherway, I'm having some major trouble with my database surrounding multiple last names from completely different families. Comment suggested I incorporate a new table called ownerships where I link the last names to the properties and that is what I am trying to do. But the problem I am having is incorporating autoentered serials. If I have a _ClientID (auto enter) and a _PropertyID (auto enter) and I'm linking them together in the ownership table no one will remember that Smith = 1178 and his property 6 harmon = 3110. asking someone to link these two things is impossible.

So I am asking you guys if anyone has any suggestions how I can make this easier, or if this is it.

What I am hoping for is a process of elimination type thing, you first select the last name, then you get a related value list for the first name, then you get another related value list for the property ID.

Maybe I am just going about this whole thing the wrong way, I am getting a little burned out by all of it.

Again, thank you for all your help.

[Ender]

There are a couple ways to make a selection work so that when the user selects from a list of real-world things (like names,) the ID of the selected thing gets used for establishing the relationship.

One way to make a selection list, is to use a value list made of items from the table you wish to select from. Value lists can either be made up of all items from a field in a specified table or just the related items. Showing all items is fine in some case (like for very small tables,) but often you want to show just a subset of a table in the value list. For this, you'd create a relationship between a field (usually a global) in the parent table, and a corresponding field in the table you are relating to. Usually these relationships used for filtering are made to additional table occurences, so that the original relational structure remains intact.

Now about those IDs. In FM7 (and previous versions,) you can have a value list show data from two fields, the first is the data that will actually be used to populate the target field, the second is additional text that only shows in the value lists (next to the first.) For making an ID selection, you would have the first field be the ID and the second be the Name (or whatever.) With this, the user sees both the ID and the name in the value list, and upon selecting, the ID populates the target field. If you also need to see the name on the layout after the selection, an additional relationship from ID to ID can be used, and the related value shown.

Another method that's worth considering for selections, is a portal. The portal would use the same relationship as the value list, but a portal can hide the ID field, and show multiple related fields, if desired. The actual selection is made by using a button on the portal row attached to a script that gets the ID of the related item and sets it in the parent table. Since portals take up more space than a value list, it may need to be placed on another layout that is used just for this selection.

Let's look at your desire to filter your list of Clients by more than one field. Doing this with value lists is commonly called using 'conditional value lists', though the idea is similar to that used for filtering a portal by multiple fields. In order to get the selection lists filtered down, you filter each successive value list by the previous selection. In other words, you use the previous selection as part of the criteria for the next relationship (which is used by the next value list.)

So for selecting a last name (gLastName, a text field with global storage) for a particular Property, you might use a value list of 'all values' from the Last Name field in Client. Then a second value list of first names, defined with a relationship based on the Property::gLastName = Client::Last Name, can be used to populate a first name field (gFirst Name, a text field with global storage.) If there is only even one client with a particular First Name-Last Name, then this is sufficient. A lookup based on a relationship from Property::gFirstName = Client::First Name AND Property::gLastName = Client::Last Name can be used to pull in the ID from that Client. If it's possible that more than one Client have the same First and Last Name, then you need to go deeper. Have another value list based on the selected First and Last Names, showing the Address or something. Making this selection should then be enough to get a unique match based on all three conditions (set up the lookup from a relationship using all three selections.)

With a portal, this filtering is a little different. Since a portal only shows one relationship, we must put these "OR" conditions in the match fields on both sides of the relationship (the first name is either blank OR it contains some text, same with Address, etc.) This is done by creating calculated keys on the parent side that have either the text of the selection, or something representing the blank that might be there (since we can't match relationships on a blank value, we'll use "all" instead.) On the child side of the relationship, the actual field value and a line with "all" is used.

So in your case, you might have these fields

In Property:

gFirstName (text, global)

gLastName (text, global)

cFirstName (calculation, text result) = case ( isempty(gFirstName); "all"; gFirstName )

cLastName (calculation, text result) = case ( isempty(gLastName); "all"; gLastName )

In Client:

cFirst Name (calculation, text result) = First Name & ¶ & "all"

cLast Name (calculation, text result) = Last Name & ¶ & "all"

Then the relationship would be:

Property <=> Client

Property::cFirstName = Client::cFirst Name

AND Property::cLastName = Client::cLast Name

This should work for the basic case where someone types in the first and/or last name into the respective global fields. But users have come to expect the ability to perform searches on partial text strings too. This is possible, without too much more work (it requires a couple more fields, but the effect is dramatic.) For this, I'll refer you to the Type Ahead example on databasepros.com.

Remember, these relationships directly between Property and Client are only used for filtering the selection down (or showing the selection.) Your original join table is still required, and its ID field can even be the target of the value list or portal selection process. (Hey, you said the link between Ownership and Property was by "PropertyID", but your TOG shows a link by "ElderID", what gives?)

[kibber]

Thank you very much for your thoughts Ender, currently I am using both portals and sorted value lists - I fear though this database has become a little too big for me to handle.

I am sure your solution works but am confused with the global storage aspect of it. If I have global storage for 'last name' then wont it only allow me one value across the database?

I am confident I have created the proper relationships between the names to allow for unique selections, but am a little confused with the portal setups.

Specifically, referencing through a table.

I have attached my database if you have the interest. As you will see the portal indicates the properties exist but does not display their information.

Thank you again for your help, as well as the reference to the website.

[Ender]

Thank you very much for your thoughts Ender, currently I am using both portals and sorted value lists - I fear though this database has become a little too big for me to handle.

The portal I was explaining is separate from the portal of Ownership records you are probably using to indicate which Owners are tied to which Properties. This filtered portal is exclusively for selecting a Client (a script would get the selected Client's ID and populate a new Ownership record.)

I am sure your solution works but am confused with the global storage aspect of it. If I have global storage for 'last name' then wont it only allow me one value across the database?

Globals are used in this selection process because the it's not necessary to store the "Last Name" or "First Name" that are part of the selection process. The end result of the selection process is to get the Client ID, and this is the only thing we really need to store.

I am confident I have created the proper relationships between the names to allow for unique selections, but am a little confused with the portal setups.

Specifically, referencing through a table.

Sorry, I'm not clear about what you're not clear about. :qwery:

I have attached my database if you have the interest. As you will see the portal indicates the properties exist but does not display their information.

I'd like to see what you've come up with, but the attachment doesn't open for me. Make sure the file is a zip or sit archive.

[kibber]

-Ok I see the purpose of the portal now, it could reside anywhere in the database, probably most effective in the property or client area and would have only a few options (If in the property area, would list first and last name - since we have already chosed the correct property through the visible record)

Then create a script which getvalue client ID, then creates a new record in ownership which finally ties the two together?

-I think I understand the global function now as a storage facility, although am not sure how to implement it, I will have to work on that

-The confusion is from me not understanding your concept for the portal as the device to relate the propery / client

-Even though it is not correct I have attached my file again, the more complicated stuff is not really mine, I was helped out by others in the forum.

Thank you for your help

EiderT2.zip

[Ender]

I've added the additional table occurances to make the selections work, and then added a simple selection layout using a combination of both methods. Hopefully you can see how this works so you can customize it for your needs.

EiderT2.fp7.zip

[kibber]

Ok I have seen what you have done and it will work for me, the only issue is my clients not only own multiple properties, but each property will have multiple owners. I'm afraid I did not explain my problem very well.

As you can see from the database I attached (prior) I tried linking last names from the property table into a portal within the clients table. Which worked perfectly when I only had one client to multiple properties. Once I had more than one client owning a property in addition to clients with the same last name I ran into problems.

Enter the ownership table, I created this so I could have a property table and a client table and link them so I could have my property portal within my client table.

The more I think about it, I believe my problem is that within my ownership table I have no way of narrowing your choices. If You choose Smith; John you will have 175 properties to choose from and if you choose Adams; Karl you will still have 175 properties to choose from.

So the area I can not seem to grasp is how to get what I had (portal in my clients table displaying all the properties associated with them) but by using an autoentered serial to form the relationship - since each name, no matter will have a unique ID - but to search with the last names.

Because of what you just showed me I think I can accomplish this, I am foggy on how to then tell filemaker to link (John Smith ID-1009) with (1 Cricket Lane Eider ID - MP-001-03)

-Within my ownership table I will have the layout you just gave me. This will enable me to link Smith with 7 Cricket as well as Adam with 7 Cricket without having to have multiple property records of the same thing.

I am sorry to have taken so much of your time and that I was not clearer with my questioning, but thank you very much for your help.

Ok I've thought about it:

Within the client table I have a property assign button. It brings up a screen similar to yours but instead you just fill in the address. Upon commit the script gets the clientID and the Property ID and creates a new record in a 'hidden' ownership table which joins the two. However how does my property portal in the clinet table work? How do I create a portal to the property table through the ownership table? and/or is their an easier way to accomplish this?

Edited October 11, 2005 by Guest

[Ender]

You might use a selection interface like I provided, only instead of setting the ClientID in Property, set the ClientID and PropertyID in a new ownership record.

Then on the Property layout, show the portal of Ownership records, with the Client names.

[kibber]

Thank you for all your help Ender, I am heading over to the script forum now.