Relating via an empty match field

[knewt99]

Hi there,

Please help if you can.

I have 3 tables, Programs, Providers and Facilities.

I want to relate each program to it's provider and facility.

I have made a complex relationship where

Programs::provider ID = Provider::provider ID

AND

Programs::facilityID = Facility::facility ID

This works fine except in cases where there is no facility so the facility ID in the Programs table is blank. FM does not read the blank field in both tables as a match so the relationship doesn't work. I'd like it in cases like this to fall back to a simple relationship based on the provder ID match fields.

Any ideas.

Thanks,

Kit

[polydore]

Hi there,

Please help if you can.

I have 3 tables, Programs, Providers and Facilities.

I want to relate each program to it's provider and facility.

I have made a complex relationship where

Programs::provider ID = Provider::provider ID

AND

Programs::facilityID = Facility::facility ID

This works fine except in cases where there is no facility so the facility ID in the Programs table is blank. FM does not read the blank field in both tables as a match so the relationship doesn't work. I'd like it in cases like this to fall back to a simple relationship based on the provder ID match fields.

Any ideas.

Thanks,

Kit

Hi Kit,

Since there needs to be a match for the relationship to work, why not create a record for facility "None" and use that.

polydore

[comment]

I have made a complex relationship where

Programs::provider ID = [color:red]Provider:provider ID

AND

Programs::facilityID = [color:red]Facility::facility ID

I don't think that is possible.

[Harmony]

I think you should create a unique field that denotes that there is no facility applicable. Perhaps 'none' is not appropriate and a unique value would work better? In your list of facilities have you used zero as a value?