FA Spencer Posted April 6, 2012 Posted April 6, 2012 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 Posted April 6, 2012 Posted April 6, 2012 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 Posted April 6, 2012 Author Posted April 6, 2012 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!
FA Spencer Posted April 6, 2012 Author Posted April 6, 2012 On 4/6/2012 at 3:53 AM, doughemi said: 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 Posted April 6, 2012 Posted April 6, 2012 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 Posted April 6, 2012 Author Posted April 6, 2012 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 Posted April 7, 2012 Author Posted April 7, 2012 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.
Recommended Posts
This topic is 4774 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now