Hi,

I have two Qs about portals in a self-joining relationship:

1) How can I omit the record "itself" from its own portal?

2) It seems that the portal only looks at the first related record. Is this true? If so, this self-joining relationship is not as helpful as I would have hoped (it can't relate one record to several others; it can only relate one record to one other record? And the one other record is always the same?)

-For example, I have set up a self-relationship for the purpose of de-duping. Thus, the "Bothnames" field in record A is matched with all other records with the same "Bothname". The portal in record A shows record A (this is the undesirable situation that I am referring to in my first question), and two other records with the same "Bothname". When I use a calculation field to make a comparison between "Address" and "::Address", the caculation always uses the first record listed in the portal. How can I use the self-relationship to compare to more related records than just the first?

I hope that makes any sense.

Thanks for your help.

I have been attempting something similar with several of my databases which have been created using data from one big blob of a database.

I have had pretty good success with using a portal set up like you are saying on a relationship between a field and itself in a very literal self-join. I then used several global fields to store information.

The way I did it was to go to the first portal row, store the portal row number in a global field (g_portal_row) and then store one or more of the field values in that row in global fields of their own using set field statements in the script. I then loop while incrementing a portal row counter each time and comparing the values in the global fields with the values in the associated fields in each row. Based on criteria that was appropriate for each circumstance that I used this in, I had the script decide whether to delete the record or not and then go onto the next portal row.

I hope this gives you enough to transpose into your circumstances. If you try this make sure to do this on a copy of the database first because once the loop starts, if you haven't put in appropriate logic to decide which records to delete, you can watch your data go bye bye very quickly.

Hi John,

Thanks so much for your response. There are a few basic things that I don't understand in your explanation however (I am new to FM). How do I access the portal row number? And how do I make the script look at each row (I only know how to make it look at the first row)?

Hi John,

I answered my own question. Moving forward Thanks!

-Helen

Sorry I didn't get back to you earlier, I have been in meetings all morning.

I use the Status function quite a bit, but it sounds like you might have found that on your own.