Relationship of a Specific Repetition in other DB

[corey]

I CURRENTLY have a PRICE LIST DB that contains about 8 REPEATING fields {200 in EACH}.

With the 8th field named [sell Price] I am trying to setup a RELATIONSHIP to ANOTHER DB.

The REPETITION in the [sell Price ] field is 64, the value of that is "$50.00".

In the NEW DB i am setting up i have a field [Glue] which is the number of [sell Price] items in it, Then i have a CALCULATION field [Total Price].

After selecting the amount of items required in the [Glue] field i want to setup the [Total Price] Field CALC. to MULTIPLY that [Glue} value with the Specific value of the PRICE DB: {Sell Price].

EG.

If [Glue] field value was "3" then i want the [Total Price ]field to give a value of "$150.00".

Being: currentDB[Total Price] = currentDB[Glue] x relatedDB[sell Price].

I am a BIT new to RELATIONSHIPS and RELATING Fields between DataBases.

How do i set the RELATIONSHIP, Do i Relate the [sell Price ] field to the [Glue] field OR the [Total Price] field?

What would the [Total Price] CALCULATION be :

?????? GetRepetition ( Sell Price, 64) * [Glue] ????????

How do i get the SPECIFIC REPETITION of the field i am looking at

After RELATING the [sell Price] field in the OTHER DB to the [Glue] and/or [Total Price] fields, I tried a simple CALC. [Total Price]= [Glue] * PRICE LIST::[sell Price] BUT it did not give me anything, OR said it was making a CIRCULAR DEFINITION.

Any assistance with this would be much appreciated.

Corey....

[danjacoby]

Another reason repeating fields are generally a bad idea.

I'd create a new, expanded price list file, with the same fields as the price list file, but no repeating fields (just make a clone, then set all the repeating fields to not repeat). Then import the data from the original price list file, splitting the repeating fields into separate records.

Now you can set up the relationship with this new file, and all your calculations should work.

[corey]

I 've removed all the repeating field and cahnged them to a cloned copy(renamed) so i have multiply fields alike, yet different.

But i am unable to get the RELATIONSHIP to work.

I even tried a LOOKUP on 1 x of the fields to have a value from the price list to be used, BUT it will not get the value either.

Am i doing something wrong?

Do i need to define a TRELATIONSHIP in eother DB, 1 looking at 2, and 2 looking at 1?

Reagrds

Corey....