Relationship doesn't work?!

[mcschan]

Dear FM Experts,

I am working on a company wide asset database. I want to create a relationship that gives computer models that have a model number but no name for it, hence "Unrecognized Models". I have created a relationship joining Computer Types::Name = Unrecognized Model::Null Value. The Unrecognized Model::Null Value has an auto enter value of "". So essentially this is asking if Computer Types::Name = "". In the Unrecognized Model table I have a field called Counter that counts the number of unrecognized models. Counter is calculated by Max (0, Count (Asset::ID)). Asset::ID is a unique number throughout the whole database.

In my Computer Types table I have about 5 models that has a model number but no name, hence unrecognized. But counter is giving me 0. The formula I used for other relationships like unknown OS and unassigned asset works, just this one is not working. Please give me a hand.

Thanks

Matthew

[Ender]

Relationships cannot be established between null values. Either use a text entry on those empty names, like "None", or have a calc that does this and use the calc as the relational key:

key (calculation, text result) = case ( isempty ( Name ) ; "None" ; Name )

[mcschan]

Dear Ender and everyone else who can answer this,

Would you please explain how to use the calculation in the above message as the rational key? I can't really use "None" for empty names. Thanks.

Matthew Chan

[Ender]

In the relationship definition, use that key calc for the relationship instead of the Name field.

[mcschan]

Hi Ender,

I originally had this relationship:

Computer Types::Name = Unrecognized::Null Value. How do I enter the key calculation into the relationship in the Edit Relationship dialog box?

Thanks,

Matthew Chan

[Ender]

Define the key field in the Model table. Then reselect it in the Edit Relationship dialog and hit the Save button. It should look like:

Computer Types::Name = Unrecognized Model::key

[mcschan]

Hi Ender,

Sorry to have you reply this many times. This definition for key (calculation, text result) = case ( isempty ( Name ) ; "None" ; Name ) will give the word None if the name is empty. But the problem is I want the name to remain as an empty field rather than putting "None" in it. Is there any way to do that?

Matthew Chan

[Ender]

This key field would only be used for the relationship, which has to have something to match against.

Note that the relationship I described implies that there will the a Computer Type record with "None" as the name. This could be dealt with in a similar manner as the match key was, by defining a calculated key that uses "None" if the Name is empty.

[mcschan]

I have a defined a counter variable in the Unrecognized table called Counter (Calculation, text) = Count (Asset::AssetID). Asset::AssetID is a unique number throughout the whole database. This counter gives me nothing.

I've also created a portal to display from the Unrecognized table but again gives nothing. I am just wondering what else do I need to do?

Thanks

Matthew

[mcschan]

I've made the changes that you recommended by the way, like inserting "None" to empty Model Names.

[SlimJim]

I have just come across this thread and was curious about the topic - how do you pick out null values and count them. I had an idea that what was required in the Unrecognised table was a value list of all the names and then a relationship in the new FMP7 model which picked out names not in the value list. After a few problems with getting the value list to refresh it seems to work OK - attached.

My apologies if this is not relevant.

[SlimJim]

Sorry reposted the same reply and forgot the zip. Senility is now flowing in!

NullTest.zip

[mcschan]

Thank you SlimJim! It's working now!