Select from another table
I am trying to display value from another table's record with criterium based on comparing fields from two tables:
first((select AnotherTable)[FieldFromAnotherTable = FieldFromThisTable].AnotherFieldFromAnotherTable)
But it is not working. What is wrong?
8 replies
-
It all depends on if you have the necessary fields linked in each table. In your example, FieldFromAnotherTable (FFAT) and AnotherFieldFromAnotherTable (AFFAT) need to be reference fields in AnotherTable. Then you can do:
let curRec := this; <--this gathers the data from the current record you are on
first((select AnotherTable) [FieldFromAnotherTable = curRec.FieldFromThisTable].AnotherFieldFromAnotherTable)
-
Thank you for bringing command
let curRec := this;. I didn't realise it is necessary to gather data from record to variable first.Please elaborate on linking fields from tables. I am putting the code in created >>Table refernce field<<. But it is still not working. Should I do something more to link specific fields? -
Can you tell us the table names and fields you are working with? You can post a screenshot of the Edit Fields window for each table and another of the data model.
-
Main table name is >Puszki< (>Items<), referenced table name is >Producenci< (>Producers<). Names od fields which are compared in both tables are >nazwa_p< (name of producer) and >producent< (name of producent). Name of field which content should appear in main table from referenced table is >opis_p< (descrcription of producer).
Edit Fields of >Puszki< table:
Edit Fields of >Producenci< table:
Function area of >producenci_o< field in main table:
Data model:
-
Hi
I suspect the reason you are not seeing anything is that nazwa_p and producent are not exactly the same. They have to match in spelling, capitalisation, and any leading & trailing spaces there may be lurking. Spend some time in the console playing with your select statement to see what results you are getting. I'd do it this this order
select Producenci - you should get a list of id's
select Producenci[nazwa_p = "Name of Producer"] - Use a known name of producer in quotes, you should see a shorter list of id's
And so on, slowly building towards your final script
Your reference fields have been set up but your formula assumes that they haven't. If the reference fields are populated then you can see the opis_p field anyway in a table view and in a form view have a formula field with just producenci_o.opis_p as the formula. If the reference fileds are not populated then I would look to do that with a script placed in a button but you will need to address the problem in paragraph 1 before embarking on that.
Regards John
-
I got it to work. The reason of failure was that the comparison field in main table (>nazwa_p<) cannot be Choice field (which it was in my case), it has to be Text.
So the formula
let curRec := this; <--this gathers the data from the current record you are onfirst((select AnotherTable) [FieldFromAnotherTable = curRec.FieldFromThisTable].AnotherFieldFromAnotherTable)is working. I think it is very useful, as it lets to keep additional data on some recurring fields in separate table.
-
You can use choice field, dynamic choice fields are different, but basic choice fields work fine.
text(choicefieldname)
first((select AnotherTable) [text(FieldFromAnotherTable) = text(curRec.FieldFromThisTable)].AnotherFieldFromAnotherTable)
-
Thank you for explanations.
Content aside
- 3 yrs agoLast active
- 8Replies
- 1559Views