My Self-Join Always 'Breaks'!

[FA Spencer]

Hi All,

My problem is this; I have a self-join which always seems to 'break' (loses data in the related portal).

I have a database cataloguing my pipe collection and in the 'Assets' table is the self-join (related_to & related_from).

The 'Assets' form contains the 'related_to' field as a drop-down, pointing in turn to a list (briar, metal, cherry ect.)

The portal on the 'Assets' form displays related records EXCEPT the current displayed record - which is fine, but when this link breaks (for whatever reason) the only way to re-populate the portal is to delete all the records and start again!

I'm pulling my hair out over this! Any suggestions?

Many thanks,

Frank.

[Matthew F]

I'm a little confused about the self-join relationship... Is the AssetID a unique record ID for assets? If so, I don't see why the self join shows anything other than the current record. I'd have guessed that you'd want to have a Product_Category field, and then you'd use the portal to view similar items. If you didn't want to view the current record in the self-join portal then adding an additional match requirement (AND Assetts::AssetID ≠ Related Assets::AssetID) would eliminate it.

[FA Spencer]

Yes, assetID is unique. The portal shows all matching records (on the related_to field) except the current record; this is exactly what it's supposed to do - so if there were 5 pipes made of cherrywood the portal shows 4, the fifth being the current record.

Hope that makes sense!

[doughemi]

Make sure that related_to is a global field.

[FA Spencer]

Make sure that related_to is a global field.

Making 'related_to' a global field means the current record cannot have a unique 'related_to' value... so in short, no that idea doesn't work...

[doughemi]

I'm afraid I'm not clear on the contents of related_from and related_to. It would probably be a good idea to zip your file and post it here.

[FA Spencer]

Well, it's definitely an indexing problem; if I turn OFF indexing for both fields (related_to / related_from) then back ON again it works... until the drop-down value is changed, by error or intentionally, the portal will not populate again.

[FA Spencer]

Solved!!!

Don't ask me where I got the idea to use (2) fields 'related_to / related_from' from, but the answer was to use ONE field and self-relate JUST 'related_to' and delete the 'related_from' field - now the portal shows related records EXCEPT the current record.