Many to Many & join fields

[Cousin Jack]

Hi everyone,

I have been struggling over this for 3 days now and getting more and more confused.

I have:

1. a nursing home policies file

2. a res home policies file

3. a domiciliary policies file

In each of these files, each policy can relate to a number of standards in my:

4. standards file

Equally, each standard may have a number of policies to which it relates, across all three policies files.

i.e. I think I have a Many to Many Relationship issue.

My problem is that I have 3 portals in my standards file allowing me to view the associated polices from each of the 3 policies files.

The match field (key) in each of the 3 policies files has been created by a value list ( from the standards file) formatted as 38 checkboxes which reflects an amalgamation of the 38 Standard Numbers and standard titles in the standards file.- this is of number type ( I have tried text type and things are even worse). Standard Number is the information by which the relationship is sorted.

So,... Relationships are based on the match fileld (above) in each policy file, and the "Standard Number" in the standards file.

In other words, "standard number" is the match.

While in the standards file, I can navigate back to any related policy in any of the 3 policy files via "go to related record (only related records)" by clicking the portal entries in the standards file.

I have also made a portal in each of my 3 policies files, and as I click the checkboxes to create the cross-reference in the policies files, the standard numbers pop up in the portals.

So far so good. ....

My problem is: Clicking the checkboxes in the policies file makes the standard numbers pop up in the portal, and ALL of these standards are listed in the standards file when I click in the portal in the policies file to "go to related record (only related record)". However, the related policy from which I have come is NOT always shown in it's portal in the standards file.

It appears that if that policy is related to more than one standard, it does not show up at all. If it is only related to one standard, then it is shown.

p.s. I previously had the key in the policies files as a repeating field with a value list rather than a standard field (Check box) with a value list - but have got rid of this as I understand it can be unreliable to use them.

Can anyone help me please. I cannot get to grips with Join files and am not sure whether I need one anyway. I desperately need some clear step by step advice.

Regards,

Jon Creber.

[Cousin Jack]

When I say:

"ALL of these standards are listed in the standards file when I click in the portal in the policies file to "go to related record (only related record)". "

I mean that all of the RELATED records are shown - not all of the records in the standards file.

This isn't the problem. The problem is the fact that the related policies records don't show up in the portal.

Sorry if I've caused any confusion. Look forward to hearing from anyone who would be kind enough to help.

Regards

Jon.

[CobaltSky]

Hi Jon,

There are several ways to set up many-to-many relationships - and there is no hard and fast rule about which is best, as it really depends on the context. Some folk swear by join files and will tell you that they are the only way to go. My view is it's an option, but not necessarily the only option.

In looking at the alternatives to a join file, however, it's important to note that all of them revolve in some way around the use of a multi-line field on one or both sides of the relationship (what is often termed a 'multi-key' field). This is significant in a number of ways, and one of them is that the relationship keys must be text fields, since only text fields accept and appropriately handle CR characters - and separately index the values found on separate lines.

Moreover, since relationship matching is done via the index of the target key field, if the data types of the left and right keys in a relationship do not match, then unpredictable results (or in some cases, no results at all) are the likely consequence. So, in short, many-to-many relationships that do not depend on a join file require that the key fields on both sides of the relationship be defined as text fields. This is the case regardless of the nature of their content - ie they may hold only numerals, but these must be indexed using text-based indexing protocols rather than numeric indexing, in order to make consistent and valid matches to multi-key values on either (or both) sides of the relationship.

From your description, I would hazard a guess that your problems may stem from the fact that you have established multi-key relationships with number fields on one or both sides. That being the case, there are a couple of ways you might proceed. One would be to use a join file. Another would be to redefine all the key fields (the ones involved in the many-to-many relationship, that is) as text fields. A third option would be to create new calculation fields that coerce the existing numeric values into text (eg using the NumToText( ) function) and use those fields instead as the keys for the many-to-many relationships.

This last option may be worth considering if you have functionality (eg sorting etc) which is dependent on the use of numeric data types for the source key values elsewhere in the solution. Ie if you need to retain numeric indexing for the source data, you will then need to either coerce the values to text so that they can be indexed appropriately for the multi-key based relationship to work, or 'bit the bullet' and use a join file instead.