Jump to content

Lookup related values from variable, not field


This topic is 2220 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

 

Link to comment
Share on other sites

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 ).

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by comment
  • Like 1
Link to comment
Share on other sites

This topic is 2220 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.