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

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 )

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

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

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

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

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

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.

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

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

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.

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

NullTest.zip

Thank you SlimJim! It's working now!