relationship - get info from many fields

[mikeytt]

I am sure this is quite easy but for the life of me can't get it to work.

A simple database with two tables. First table just contains an ID. Second table contains related records with an ID for matching and a second field, CONTRACTS - which can contains the following entries: UK, EUROPE, AMERICAS, VENUES.

Now i can easily view the contracts in a portal for each ID, but what i want to do is have either:

1) A single field on the first table which lists the the contracts for that ID or

2) a field that can give me a TRUE/FALSE response, such as does ID 1 have any contracts for Europe

Any help greatly appreciated, and basic file attached

test1.fp7.zip

[Robert Kidd]

Hope this is close enough for you.

test1.zip

[mikeytt]

Hi, thanks for having a go but it wasn't quite what i was looking for.

I finally worked it out and have attached the file showing what I meant.

By creating a Value List from the contract fields (based on related files only), you can then use the filter function and valuelist function in a calculation. The end result is that it gives you a list of the different occurences of the related contracts for each ID in a single field on the ID layout without the need for portals.

I don't need to know that an ID has 3 UK contracts and 2 EUROPE contracts, just that it has a UK and EUROPE contract. The single field reports this and allows me to perform other calculations and also to assign record access priviledges to different departments.

For example, the european team needs to access every ID that has a EUROPE contract, the uk team access to every ID that has a UK contract.

The difficulty was getting that information into a single field on the ID side, but this now works, fortunately!

test1.fp7.zip

Edited February 1, 2006 by Guest

[Robert Kidd]

I don't see what the filter function is going to be doing for you. The value list will only give you one of each unique occurance anyway ( besides you have hard coded your calculation field which is never nice )

Also you can't index this field or it won't update when you add/delete the related records.

That's why I had mine set to be unstored.

[mikeytt]

I see now - when i first opened your file the contractvalues were reporting a missing table, but on openeing it again now it is working fine.

You are quite correct and your way is indeed better. Is there any other way this can be done whereby indexing is involved however?

I ask as the results of this field are needed to determine who can see what records, and there will be over 15,000 records to work through. If I go ahead and use this method, unindexed, will this cause a big drag on the database?

[Robert Kidd]

The only way you are going to be able to index this field is to script the creation and deletion of your related records and set the field in these scripts.

[mikeytt]

that sounds like a good option - however, is it going to make a significant difference to the speed of the database, considering that the field will be pivitol in determining what records people can view (therefore continually being checked)?