big probs with relationships

[the walker]

i have a file that holds sales in certain countries and until now the region the coutry was in was calculated, but this meant that no one could add new countries without me changing the calculation. so i now have a file called coutry which contains a list of the countries and the region thay are in

however i had a self relation in the old version that showed in a portal all of the other sales in that region when you were on a record.

now i cannot create the self relation region::region and cannot get this portal

how can i get this to work

i have many other similar problems to this as we have added a product table, to specify which group and sub type a product is in(also used to be stored Calcs), a portal used to pull up all the other products in a sub type in the same country by using the self rel CountryAndSubtype::CountryAndSubtype can longer be created

please help

[simon1663]

What version of FM are you using? I can do this fine on FM6...

Well, assuming it doesn't work for you, I can think off 2 easy solution:

1. If you are viewing your records in form view - and using scripts. Why not try Having a Temporary global field - were you copy the content of the region - . and establish a relationship between that global field and the region field. This should work!!

2. Create a calculation field which just holds the copy of region field. And then try creating relationship.

Hope that helps...

[the walker]

i mean creating the relationship in the same file as it was before main.region::Main.region

if i try your second one i will just be where i started before. At the very least if i make that a calculation =Country:region in the country table i cannot store a related value meaning no index, meaning no Relationship

[CobaltSky]

The only way that you can get a self relationship to work is by having a region field that can be stored and indexed in your main file.

Since a calculation which references the region from your regions table (good move, by the way) can't be stored, the solution is to have a text field which automatically brings region across from your related table via a lookup. That will give you the best of both worlds because the lookup field can be indexed, but will still automatically populate and update based on the data in the related table.

The only catch is that if you make changes in the related table which affect existing records, you'll have to remember to run a relookup in the main file to pull the amendments across.