Choosing a relationship

[jodibee]

Hi All,

I need some help with auto data entry via calculation based on relationships with multiple files.

The parent file will have a many to one relationship with three different child files, the dilemma is deciding

while relationship to use to pull information from a child file. I have a field with a value list that denotes

which child file it should use and I can set up the relationship OK. But I need some help to test the relationships

so that data is automatically entered in several fields in the parent file.

My thoughts have gone along the lines of an If script...

If field a = value a of list, then use relationship 1.

If field a = value b of list, then use relationship 2.

etc.....

Am I on the right track and how best can I lay the track?

Ta

JB

[Ugo DI LUCA]

This is your second attempt here. Sorry I misunderstood your first question.

You don't need a conditional value list though, you just need a script that would be based on the content of a pop up list.

You therefore need a Case calculation (call it c_List Choice) to identify wether value A = W makes part of Value List 1, 2 or 3.

create 3 calcs, call them ResultList1, ResultList2,ResultList3

ResultList1= PaternCount(ValueListItems,"the file where the value is", "Value List 1"); "Field A")

ResultList2= PaternCount(ValueListItems,"the file where the value is", "Value List 2");"Field A")

ResultList3= PaternCount(ValueListItems,"the file where the value is", "Value List 3");"Field A")

and a case calc c_List Choice = Case(ResultList1=1; "A", ResultList2=1,"B","C"))

Your script would therefore go :

If c_List Choice = A

Go to Client

Perfor script there

End if

If c_List Choice = B

Go to Prospect

Perform script there

End If

If c_List Choice = C

Go to Supplier

Perform script there

Not sure you could'nt do a case calc with Case(PatternaCount...) that would be easier.

And not sure about ";" or "," as I use a slightly different French system.

[jasonwood]

Hi Jodibee,

Sounds like Ugo might know a little more about your situation than I do (it almost seems like an answer to another question(?)), but here's how I would answer your question without any additional information...

I have a field with a value list that denotes which child file it should use and I can set up the relationship OK

You can't use a script to tell a record which relationship to use for auto-enter data.

There's probably a way to manipulate a auto-enter calculation so that the auto-enter calculation itself checks the value of your field and takes an appropriate response (through IF statements), but this is not a script, and I have a feeling that a script would be more effective.

Let's say your field (the one you mentioned with the value list), let's call it relationshipChoice, has 2 values, "Use Relationship 1" and "Use Relationship 2".

You then have 3 fields you want to fill in, firstName, lastName, companyName, with data from one of the 3 parent files. Let's call the two relationships Relationship1 and Relationship2.

Now attach the following script to a button:

If relationshipChoice = "Use Relationship 1"

set field, firstName, Relationship1::firstName

set field, lastName, Relationship1::lastName

set field, companyName, Relationship1::companyName

Else

If relationshipChoice = "Use Relationship 2"

set field, firstName, Relationship2::firstName

set field, lastName, Relationship2::lastName

set field, companyName, Relationship2::companyName

End If

End If

Is that helpful?

[Ugo DI LUCA]

Hi Jason,

I just messed up the first time, thinking that Jodibee was searching a way to choose a value-list to be attached to a field depending on the value in that field. I did a tip for that purpose.

Rereading your post, I may have messed up a second time if Jodibee wants to use a script to tell a record which relationship to use for auto-enter data.

I considered in fact that he just wanted his script to be triggered in a different file accoding to the value list chosen.

[djgogi]

There are several ways for achieving it.

The one jasonwood suggested you, for ex, which requires scripting however.

But also it could be reached by only using calculations.

Now I'll make some assumption (those are not of esential and could be worked around with proper calculations):

1)value list contains names of relationships to use, otherwise use an tunneling calculation of type:

case(value list item=choice 1,RelName to use for choice 1,

value list item=choice 2,RelName to use for choice 2, Default to use if no selection)

2) names of related fields are same in all related files, agin if the above is not true you should use proper calculation for selecting the right name.

OK, so given value list field relList with names of relationships to use the calcuation for an hypothetic field multiRelField should be defined as:

multiRelField=GetField(listRel & "::" & fieldname)

HTH

Dj

[jasonwood]

multiRelField=GetField(listRel & "::" & fieldname)

That's cool... I didn't know you could construct a field address that way.

Just be advised that this is almost equivalent to using related fields rather than lookup fields. The data will not be stored in this db. The advantage would be that the fields will change immediately if you change the relationship used.

You can also use some of Dj's ideas to make a much simpler script (avoiding the If statements).