Lookup related values from variable, not field

[Roeland De Windt]

Situation: a TableA with record(s) containing a multiline field (which happens to be a multi-line key) which I am looping through in a script. I need to lookup a related value from another TableB on each loop. Currently I have to set a (global) field in TableA to the n-th value of the multiline field at each loop and pull the related value from a relation based on that (global) field (TableA_TableB).

Question: is there a way to script this using only variables?

ie: Set $SomeRelatedValue = LookupValue ( TableA_TableB::LookupField , $SomeGivenValue )

or: Set Field [ TableA::SomeRelatedValue ; LookupValue ( TableA_TableB::LookupField, $SomeGivenValue ) ]

The LookupValue function is the one I am looking for and don't seem to be able to find. Solved it by using a global, as I said earlier, but I would like to use only variables. Anyone has a hint on how to do lookup from variables instead of (global) fields?

 

[comment]

I don't understand you description. What do you mean by "pull the related value"? What will you do with it after you have pulled it?

And why is it necessary to loop through the values in the multi-line field? If you have a relationship where this field is the match field, you can "pull" all the related values at once, using List ( Related::Field ).

[Roeland De Windt]

Good questions, comment.

Bare with me as I explain in more detail.

TableA contains evaluation questions, 1 per record. Every record has a multi-line employeeID field holding IDs of employees in TableB, who will be evaluated. How this multi-line field is populated is beyond the scope of my question, but the bottom line is that I didn't go for a many-to-many relationship setup. 

To assign questions to employees, I've created a third TableC, where later on, an evaluator of the employee will need to put his answer. Now to create a record in TableC, I need to get the TableA::QuestionID, and then loop through all values in the employee multi-line field to get each one. For every combo, a new record is created in TableC.

Now, in the process, for every employeeID-questionID combo, I need to look up an evaluatorID to put in the newly created record in TableC. I don't seem to be able to do this without putting the employeeID in a global from which the "evaluator" value is calculated. PS, should have said "pull the calculated value", not "related", as it involves  a LOT of If statements and different relations from them. It's not a simple case of always using the same relation).

Now... you mention a technique to pull all related values using List ( Relation::Field ), but this won't work because of the mentioned If statements and different relations.

[Roeland De Windt]

In short: can I use relations with variables instead of fields. Guess not. Got it working with a global. A non-issue I just would like to see confirmed.

[comment]

Let us name your tables as Questions, Employees and Answers.

52 minutes ago, Roeland De Windt said:

the bottom line is that I didn't go for a many-to-many relationship setup. 

That's not entirely correct. The way I understand this, you have two many-to-many relationships between Questions and Employees: one through a multi-key field in the Questions table, and one where the Answers table serves as a join table.

 

52 minutes ago, Roeland De Windt said:

Now to create a record in TableC Answers, I need to get the TableA::QuestionID Questions:QuestionID, and then loop through all values in the employee multi-line field to get each one. For every combo, a new record is created in TableC Answers.

I don't see any problem with that. However:

52 minutes ago, Roeland De Windt said:

for every employeeID-questionID combo, I need to look up an evaluatorID to put in the newly created record in TableC.

I didn't get this part, because you did not say where and how the EvaluatorID is to be found.

In any case, I don't see why you need to put the employeeID value in a global field, when you have already put it into the Answers::EmployeeID field. Surely you could base your lookup on that just as well?

 

52 minutes ago, Roeland De Windt said:

Bare with me

I am willing to bear with you - but the only way I will bare with you is if your name is Heidi and your picture appeared in an automotive parts catalog within the last 5 years.

 

37 minutes ago, Roeland De Windt said:

can I use relations with variables instead of fields.

If you mean can I use a variable as the matchfield in a relationship, then no.

 

Edited May 30, 2016 by comment