0

Whether the data content of table1 field can become the field of select(table2)

I have no foundation in coding, thank you

let t := this;
let X := 'code name';
first((select Table2)[name = t.name].X)
 

The above coding is incorrect, please give me some suggestions
 

5 replies

null
    • John_Halls
    • 9 days ago
    • Reported - view

    I have created a demo file that uses a sub-table for the values of A, B, C and D in Table2. It is also linked to Table1 to show these values as you requested. The code for this is in the 'Trigger after update' of Table1. I have also added formula fields to mimic the fields in  JPG 2

      • cyan_silver
      • 8 days ago
      • Reported - view

        Thanks

    • Fred
    • 9 days ago
    • Reported - view

    shucks, beat me to it.

    Let us breakdown your code:

    let t := this;
    let X := 'code name';
    first((select Table2)[name = t.name].X)

    According to your first picture, this code is in a formula field called 'quantity' in a table called 'Table1'. I am guessing you want to pull the data from the field quantity from Table2 for records where the data in 'name' matches.

    Line 1, you are creating a variable called 't' and using the this command to capture the current record.

    Line 2, you are creating a variable called 'X' and captures the data in the field 'code name'. This is fine, a bit redundant since you have already captured all of the fields in line 1.

    Line 3, you want the first record of a select statement. I would recommend you use the where function of the select statement here. Why? When you do a where filter of a select, the server only sends over the records that match. When you do a square bracket [ ] filter, the server sends over all records then does the filtering on the client side. In this test DB, you won't see a difference, but if you had 100 of thousands of records then it will make a difference.

    Generally you can't use a variable for a field name. In your case, that is why you getting the response of 'A' when you use the variable X. You are getting the value in the field 'code name' of the current record.

    Looking at your pictures, Table2 shows columns called A, B, C, D. Are those separate field names? If they are then that makes things a bit more complicated.

    If A, B, C, D are separate fields that store the same type of data, quantity in your case. Then you may want to look into creating a subtable that stores each value instead.

      • John_Halls
      • 9 days ago
      • Reported - view

       Makes a change Fred!

      • cyan_silver
      • 8 days ago
      • Reported - view

       thanks,I will try it

Content aside

  • 8 days agoLast active
  • 5Replies
  • 46Views
  • 3 Following