0

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

1 2 3

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
 

7 replies

null
    • John_Halls
    • 7 mths 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

    Demo.ninox
      • cyan_silver
      • 7 mths ago
      • Reported - view

        Thanks

    • Fred
    • 7 mths 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
      • 7 mths ago
      • Reported - view

       Makes a change Fred!

      • cyan_silver
      • 7 mths ago
      • Reported - view

       thanks,I will try it

    • cyan_silver
    • 7 mths ago
    • Reported - view

    It doesn't seem to be as simple as I thought
    I can only try it

    let t := this;
    let fieldName := t.'code name';
    let matchingRecord := first((select Table2)[name = t.name]);
    if matchingRecord != null then
        if fieldName = "A" then
            matchingRecord.A
        else
            if fieldName = "B" then
                matchingRecord.B
            else
                if fieldName = "C" then
                    matchingRecord.C
                else
                    if fieldName = "D" then matchingRecord.D else null end
                end
            end
        end
    else
        null
    end

      • Fred
      • 7 mths ago
      • Reported - view

        With how you setup your DB, it is not simple. Did you try DB? Did you modify your DB to use a N:N table?

      Your code should work as long as A, B, C, D are the actual names of the fields in Table2. What is happening?

      I find if you need to do more than 1 embedded if statement then I try to use a switch.

      It would look something like:

      if matchingRecord != null then
    switch fieldName do
        case "A":
            matchingRecord.A
        case "B":
            matchingRecord.B
        case "C":
            matchingRecord.C
        case "D":
            matchingRecord.D
    end
end
Login to reply

Content aside

  • 7 mths agoLast active
  • 7Replies
  • 66Views
  • 3 Following