0

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

null
    • Fred
    • 2 yrs ago
    • Reported - view

    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)

    • chwalowski
    • 2 yrs ago
    • Reported - view

    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?

    • Fred
    • 2 yrs ago
    • Reported - view

    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. 

    • chwalowski
    • 2 yrs ago
    • Reported - view

    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 >Puszki< table

    Edit Fields of >Producenci< table:

    Edit Fields of >Producenci< table

    Function area of >producenci_o< field in main table:

    03

    Data model:

    Data model

    • John_Halls
    • 2 yrs ago
    • Reported - view

    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

    • chwalowski
    • 2 yrs ago
    • Reported - view

    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 on

    first((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.

    • Fred
    • 2 yrs ago
    • Reported - view

    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)

    • chwalowski
    • 2 yrs ago
    • Reported - view

    Thank you for explanations.

Login to reply

Content aside

  • 2 yrs agoLast active
  • 8Replies
  • 1415Views